laravel - How to order grouped results using Eloquent? -

i've been trying figure out time , can't seem make work. have table looks similar 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 need sort (easy!) but, want 1 of each magazine (column magazine_id).

my eloquent query of is:

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

i thought adding groupby('magazine_id') help, seems though partially helps me. results not in correct order. so, question - there easy way around this?

i've been experimenting various answers similar questions fail implement it.

retrieving last record in each group

or

how latest record in each group using group by?

more appreciated.

edit: closest this:

select i1.*, c.name image, m.title title 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 issues group magazine_id) i2 on i1.magazine_id = i2.magazine_id , i1.year = i2.year -- , i1.yearly_issue = i2.yearly_issue i1.stock ($all ? '>=' : '>') 0 order i1.year desc, i1.yearly_issue desc limit $perpage 

however, not giving me desired result @ all.

you need add max function in select clause each column ordered in descending order. inverse goes columns ordered in ascending order, need add min function in select clause.

your eloquent query has include raw select:

$issues = db::table('issues')     ->select(db::raw('id, max(year) year, max(yearly_issue) yearly_issue, stock, created_at, updated_at, magazine_id'))     ->groupby('magazine_id')     ->orderby('year', 'desc')     ->orderby('yearly_issue', 'desc')     ->take(10)     ->get(); 

the drawback need specify each column want retrieve. , not use * selector, override aggregate function in select clause.

---

update: seems adding * selector before aggregate functions in select clause works too. means rewrite raw select as:

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

i think putting * selector before makes aggregate functions overrides columns.