CODEWITHSUNDEEP
phpmysqllaraveleloquent
just_chris
just_chris

Reputation: 154

GroupBy Email & get earliest created_at date in Group - Laravel Eloquent

How can I get the earliest created_at date when using groupBy in an eloquent statement?

I have a table with the following:

| id | name   | email   | created_at          |
|----|--------|---------|---------------------|
| 1  | test   | [email protected] | 2018-01-01 09:00:00 |
| 2  | test   | [email protected] | 2018-01-02 09:00:00 |
| 3  | test 2 | [email protected] | 2018-01-02 09:00:00 |
| 4  | test 2 | [email protected] | 2018-01-03 09:00:00 |
| 5  | test   | [email protected] | 2018-01-03 09:00:00 |
| 6  | test   | [email protected] | 2018-01-04 09:00:00 |
| 7  | test   | [email protected] | 2018-01-05 09:00:00 |
| 8  | test 3 | [email protected] | 2018-01-06 09:00:00 |
| 9  | test 3 | [email protected] | 2018-01-07 09:00:00 |

when running the following query:

Customers::OrderBy('created_at', 'desc')->groupBy('email')->paginate('5');

I was expecting to get the results in the following order:

Test 3 (created: 2018-01-07)
Test   (created: 2018-01-05)
Test 2 (created: 2018-01-03)

But instead I am getting so it doesn't look like it's respecting the OrderBy 

Test 3 (created: 2018-01-06)
Test 2 (created: 2018-01-02)
Test   (created: 2018-01-01)

Here is the SQL fiddle http://sqlfiddle.com/#!9/89ee39/2

# EDIT - Answer based on response

Thanks to Nesku, finally query to get this working was: 

 return DB::table('customers')
        ->select(DB::raw('email, max(created_at)'))
        ->groupBy('email')
        ->orderBy('max(created_at)', 'desc')
        ->get();

Upvotes: 0

Views: 923

Answers (1)

Nesku
Nesku

Reputation: 501

You can't order by created_at because it contains multiples values, you can however take the max of created_at for each email and order by that.

The SQL query would look like this :

SELECT email, max(created_at)
FROM `customers`
GROUP BY email
ORDER BY max(created_at) DESC

http://sqlfiddle.com/#!9/89ee39/12

Upvotes: 2

Related Questions