Laravel 实现最大 n-per-group

Question

用户表：| id |       name | age ||----|------------|-----||  1 |      Apple |  22 ||  2 | Strawberry |  23 ||  3 |     Orange |  50 ||  4 |      Mango |  30 |会员表：| 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 |问题我想要选择具有最新“expire_at”的用户。参考：https : //stackoverflow.com/a/2111420/5588637 后，我尝试了以下操作：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;结果的id，因为我曾经将出现两次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 |更改 m1.*为 后m1.expire_at。我得到了我想要的结果。| 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 |在线试用：http : //sqlfiddle.com/#!9/27fa22/4在 Lavavel 中实现Laravel 框架版本：5.6.39比较结果，Laravel 结果缺少第二个 id，即成员资格表 id，我想这是结果不正确的原因。我在网上搜了一下，好像是这个问题。 https://github.com/laravel/framework/issues/4962但是经过各种尝试我都失败了......

Answer 1

您不能在 Laravel 中选择具有相同名称的两行。第二个将覆盖第一个。请改用别名。

$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();

注意：我还在连接中封装了orWhere()，以避免混淆 AND/OR 的顺序。

同样有效的是在选择中使用不同的顺序。例如，您可以使用以下内容：

$query->select([

    'm1.*',

    'm1.id as membership_id',

    'u.*'

])

它将返回两个表的所有列以及新membership_id列。但是如果users表上有一列的名称与表上的列相似memberships，则只users返回表列（例如created_at）。返回列表中最后一个。

Answer 2

编辑：

正如@Namoshek 所提到的，您不应该选择所有内容，因为您的 SQL 查询中存在重复键问题。我修改了我的答案，使其与@RaymondNijland 的答案相匹配。顺便说一句，即使对于表用户，您也应该准确地选择您需要的内容。不仅针对重复键问题，还针对 SQL 查询的速度。我们考虑得还不够多，但它可以很快对大量结果产生影响。

从数据库发送到 PHP 服务器的数据更少 = 更快

你应该试试这个：

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()

如本页 Laravel 文档中所述：https ://laravel.com/api/5.8/Illuminate/Database/Query/JoinClause.html#method_on

您可以将闭包传递给该on()方法，并且orOn()您可以在此闭包中使用该方法。

我测试了它，它给出了与您的 SQL 查询相同的结果。