How to organize grouped results using Eloquent?

Question

I have been trying to figure this out for some time and just can't get it to work. I have a table that looks like this.

Table: Problems

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 (just!), But I want to get only one journal (column magazine_id).

My current search request is:

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

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

I experimented with various answers to similar questions, but I was completely unable to implement it.

Getting the last record in each group

or

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

Additional help would be greatly appreciated.

EDIT: The closest I currently have is:

 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, he does not give me the desired result at all.

Answer 1

You need to add the MAX function to the SELECT for each column ordered in DESC final order. Inverse to columns ordered in ASC ending order, you need to add the MIN function to the SELECT .

Raw should be included in your Eloquent request:

 $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 that you want to get. And do not use the selector * , it will override the aggregate function in the select clause.

---

Update . It seems to add a selector * until aggregate functions are created in the SELECT . This means that you rewrite raw select as:

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

I think putting a selector * before aggregated functions redefine their columns.

Answer 2

I was looking for a way to organize the results before grouping, and this answer continued to appear. In my case, this was for user inbox. I needed to display the last message received by the user in each message flow of which they were a part. 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, in descending order, before grouping them so that I get the last message from each thread. Anyway, here was my final decision after a big dig:

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

This creates the following query:

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

Answer 3

In MySQL, you can do something like this:

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

In eloquence you will do something like this: -> groupBy ('field', 'desc'), however, groupBy ordering does not seem to be an eloquent option, the same as for order.

There may be an instance in which you should use a raw request, or perhaps a maximum function.

Wade