Apply eloquent eager limit query using SQL ROW_NUMBER in Laravel

Question

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?

Answer 1

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