Group by month and order by something else?

PS - I've tried to edit this question to make the formatting better but the little humanity check thing is saying I haven't answered the question and I absolutely have.

I usually have orderBy after groubBy.

I get a "bad method call" error when I do that:

$posts = Post::query()
            ->get()
            ->groupBy(function ($date) {
                return Carbon::parse($date->created_at)->format('F');
            })->orderBy('created_at', 'DESC');

Put query in this order, just example:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

->get(); comes last. Adjust to your query / data.

Ok I'll give it a bash, thanks. But I think it's the ordering within the month structure that's the thing which is breaking my soul.

Here is just an example of a report dealing with month, not eloquent, but might help, or might not:

Last example here: https://laracasts.com/discuss/channels/guides/getpdo-usage

Of course in any raw query, bind parameters, just example.

But you should be able to orderBy date.

Hi @mick79

I'll add a raw select with MONTH(created_at) as created_month to your query and then you can chain orderBy methods to achieve what you want.

For example:

$posts = Post::query()
            ->select(DB::raw('MONTH(created_at) as created_month, post_likes'))
	    ->orderBy('created_month')
            ->orderBy('post_likes', 'DESC')
            ->groupBy('created_month')
	    ->get();

Also, unless this query will always be run for a particular year, I'd consider adding the year to the query, otherwise you will have everything mixed when you start having Posts from a new Year.

Hope this helps!

Laracasts has a video about something similar:

https://laracasts.com/series/laravel-explained/episodes/6

But the solution in the lesson is similar to what you already have

As you want to show the individual records, grouped by month and then sort them by rank, maybe fetching from DB ordered by date, group the collection and them sorting might do the trick:

$posts = Post::query()
    ->latest() // similar to ->orderByDesc('created_at')
    ->get() // fetch the query and returns a collection
    ->groupBy(function ($post) {
        // If Post is a regular Eloquent model, 
        // I think you can safely change this line to:
        // return $post->created_at->format('F');

        // to be safe when close to year change, 
        // I would add the year to the format:
        // return $post->created_at->format('F, Y');

        return Carbon::parse($post->created_at)->format('F');
    })
    ->transform(function ($group) { // similar to ->map()
        // as collection was grouped each item will be a collection from the grouped items
        return $group->sortByDesc('post_likes');
    });

To add it would not hurt to have a decent visual query Builder.

I sometimes use Microsoft Access but if you don't have something like that a good free Builder is SQL Leo.

It really helps working out certain queries.

Of course this is just a suggestion.

As I've said on some other post some of these more complex type queries does require a bit of trial and error.

Note also that a group by can be tricky in that you can actually get results but the wrong results, so when testing make sure you have known good results run the query and you should get the same thing.

I think your solution @rodrigo.pedra is an interesting approach when you have irregular groups (like in that lesson that you have 'today' and 'last week') but in this case I think is best to let the DB handle all the grouping and ordering, as it's optimized to do that in the best and efficient way possible.

Although as you've pointed out...it's similar to what @mick79 had in the first place, so it might be easier to understand and implement.

I was excited by this solution (thank you) but I get this error message:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'mvpfyi.posts.post_likes' which is not functionally dependent on columns in GROUP BY clause;

@rodrigo.pedra @jlrdw @marianomoreyra

I solved this really simply within the blade.

I changed

@foreach($groupedposts as $post)

to

@foreach($groupedposts->sortByDesc('post_likes') as $post)

And it's perfect.

@mick79 have you tried grouping the second field as the message suggests?

$posts = Post::query()
            ->select(DB::raw('MONTH(created_at) as created_month, post_likes'))
	    ->orderBy('created_month')
            ->orderBy('post_likes', 'DESC')
            ->groupBy('created_month')
            ->groupBy('post_likes')
	    ->get();

Yeah I did but still couldn't get it to work. I appreciate the help but I solved this as detailed above.

Great you have it working. The weird thing from the error message you sent is that my try was not grouping by on the DB query, it was using the collection's group by method after the database query is getting executed (after the ->get()) method.

Maybe you tried adapting my solution to your existing code. Which makes totally sense as you might have shared a simplified version of your app's code.

Essentially grouping by in the for each is similar to what I proposed, as it is grouping the collection, not the query.

I skipped grouping by in the database as it seemed to me you want to fetch all Post's records and then group by the results for UI presentation. I generally only group by in the database when I need some kind of aggregation, (such as COUNT(), AVG(), SUM(), ...).

When using one of those, for most DBMS, you need to specify the columns you are referring on the SELECT and ORDER BY clauses to not have the error message you sent.

But then again, great you got it working =)