How to achieve this, eloquent tricks

This is the closest I could get for ordering posts by date.

Raw

select *, date(created_at) as date_of_creation from posts order by date_of_creation

Eloquent

Post::select('*')->selectRaw('date(created_at) as date_of_creation')->orderBy('date_of_creation');

Not sure how you intended to group them by days, since each record is represented as one row in the table.

I am currently using this, but it's not working with pagination...

Post::latest()
            ->get()
            ->groupBy(function($date) {
                return Carbon::parse($date->created_at)->format('l');
            });

What you get in your call is a Collection object if I'm not mistaken, and you're free to pass the Collection as the first argument to the Paginator. Notice line 40.

@RachidLaasri how exactly is it not working for you? What output do you get?

I think the problem is using "get" method before grouping.

The error i get when i try to add the groupBy clause is :

Call to undefined method Illuminate\Database\Eloquent\Collection::paginate()

@RachidLaasri it's because get() method returns a Collection instance, and there's no paginate() method on Collection instance. The groupBy() you're calling is actually performed on the Collection instance, after all of the data has been loaded.

Since paginate() method on the Eloquent model returns a LengthAwarePaginator instance, can't you do the same?

$data = Post::latest()
    ->get()
    ->groupBy(function($date) {
        return Carbon::parse($date->created_at)->format('l');
    });

return new LengthAwarePaginator($data, /** other params */);

It didn't work for me, still giving me all the posts...

Any ideas...

Why you do not want group it with pure PHP after SQL query and pagination?

@RachidLaasri You want to paginate it by date or by number of posts?

@unglued because i need to use pagination on the grouped result...

@JarekTkaczyk I want to paginate it by date, the day to be exact

@RachidLaasri So just get the dates you need for given page, then query the posts with these dates.

@JarekTkaczyk i didn't understand what you meant, what i want to do is show the posts on the home page, i want the user to be able to see the posts grouped by days with pagination, from newest to oldest...

@RachidLaasri You mean you are creating a time line?

@toniperic solution should work. Creating a grouped collection and instanciate a LengthAwarePaginator.

@RachidLaasri Well, I asked what's the pagination base. You said the date, so I suppose this:

1. you want N dates per page, no matter how many posts there are for each date
2. you want N dates with posts, not N consecutive dates

That said, you want to first get the N dates (distinct) ordered desc, then you get the real result - your posts, that were published on that N dates. Next, you just group the result collection by date and you're good. Should I explain it in code?

@SachinAgarwal Yes, the same idea as a timeline.

@pmall I tried it, still giving me all the posts ( the pagination doesn't work ).

@JarekTkaczyk Thanks for the explanation, it would be better if you could explain it in code...

@RachidLaasri Assuimng MySQL and created_at timestamp as your field (if you're using date type, then no need for raw additions):

$dates = Post::selectRaw('date(created_at) as date')->latest()->distinct()->paginate()->lists('date');

$posts = Post::whereIn(DB::raw('date(created_at)'), $dates)->get()->groupBy( YOUR CLOSURE HERE );