使用SQL ROW_NUMBER在Laravel中应用聪明的渴望限制查询。

huangapple go评论62阅读模式
英文:

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(&#39;id&#39;)
-&gt;with([
&#39;posts&#39; =&gt; fn($query) =&gt; $query-&gt;select([&#39;id&#39;, &#39;user_id&#39;])-&gt;limit(4)
])
-&gt;limit(2)
-&gt;get();

使用SQL ROW_NUMBER在Laravel中应用聪明的渴望限制查询。

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 &lt;= 4 order by laravel_row

Now, my interest is to do it manually which is what I tried here:

User::select(&#39;id&#39;)
-&gt;with([
&#39;posts&#39; =&gt; fn($query) =&gt; $query-&gt;select([&#39;id&#39;, &#39;user_id&#39;])
-&gt;selectRaw(&quot;row_number() over (partition by `posts`.`user_id`) as laravel_row&quot;)
-&gt;where(&#39;laravel_row&#39;, &#39;&lt;=&#39;, 4)
-&gt;orderBy(&#39;laravel_row&#39;)
])
-&gt;limit(2)
-&gt;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 &#39;laravel_row&#39; in &#39;where clause&#39;
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` &lt;= 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(&#39;limit&#39;, function(int $value) {
if($this-&gt;parent-&gt;exists) {
$this-&gt;query-&gt;limit($value);
} elseif ($value &gt;= 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&#39;t happen
$parentLimitValue = $this-&gt;query-&gt;getQuery()-&gt;limit; // null
// $parentLimitValue = $this-&gt;parent-&gt;getQuery()-&gt;limit; // null
// $parentLimitValue = $this-&gt;related-&gt;getQuery()-&gt;limit; // null
$parentLimitValue ??= 1;
$this-&gt;query
-&gt;selectRaw(&quot;row_number() over (partition by &quot;.$this-&gt;getExistenceCompareKey().&quot;) as laravel_row&quot;)
-&gt;orderBy(&#39;laravel_row&#39;)
-&gt;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(&#39;id&#39;)
-&gt;with([
&#39;posts&#39; =&gt; fn($query) =&gt; $query-&gt;select([&#39;id&#39;, &#39;user_id&#39;])
-&gt;selectRaw(&quot;row_number() over (partition by `posts`.`user_id`) as laravel_row&quot;)
-&gt;orderBy(&#39;laravel_row&#39;)
-&gt;limit(4) 
])
-&gt;limit(2)
-&gt;get();

UPDATE

To limit users and posts per users use this:

$usersCount = 2;
$postsPerUser = 4;
$users = User::select(&#39;id&#39;)
-&gt;with([
&#39;posts&#39; =&gt; fn($query) =&gt; $query-&gt;select([&#39;id&#39;, &#39;user_id&#39;])
-&gt;selectRaw(&quot;row_number() over (partition by `posts`.`user_id`) as laravel_row&quot;)
-&gt;limit($usersCount * $postsPerUser) 
-&gt;orderBy(&#39;laravel_row&#39;)
])
-&gt;limit($usersCount)
-&gt;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();

只有当查询在两个表(即usersposts)上都有足够的结果时,您才能获得使用此特性的好处。否则,您将因为在这里执行了UsersLimit x PostsLimit而获得额外的结果,因此请相应地设置查询。

英文:

As per @Igor's response, I made it more handy by placing it in traits & local scopes.

App\Traits\WithEagerLimit.php

&lt;?php
namespace App\Traits;
use Illuminate\Database\Eloquent\Builder;
trait WithEagerLimit
{
public function scopeWithEagerLimit(Builder $builder, string $relation, callable $callback)
{
return $builder-&gt;with([ $relation =&gt; function($query) use ($builder, $callback) {
$limit = $builder-&gt;getQuery()-&gt;limit ?: 1;
// Illuminate\Database\Eloquent\Relations\Relation
$query = call_user_func_array($callback, [$query]);
// Illuminate\Database\Query\Builder
$dbQB = $query-&gt;getQuery()-&gt;getQuery();
if(!$dbQB-&gt;limit) {
return $query;
}
$dbQB-&gt;limit *=  $limit;
return $query-&gt;when(is_null($dbQB-&gt;columns), fn($q) =&gt; $q-&gt;select(&#39;*&#39;))
-&gt;selectRaw(&quot;row_number() over (partition by &quot;.$query-&gt;getExistenceCompareKey().&quot;) as laravel_row&quot;)
-&gt;orderBy(&#39;laravel_row&#39;);
}]);
}
}

Use the WithEagerLimit trait on the corresponding model.

For example:

class User extends Model {
use \App\Traits\WithEagerLimit;
//...
public function posts()
{
return $this-&gt;hasMany(Post::class);
}
}

Now, you can chain withEagerLimit() to any of your queries by doing the following:

Simple eager loading functionality

User::withEagerLimit(&#39;posts&#39;, fn($query) =&gt; $query)-&gt;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()
-&gt;withEagerLimit(&#39;posts&#39;, fn($query) =&gt; $query-&gt;select([&#39;id&#39;, &#39;user_id&#39;])-&gt;where(&#39;id&#39;, &#39;&lt;=&#39;, 5))
-&gt;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` &lt;= 5

Complex SQL RAW query on eager loading functionality

User::query()
-&gt;select(&#39;id&#39;)
-&gt;withEagerLimit(&#39;posts&#39;,
fn($query) =&gt; $query-&gt;select([&#39;id&#39;, &#39;user_id&#39;])
-&gt;whereRaw(&#39;MOD(id, 2) = 0&#39;)
-&gt;limit(4)
)
-&gt;whereRaw(&#39;MOD(id, 2) = 1&#39;)
-&gt;skip(3)
-&gt;take(2)
-&gt;latest(&#39;id&#39;)
-&gt;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()
-&gt;withEagerLimit(&#39;posts&#39;, fn($query) =&gt; $query-&gt;limit(4))
-&gt;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 -&gt;limit($value) or an equivalent method (i.e. take(), skip(), paginate(), or similar procedure) in any queries chained with -&gt;withEagerLimit($relationName, $callback) in the main-query and also in sub-query.

User::query()
-&gt;select(&#39;id&#39;)
-&gt;withEagerLimit(&#39;posts&#39;, fn($query) =&gt; $query-&gt;select([&#39;id&#39;, &#39;user_id&#39;])-&gt;limit(4))
-&gt;limit(2)
-&gt;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.

huangapple
  • 本文由 发表于 2023年7月23日 21:26:16
  • 转载请务必保留本文链接:https://go.coder-hub.com/76748486.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定