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.

Answer 1

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.