时间戳

首页 > 解决方案 > Laravel 实现每组最大 n

php - Laravel 实现每组最大 n

问题描述

用户表：

| 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

我正在尝试使用Database: Query Builder将上述 SQL 转换为 Laravel 。

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

我正在使用toSql(). 这将首先将其转换为 SQL，以确保它与上述 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

?似乎是laravel的特点，我相信上面的SQL是一样的。

当我更改toSQL()为时get()，结果如下：

Collection { ▼
  #items: []
}

上面的结果是错误的，所以我尝试在 Laravel 代码中删除 （在 SQL 中），让我们看看发生了什么。whereNull('m2.id')WHERE m2.id IS NULL

Laravel 结果

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

理想的结果

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

比较结果，Laravel 结果缺少第二个 id，即成员资格表 id，我猜这是结果不正确的原因。

我上网查了一下，好像是这个问题。 https://github.com/laravel/framework/issues/4962

但经过各种尝试，我失败了......

标签： phpmysqllaravel

解决方案

你不能在 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）。返回列表中最后的内容。

推荐阅读