Laravel Implement greatest-n-per-group

Question

User table:

| id |       name | age |
|----|------------|-----|
|  1 |      Apple |  22 |
|  2 | Strawberry |  23 |
|  3 |     Orange |  50 |
|  4 |      Mango |  30 |

Memberships table:

| id | user_id |            expire_at |
|----|---------|----------------------|
|  1 |       1 | 2019-08-17T11:19:30Z |
|  2 |       1 | 2019-08-10T11:20:10Z |
|  3 |       2 | 2019-08-29T11:20:19Z |
|  4 |       3 | 2019-08-02T11:20:30Z |
|  5 |       3 | 2019-08-28T11:20:40Z |

---

Problom

I want select users with the latest 'expire_at'.

After reference: https://stackoverflow.com/a/2111420/5588637, I tried the following:

SELECT
    u.*,
    m1.* 
FROM
    users u
    INNER JOIN memberships m1 ON u.id = m1.user_id
    LEFT JOIN memberships m2 ON u.id = m2.user_id 
    AND ( 
        m1.expire_at < m2.expire_at 
        OR m1.expire_at = m2.expire_at 
        AND m1.id < m2.id 
        ) 
WHERE
    m2.id IS NULL;

Result

The id will appear twice because I used to join.

| id |       name | age | id | user_id |            expire_at |
|----|------------|-----|----|---------|----------------------|
|  1 |      Apple |  22 |  1 |       1 | 2019-08-17T11:19:30Z |
|  2 | Strawberry |  23 |  3 |       2 | 2019-08-29T11:20:19Z |
|  3 |     Orange |  50 |  5 |       3 | 2019-08-28T11:20:40Z |

After change m1.* to m1.expire_at. I got the result I want.

| id |       name | age |            expire_at |
|----|------------|-----|----------------------|
|  1 |      Apple |  22 |  2019-08-17T11:19:30Z|
|  2 | Strawberry |  23 | 2019-08-29T11:20:19Z |
|  3 |     Orange |  50 | 2019-08-28T11:20:40Z |

online try: http://sqlfiddle.com/#!9/27fa22/4

---

Implement in Lavavel

Laravel Framework version: 5.6.39

I am trying to convert the above SQL into Laravel using Database: Query Builder.

$users = DB::table('users as u')
            ->select('u.*', 'm1.*')
            ->join('memberships as m1','u.id','=','m1.user_id')
            ->leftJoin('memberships as m2', function($join){
                $join->on('u.id', '=', 'm2.user_id')
                ->where(function ($query) {
                    $query->where('m1.expire_at','<','m2.expire_at')
                    ->orWhere('m1.expire_at','=','m2.expire_at')
                    ->where('m1.id','<','m2.id');
                });
            })
            ->whereNull('m2.id')
            ->toSQL();

I'm using toSql(). This will convert it to SQL first to make sure it's same of above SQL.

SELECT
    `u`.*,
    `m1`.* 
FROM
    `users` AS `u`
    INNER JOIN `memberships` AS `m1` ON `u`.`id` = `m1`.`user_id`
    LEFT JOIN `memberships` AS `m2` ON `u`.`id` = `m2`.`user_id` 
    AND (
        `m1`.`expire_at` < ? 
        OR `m1`.`expire_at` = ? 
        AND `m1`.`id` < ?
        ) 
WHERE
    `m2`.`id` IS NULL

? seems to be the characteristic of laravel, I believe it is same of above SQL.

when i change toSQL() to get(), the result following:

Collection { ▼
  #items: []
}

The above result is wrong, so i tried remove whereNull('m2.id') in Laravel code (WHERE m2.id IS NULL in SQL), let’s see what happened.

Laravel result

Collection { ▼
  #items: array:5 [▼
    0 => { ▼
      +"id": 1
      +"name": "Apple"
      +"age": "Eric Yiu SL"
      +"user_id": 1
      +"expire_at": "2019-08-10T11:20:10Z"
    }
    ...
]

Ideal result

| id |       name | age | id | user_id |            expire_at |
|----|------------|-----|----|---------|----------------------|
|  1 |      Apple |  22 |  2 |       1 | 2019-08-10T11:20:10Z |
|  3 |     Orange |  50 |  4 |       3 | 2019-08-02T11:20:30Z |
|  1 |      Apple |  22 |  1 |       1 | 2019-08-17T11:19:30Z |
|  2 | Strawberry |  23 |  3 |       2 | 2019-08-29T11:20:19Z |
|  3 |     Orange |  50 |  5 |       3 | 2019-08-28T11:20:40Z |

Comparing results, Laravel result missing second id which is memberships table id, i guess this is the reason of incorrect results.

I have searched the Internet, seems is this problem. https://github.com/laravel/framework/issues/4962

But I failed after various attempts...

Answer 1

You cannot select two rows with the same name in Laravel. The second one will override the first one. Use an alias instead.

$users = DB::table('users as u')
            ->select('u.*', 'm1.id as membership_id')
            ->join('memberships as m1','u.id','=','m1.user_id')
            ->leftJoin('memberships as m2', function($join){
                $join->on('u.id', '=', 'm2.user_id')
                    ->where(function ($query) {
                        $query->whereColumn('m1.expire_at','<','m2.expire_at')
                            ->orWhere(function ($query) {
                                $query->whereColumn('m1.expire_at','=','m2.expire_at')
                                    ->whereColumn('m1.id','<','m2.id');
                            });
                    });
            })
            ->whereNull('m2.id')
            ->get();

Note: I also encapsulated the orWhere() in the join to avoid confusion about the order of AND/OR.

What also works is using a different order in the select. You can for example use the following:

$query->select([
    'm1.*',
    'm1.id as membership_id',
    'u.*'
])

It will return all columns of both tables plus the new membership_id column. But if there is a column on the users table which is named similarly to a column on the memberships table, only the users table column is returned (e.g. created_at). What comes last in your list is returned.

Answer 2

EDIT:

As @Namoshek mentioned, you should not select everything because you have a duplicate key problem in your SQL query. I modified my answer so that it would match @RaymondNijland answer. And by the way, even for the table user, you should select exactly what you need. And not only for a duplicate key problem but also for the speed of your SQL query. We don't think about it enough but it can quickly make the difference on a big set of results.

Less data to send from the database to your PHP server = faster

---

You should try this one :

DB::table('users as u')
            ->select('u.*', 'm1.id as membership_id')
            ->join('memberships as m1','u.id','=','m1.user_id')
            ->leftJoin('memberships as m2', function ($join) {
                $join->on('u.id', '=', 'm2.user_id')
                    ->on(function($join) {
                        $join->on('m1.id', '<', 'm2.id')
                            ->on(function($join) {
                                $join->on('m1.expire_at', '<', 'm2.expire_at')
                                    ->orOn('m1.expire_at', '=', 'm2.expire_at');
                            });
                    });
            })
            ->whereNull('m2.id')
            ->toSQL()

As mentioned in Laravel's documentation on this page: https://laravel.com/api/5.8/Illuminate/Database/Query/JoinClause.html#method_on

You can pass a closure to the on() method and there is the orOn() method that you can use in this closure.

I tested it and it gives the same result as your SQL query.