How to order grouped results using Eloquent?

Question

I've been trying to figure this out for some time now and just can't seem to make it work. I have a table that looks similar to this.

Table: Issues

id  yearly_issue    year    stock   created_at      updated_at      magazine_id 
1   10              2000    1       [timestamp]     [timestamp]     3
2   12              1994    6       [timestamp]     [timestamp]     10
3   36              2007    10      [timestamp]     [timestamp]     102
4   6               2002    7       [timestamp]     [timestamp]     7
5   6               2002    2       [timestamp]     [timestamp]     5
6   12              2003    9       [timestamp]     [timestamp]     10
7   11              2003    12      [timestamp]     [timestamp]     10

My problem is that I need to sort it (easy!) but, I only want to get one of each magazine (column magazine_id).

My Eloquent query as of now is:

$issues = Issue::where('stock', ($all ? '>=' : '>'), 0)
  ->orderBy('year', 'desc')
  ->orderBy('yearly_issue', 'desc')
  ->take($perpage)
  ->get();

I thought adding the groupBy('magazine_id') would help, but it seems as though it only partially helps me. The results is not in the correct order. So, my question then is - is there any easy way around this?

I've been experimenting with various answers to similar questions but I completely fail to implement it.

Retrieving the last record in each group

or

How to get the latest record in each group using GROUP BY?

More help would be much appreciated.

EDIT: The closest I am currently is this:

SELECT i1.*, c.name AS image, m.title AS title
FROM issues i1
INNER JOIN covers c ON i1.id = c.issue_id
INNER JOIN magazines m ON i1.magazine_id = m.id
JOIN (SELECT magazine_id, MAX(year) year, MAX(yearly_issue) yearly_issue FROM issues GROUP BY magazine_id) i2
ON i1.magazine_id = i2.magazine_id
AND i1.year = i2.year
-- AND i1.yearly_issue = i2.yearly_issue
WHERE i1.stock ($all ? '>=' : '>') 0
ORDER BY i1.year DESC, i1.yearly_issue DESC
LIMIT $perpage

However, it is not giving me the desired result at all.

score 14 · Accepted Answer · edited Aug 11 '13 at 04:54

You need to add a MAX function in the SELECT clause for each column to be ordered in DESCending order. The inverse goes for columns ordered in ASCending order, you need to add MIN function in the SELECT clause.

Your Eloquent query has to include a raw select:

$issues = DB::table('issues')
    ->select(DB::raw('id, max(year) as year, max(yearly_issue) as yearly_issue, stock, created_at, updated_at, magazine_id'))
    ->groupBy('magazine_id')
    ->orderBy('year', 'desc')
    ->orderBy('yearly_issue', 'desc')
    ->take(10)
    ->get();

The only drawback is that you need to specify each column you want to retrieve. And do not use the * selector, it will override the aggregate function in the select clause.

Update: Seems like adding the * selector before the aggregate functions in the SELECT clause works too. This means that you rewrite the raw select as:

->select(DB::raw('*, max(year) as year, max(yearly_issue) as yearly_issue'))

I think putting the * selector before makes the aggregate functions overrides their columns.

Awesome! =) That saved my weekend. – Andreas Aug 11 '13 at 07:30 — Andreas, Aug 11 '13 at 07:30
Uooow! That was really a life saver. Thanks a lot! – Bruno Gomes Aug 22 '14 at 14:57 — Bruno Gomes, Aug 22 '14 at 14:57

score 4 · Answer 2 · answered Sep 10 '14 at 19:31

I was looking for a way to order results before grouping and this answer kept popping up. In my case, it was for a user's message inbox. I needed to display the latest message received by the user in each message thread they were part of. My table looks like this:

--------------------------------------------------------------------
| id | sender_id | receiver_id | message | created_at | updated_at |
--------------------------------------------------------------------
|    |           |             |         |            |            |
--------------------------------------------------------------------

The accepted answer on this question, groups the results and then sorts them. I needed to sort the results by the created_at field, descending, before grouping them so I'd get the latest message from each thread. Anyway, here was my final solution after a lot of digging:

$sub = $query->orderBy('created_at', 'desc');
return $query->select('*')
             ->from(DB::raw("({$sub->toSql()}) as sub"))
             ->groupBy('sender_id');

This constructs the following query:

select * from (
    select * from `messages`
    order by `created_at` desc
) as sub
group by `sender_id`

Thanks a lot, it didn't worked for my specific need, the ->tosql parser isn't parsing the parameters quite well, but running a simple raw sql query solved it for me, added my own solution :) — Guy Mazuz, Mar 24 '15 at 20:28

score 0 · Answer 3 · answered Mar 23 '15 at 17:16

In MySQL you can do something like this:

select * 
    from `UserTable` 
where userId=3
    group by `field` desc
    order by `dateActivityComplete` ;

In eloquent you would do something like this: ->groupBy('field','desc'), however, ordering the groupBy doesn't seem to be an option in eloquent the same as order by is.

Might be an instance where you have to use a raw query, or possibly the max function.

Wade

How to order grouped results using Eloquent?

3 Answers3

Linked