提问人：JS TECH 提问时间：7/23/2023 最后编辑：JS TECH 更新时间：7/26/2023 访问量：171
在 Laravel 中使用 SQL ROW_NUMBER 应用雄辩的急切限制查询
Apply eloquent eager limit query using SQL ROW_NUMBER in Laravel
问：
正如@JonasStaudenmeir在 laravel eager loading with limit 上回答的那样，哪个查询如下所示：
User::select('id')
->with([
'posts' => fn($query) => $query->select(['id', 'user_id'])->limit(4)
])
->limit(2)
->get();
select `id` from `users` limit 2
select * from (select `id`, `user_id`, row_number() over (partition by `posts`.`user_id`) as laravel_row from `posts` where `posts`.`user_id` in (1, 3)) as laravel_table where laravel_row <= 4 order by laravel_row
现在，我的兴趣是手动完成，这就是我在这里尝试的：
User::select('id')
->with([
'posts' => fn($query) => $query->select(['id', 'user_id'])
->selectRaw("row_number() over (partition by `posts`.`user_id`) as laravel_row")
->where('laravel_row', '<=', 4)
->orderBy('laravel_row')
])
->limit(2)
->get();
另外，我从在线（SQLtoEloquent）中得到了一些帮助，但是语法没有正确形成，所以它在那里也失败了。
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'laravel_row' in 'where clause'
select `id`, `user_id`, row_number() over (partition by `posts`.`user_id`) as laravel_row from `posts` where `posts`.`user_id` in (1, 3) and `laravel_row` <= 4 order by `laravel_row` asc
更新：
按照 @Igor 的建议，我试图让它与包相似并且更方便，但与预期的输出不匹配。
应用\提供程序\应用服务提供程序.php
public function boot(): void
{
\Illuminate\Database\Eloquent\Relations\Relation::macro('limit', function(int $value) {
if($this->parent->exists) {
$this->query->limit($value);
} elseif ($value >= 0) {
// When I tried on the Model(User), it succeeded the below logic
// 2 was expected because the total number of users fetched is 2 but it didn't happen
$parentLimitValue = $this->query->getQuery()->limit; // null
// $parentLimitValue = $this->parent->getQuery()->limit; // null
// $parentLimitValue = $this->related->getQuery()->limit; // null
$parentLimitValue ??= 1;
$this->query
->selectRaw("row_number() over (partition by ".$this->getExistenceCompareKey().") as laravel_row")
->orderBy('laravel_row')
->limit($value * ($parentLimitValue ?: 1));
}
return $this;
});
}
有谁知道我应该把眼睛放在哪里以尽量减少这个包？
答：
2赞
Igor
7/24/2023
#1
我认为您需要删除 where 并在子查询中添加限制
User::select('id')
->with([
'posts' => fn($query) => $query->select(['id', 'user_id'])
->selectRaw("row_number() over (partition by `posts`.`user_id`) as laravel_row")
->orderBy('laravel_row')
->limit(4)
])
->limit(2)
->get();
更新
要限制用户和每个用户的帖子，请使用以下命令：
$usersCount = 2;
$postsPerUser = 4;
$users = User::select('id')
->with([
'posts' => fn($query) => $query->select(['id', 'user_id'])
->selectRaw("row_number() over (partition by `posts`.`user_id`) as laravel_row")
->limit($usersCount * $postsPerUser)
->orderBy('laravel_row')
])
->limit($usersCount)
->get();
评论
0赞
JS TECH
7/24/2023
是的！但问题是如何安排/获取数据。它不会为每个用户获得 4 个帖子，而是为每个用户获得 2-2 个帖子。所以，这不是我所期望的。
0赞
Igor
7/24/2023
尝试使用...->groupBy（'user_id'）->limit（4） ...进入子查询？
0赞
Igor
7/24/2023
好的，似乎最好创建 latestPost hasOne 关系并像那样做。分区依据似乎不适合您的情况。https://stackoverflow.com/a/33780740/3955714
1赞
JS TECH
7/24/2023
一种可能的解决方案是，我们可以欺骗您的答案，使其将两个极限值相乘，即 .通过这样做，它将匹配我的预期输出，但它会像 SQL 中的末尾一样进行查询。limit(4x2)limit(8)
0赞
JS TECH
7/24/2023
此外，您的 groupBy 方法向我抛出此错误。窗口函数仅在 SELECT list 和 ORDER BY 子句中允许使用。如果您能回答，请告知？
0赞
JS TECH
7/25/2023
#2
根据 @Igor 的回应，我把它放在特征和局部范围内，让它更方便。
应用\特征\WithEagerLimit.php
<?php
namespace App\Traits;
use Illuminate\Database\Eloquent\Builder;
trait WithEagerLimit
{
public function scopeWithEagerLimit(Builder $builder, string $relation, callable $callback)
{
return $builder->with([ $relation => function($query) use ($builder, $callback) {
$limit = $builder->getQuery()->limit ?: 1;
// Illuminate\Database\Eloquent\Relations\Relation
$query = call_user_func_array($callback, [$query]);
// Illuminate\Database\Query\Builder
$dbQB = $query->getQuery()->getQuery();
if(!$dbQB->limit) {
return $query;
}
$dbQB->limit *= $limit;
return $query->when(is_null($dbQB->columns), fn($q) => $q->select('*'))
->selectRaw("row_number() over (partition by ".$query->getExistenceCompareKey().") as laravel_row")
->orderBy('laravel_row');
}]);
}
}
在相应的模型上使用该特征。WithEagerLimit
例如：
class User extends Model {
use \App\Traits\WithEagerLimit;
//...
public function posts()
{
return $this->hasMany(Post::class);
}
}
现在，您可以通过执行以下操作链接到任何查询：withEagerLimit()
简单的快速加载功能
User::withEagerLimit('posts', fn($query) => $query)->get();
// SQL
select * from `users`
select * from `posts` where `posts`.`user_id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
预先加载功能的条件
User::query()
->withEagerLimit('posts', fn($query) => $query->select(['id', 'user_id'])->where('id', '<=', 5))
->get();
// SQL
select * from `users`
select `id`, `user_id` from `posts` where `posts`.`user_id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) and `id` <= 5
关于快速加载功能的复杂 SQL RAW 查询
User::query()
->select('id')
->withEagerLimit('posts',
fn($query) => $query->select(['id', 'user_id'])
->whereRaw('MOD(id, 2) = 0')
->limit(4)
)
->whereRaw('MOD(id, 2) = 1')
->skip(3)
->take(2)
->latest('id')
->get();
// SQL
select `id` from `users` where MOD(id, 2) = 1 order by `id` desc limit 2 offset 3
select `id`, `user_id`, row_number() over (partition by posts.user_id) as laravel_row from `posts` where `posts`.`user_id` in (1, 3) and MOD(id, 2) = 0 order by `laravel_row` asc limit 8
快速加载功能的分页
User::query()
->withEagerLimit('posts', fn($query) => $query->limit(4))
->paginate(5);
// SQL
select count(*) as aggregate from `users`
select * from `users` limit 5 offset 0
select *, row_number() over (partition by posts.user_id) as laravel_row from `posts` where `posts`.`user_id` in (1, 2, 3, 4, 5) order by `laravel_row` asc limit 20
对于那些急切等待答案的人
您应该在主查询和子查询中链接的任何查询中调用或等效方法（即 、 、 或类似过程）。->limit($value)take()skip()paginate()->withEagerLimit($relationName, $callback)
User::query()
->select('id')
->withEagerLimit('posts', fn($query) => $query->select(['id', 'user_id'])->limit(4))
->limit(2)
->get();
只有当查询在两个表（即用户和帖子）上都遇到足够的结果时，您才能获得使用此特征的好处。否则，您将获得开箱即用的额外结果，因为在这里我们已经完成了，因此请相应地保留查询。UsersLimit x PostsLimit
评论