Применить красноречивый запрос ограничения с использованием SQL ROW_NUMBER в Laravel

Я думаю, вам нужно удалить 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();

Согласно ответу @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, поэтому сохраните запрос соответствующим образом.