Query builder - combining orderBy and groupBy

Question

I have a Laravel application, and I have a query that is supposed to function as follows.

company   date      period
apple | 2017-08-23 | live
apple | 2017-09-04 | live
apple | 2014-03-04 | history
enron | 1987-09-09 | history
tesla | 2017-07-04 | live
tesla | 2017-06-03 | live

It needs to order each company's entries by date descending, with the condition that period='live' and thus return the last "live" entry for each company. In the example above, this should return

company   date 
apple | 2017-09-04
tesla | 2017-07-04

I have tried to accomplish this using the query builder, as such:

return $query->where('period', '=', 'live')
        ->groupBy('company')
        ->orderBy('date','desc');

However, this first groups the results and then orders them, which gives unpredictable results. According to this question/answer, the answer lies in a subquery:

ORDER BY date and time BEFORE GROUP BY name in mysql

However, I haven't been able to incorporate this approach into my problem. Can you explain to me how to effectively resolve this issue, whether with a subquery or with a different approach, but without resorting to a raw query?

I think this is what you are looking for: https://laravel.com/api/5.5/Illuminate/Database/Query/Builder.html#method_selectSub — online Thomas, Jan 17 '18 at 13:39
@ThomasMoors Sorry, but I don't understand the syntax at all. Could you elaborate in an answer please? — sveti petar, Jan 17 '18 at 13:44
You just want to select `company, MAX(date)` grouping by `company`. You don't need to sort the results, unless you want the resulting groups to be sorted. — eggyal, Jan 17 '18 at 13:45

Tim Biegeleisen · Accepted Answer · 2018-01-17T15:19:52.313

Your current strategy for the desired output is correct, and you don't need a subquery. The following raw MySQL query should suffice:

SELECT company, MAX(date)
FROM companies
GROUP BY company

We can try the following Laravel code:

return $query->where('period', '=', 'live')
    ->groupBy('company')
    ->orderBy('date','desc')
    ->get(['company', DB::raw('MAX(date) AS max_date')]);

To the contrary of what you said in your question, if you query with ORDER BY and the underlying data in the table does not change then the results of the query should be completely reproducible.

Edit:

Contrary to your question, your comments indicate that you want the entire matching row for each max date per company. If so, then you really want the following raw MySQL query:

SELECT c1.*
FROM companies c1
INNER JOIN
(
    SELECT company, MAX(date) AS max_date
    FROM companies
    GROUP BY company
) c2
    ON c1.company = c2.company AND
       c1.date = c2.max_date

We can try writing the following Laravel code to handle this:

DB::table('companies')
    ->select('*')
    ->join(DB::raw('(SELECT company, MAX(date) AS max_date
                     FROM companies GROUP BY company) c2'), function($join)
        {
            $join->on('companies.company', '=', 'c2.company');
            $join->on('companies.date', '=', 'c2.max_date');
        })
    ->orderBy('companies.date', 'DESC')
    ->get();

The query using MAX still returns the first row it finds instead of the last date one for some reason. I tried running it directly in phpMyAdmin to make sure it's not a problem with my application and it's not. — sveti petar, Jan 17 '18 at 13:52
To be precise, it returns the max date correctly, but it returns the rest of the row from the first row it found. I know in my example that's just the "company" column, but in reality I have a couple more columns which hold different values. So it's important to return the whole actual row of the last date, not just the date itself. Sorry for the confusion. — sveti petar, Jan 17 '18 at 13:55
@jovan I updated my answer, which hopefully covers your needs. — Tim Biegeleisen, Jan 17 '18 at 15:20
using a raw query is not even recommended by laravel itself. — sdebarun, Jan 18 '18 at 05:01

score 0 · Answer 2 · answered Jan 17 '18 at 13:46

0

Another solution could be to simply groupBy() the sorted query result (https://laravel.com/docs/5.5/collections#method-groupby) it entirely depends on the size of your resulting data set

answered Jan 17 '18 at 13:46

DrOhReally

11
2

score -2 · Answer 3 · answered Jan 17 '18 at 14:00

-2

https://laravel.com/docs/5.5/queries#retrieving-results look at this link. you should use DB::table('company')->where('period','live')->groupBy('your condition')->orderBy('your condition')->get(); it will return StdClass object. if you want an array add toArray() after get(); hope it works and let me know.

answered Jan 17 '18 at 14:00

sdebarun

99
8

3

I didn't give the downvote, but I would assume it's because the answer misses the point of the question completely. – sveti petar Jan 17 '18 at 15:55

Query builder - combining orderBy and groupBy

3 Answers3