Eloquent : Count of posts for a period with one row per day

You can create a custom collection for your model. Then you can implements a method that return a subcollection for a day by using the filter method.

use Illuminate\Support\Collection;

class PostCollection extends Collection{

  function filterDay($day)
  {
     return $this->filter(function($post){

      // Check whether the post is from this day

       return $is_from_this_day;
    }
  }

}

class Post extends Eloquent{

  public function newCollection($posts)
  {
    return new PostCollection($posts);
  }

}

then in your view

@foreach($last_30_days as $day)
  <?php $posts_from_this_day = $posts->fromDay($day) ?> 
  <h2>{{ count($posts_from_this_day }}) posts from the day {{ $day }}</h2>
  <ul>
  @foreach($posts_from_this_day as $post)
    <li>{{ $post->title }}</li>
  @endforeach
  </ul>
@endforeach

For my needs it would be better to have it directly from the DB results...

Why, if I may ask? It seems like this would be something pretty awkward to do in SQL and pretty simple to do with PHP:

$chartDatas = /** your previous code */;

$chartDataByDay = array();
foreach($chartDatas as $data) {
    $chartDataByDay[$data->date] = $data->count;
}

$date = new Carbon;
for($i = 0; $i < 30; $i++) {
    $dateString = $date->format('Y-m-d');
    if(!isset($chartDataByDay[ $dateString ] {
        $chartDataByDay[ $dateString ] = 0;
    }
    $date->subDay();
}

@thepsion5 : I use your solution, much more simple ;-) Thanks !

Hi, thank you guys for your replies.

I want to do the same but select by month.

Anyone can help me please ?

@Mehdi : You can do DB::raw('MONTH(created_at) AS month')

Thank you @Mushr00m four your reply : That's what i get with DB::raw('MONTH(created_at) AS month')

SQLSTATE[42883]: Undefined function: 7 ERROR: function month(timestamp without time zone) does not exist LINE 1: select MONTH(create_at) AS month, COUNT(*) AS count from "vi... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. (SQL: select MONTH(create_at) AS month, COUNT(*) AS count from "view_resacashin" where "create_at" between 2014-05-16 and 2014-10-16 group by "month" order by "month" asc)

That's what i do :

        $from = date('Y-m-d', strtotime('-5 month'));
        $to = date('Y-m-d');

        $chartDatas = Reservation::select([
            DB::raw('MONTH(create_at) AS month'),
            DB::raw('COUNT(*) AS count'),
        ])
            ->whereBetween('create_at', [$from, $to])
            ->groupBy('month')
            ->orderBy('month', 'ASC')
            ->lists('count', 'date');

        echo '<pre>';
        echo count($chartDatas);
        print_r($chartDatas);exit;

You are asking to lists('count', 'date') but your have changed "AS date" to "AS month" ;-) So change lists for "month".

I do it, but still the same error !

Undefined function: 7 ERROR: function month(timestamp without time zone) does not exist LINE 1...

Ho yes, I think you have a typo, it should be "created_at" and not "create_at". And of course you must not have public $timestamps = false; in your model.

the database is created manually, "create_at" represents "created_at" I use PostegreSQL

Looking at your error, the "create_at" is not recognise as a datetime column so Month can't work. You have to fix that part. I've never used Postgres so I can't help you much.

i find the solution :

DB::raw('EXTRACT(MONTH FROM create_at) AS month'),

MONTH function apparently does not exist in PostgreSql. Thank you for your help.

Hello. I tried to use the solution from @thepsion5 but I'm really not experienced to Laravel and maybe because of this I'm not getting success to my task.

I need to create a graph from the last 7 days. The first part ("$chartDatas = /** your previous code */;") worked fine, but the second I can't understand how to do.

This entire code is uploaded to the Controller? Thanks.

@THEPSION5 - Thanks for above code . you can also sort date this way .. then it become perfect !!!

foreach($chartDataByDay as $key => $value){
    $key = date('Y-m-d',strtotime($key));
    $array[$key] = $value;
}
ksort($array);
print_r($array);```

Hey, based on your code and @thepsion5 's I think this function does exactly what you want:

function getLastMonthUsers()
    {
        $chartData = User::select([
            DB::raw('DATE(created_at) AS date'),
            DB::raw('COUNT(id) AS count'),
        ])
        ->whereBetween('created_at', [Carbon::now()->subDays(30), Carbon::now()])
        ->groupBy('date')
        ->orderBy('date', 'ASC')
        ->get()
            ->toArray();
        $chartDataByDay = [];
        foreach ($chartData as $data) {
            $chartDataByDay[$data['date']] = $data['count'];
        }
        $date = new Carbon;
        for ($i = 0; $i < 30; $i++) {
            $dateString = $date->format('Y-m-d');
            if (!isset($chartDataByDay[$dateString])) {
                $chartDataByDay[$dateString] = 0;
            }
            $date->subDay();
        }
        ksort($chartDataByDay);
        return asort($chartDataByDay);
    }