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')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->select('u.*', 'm1.id as membership_id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->join('memberships as m1','u.id','=','m1.user_id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->leftJoin('memberships as m2', function($join){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $join->on('u.id', '=', 'm2.user_id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->where(function ($query) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $query->whereColumn('m1.expire_at','<','m2.expire_at')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->orWhere(function ($query) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $query->whereColumn('m1.expire_at','=','m2.expire_at')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->whereColumn('m1.id','<','m2.id');&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; })&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->whereNull('m2.id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->get();注意：我还在连接中封装了orWhere()，以避免混淆 AND/OR 的顺序。同样有效的是在选择中使用不同的顺序。例如，您可以使用以下内容：$query->select([&nbsp; &nbsp; 'm1.*',&nbsp; &nbsp; 'm1.id as membership_id',&nbsp; &nbsp; 'u.*'])它将返回两个表的所有列以及新membership_id列。但是如果users表上有一列的名称与表上的列相似memberships，则只users返回表列（例如created_at）。返回列表中最后一个。

Answer 2

编辑：正如@Namoshek 所提到的，您不应该选择所有内容，因为您的 SQL 查询中存在重复键问题。我修改了我的答案，使其与@RaymondNijland 的答案相匹配。顺便说一句，即使对于表用户，您也应该准确地选择您需要的内容。不仅针对重复键问题，还针对 SQL 查询的速度。我们考虑得还不够多，但它可以很快对大量结果产生影响。从数据库发送到 PHP 服务器的数据更少 = 更快你应该试试这个：DB::table('users as u')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->select('u.*', 'm1.id as membership_id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->join('memberships as m1','u.id','=','m1.user_id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->leftJoin('memberships as m2', function ($join) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $join->on('u.id', '=', 'm2.user_id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->on(function($join) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $join->on('m1.id', '<', 'm2.id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->on(function($join) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $join->on('m1.expire_at', '<', 'm2.expire_at')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->orOn('m1.expire_at', '=', 'm2.expire_at');&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; })&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->whereNull('m2.id')&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ->toSQL()如本页 Laravel 文档中所述：https ://laravel.com/api/5.8/Illuminate/Database/Query/JoinClause.html#method_on您可以将闭包传递给该on()方法，并且orOn()您可以在此闭包中使用该方法。我测试了它，它给出了与您的 SQL 查询相同的结果。