英文:
Apply eloquent eager limit query using SQL ROW_NUMBER in Laravel
问题
以下是您要求的代码的翻译部分:
如@JonasStaudenmeir在[laravel eager loading with limit][1]上回答的那样,查询如下所示:
User::select('id')
->with([
'posts' => fn($query) => $query->select(['id', 'user_id'])->limit(4)
])
->limit(2)
->get();
[![enter image description here][2]][2]
从`users`中选择`id`限制为2
选择*从(选择`id`,`user_id`,按`posts`.`user_id`分区的row_number() over as laravel_row from `posts` where `posts`.`user_id`在(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][3])获得了一些帮助,但是语法没有正确形成,所以也失败了。
SQLSTATE[42S22]: 在‘where clause’中未找到列‘laravel_row’
选择`id`,`user_id`,row_number() over as laravel_row from `posts` where `posts`.`user_id`在(1,3)和`laravel_row` <= 4 order by `laravel_row` asc
**更新:**
遵循@Igor的建议,我尝试使其类似于该包并更方便,但未达到预期的输出。
*App\Providers\AppServiceProvider.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) {
// 当我在模型(User)上尝试时,下面的逻辑成功了
// 预期的是2,因为总共获取的用户数量是2,但是没有发生
$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;
});
}
**有人知道我应该把注意力放在哪里以减少这个包吗?**
[1]: https://stackoverflow.com/questions/33607088/laravel-eager-loading-with-limit#52991461
[2]: https://i.stack.imgur.com/CLc6g.png
[3]: https://jjlabajo.github.io/SQLtoEloquent/
英文:
As @JonasStaudenmeir answered on laravel eager loading with limit, which query looks like:
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
Now, my interest is to do it manually which is what I tried here:
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();
Also, I take some help from online (SQLtoEloquent), but the syntax was not properly formed so it failed there too.
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
UPDATE:
Following @Igor's advice, I tried to make it similar to the package and more convenient but didn't match the expected output.
App\Providers\AppServiceProvider.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;
});
}
Does anyone know where I should put my eyes to minimize this package?
答案1
得分: 2
我认为你需要在子查询中移除 where 并添加 limit。
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();
英文:
I think you need to remove where and add limit into the subquery
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();
UPDATE
To limit users and posts per users use this:
$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();
答案2
得分: 0
根据@Igor的回复,我通过将它放在traits和本地范围中使它更加方便。
App\Traits\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
对于那些急切等待答案的人
您应该在主查询和子查询中的任何与withEagerLimit($relationName, $callback)
链接的查询中调用->limit($value)
或等效方法(例如take()
,skip()
,paginate()
或类似的过程)。
User::query()
->select('id')
->withEagerLimit('posts', fn($query) => $query->select(['id', 'user_id'])->limit(4))
->limit(2)
->get();
只有当查询在两个表(即users和posts)上都有足够的结果时,您才能获得使用此特性的好处。否则,您将因为在这里执行了UsersLimit x PostsLimit
而获得额外的结果,因此请相应地设置查询。
英文:
As per @Igor's response, I made it more handy by placing it in traits & local scopes.
App\Traits\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');
}]);
}
}
Use the WithEagerLimit
trait on the corresponding model.
For example:
class User extends Model {
use \App\Traits\WithEagerLimit;
//...
public function posts()
{
return $this->hasMany(Post::class);
}
}
Now, you can chain withEagerLimit()
to any of your queries by doing the following:
Simple eager loading functionality
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)
Conditions on eager loading functionality
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
Complex SQL RAW query on eager loading functionality
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
Pagination on eager loading functionality
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
For those eagerly waiting for an answer
You should call ->limit($value)
or an equivalent method (i.e. take()
, skip()
, paginate()
, or similar procedure) in any queries chained with ->withEagerLimit($relationName, $callback)
in the main-query and also in sub-query.
User::query()
->select('id')
->withEagerLimit('posts', fn($query) => $query->select(['id', 'user_id'])->limit(4))
->limit(2)
->get();
You get the benefits of using this trait only when the query meets enough results on both the tables i.e. users & posts. Otherwise, you will get extra results out of the box because here we have done UsersLimit x PostsLimit
, so keep the query accordingly.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论