Как правильно составить сложный запрос в Laravel?

Question

[Bodrosh]

Здравствуйте, можно ли в laravel составить запрос с получением связанных данных через несколько таблиц?
Есть таблицы:
Пользователи (users)
Проекты_пользователей (projects, связаны с user_id - у 1-го пользователя множество проектов)
Посты_пользователей (posts, связаны с project_id - у 1-го проекта множество постов)
Услуги_пользователей (services, связаны с user_id - у 1-го пользователя множество услуг)

Т.е. для получения услуг пользователя из конкретного поста такая схема $post->project->user->services()

Можно ли составить запрос так, чтобы получать все записи Посты_пользователей вместе со связанными данными Услуги_пользователей текущего поста (точнее данные поста + кол-во услуг пользователя, у которых определенный статус)

Речь не об SQL запросе, его составить не проблема, вопрос именно о реализации этого в Laravel, т.к. используется внутренняя пагинация (хочется использовать внутренний метод ->paginate()). Спасибо.

Comments

[Сергей delphinpro]

Не очень понятны связи

Посты_пользователей (разных пользователей) вместе со связанными данными Услуги_пользователей текущего поста

То есть услуги также связаны и с постами?

[Bodrosh]

Сергей delphinpro, Услуги не связаны с постами, но нужно отобразить статус услуг пользователя для каждого поста. На SQL такой получил (здесь берется сумма всех статусов услуг пользователя):

SELECT p.*, u.id, SUM(su.status) as sum_status
FROM `posts` as p 
LEFT JOIN projects as p ON p.id = p.project_id
LEFT JOIN users as u ON u.id = p.user_id
LEFT JOIN service_user as su ON su.user_id = u.id
GROUP BY p.id, su.user_id

[Антон Антон]

А вот этот раздел документации читал? https://laravel.com/docs/master/eloquent-relationships

[Bodrosh]

Антон Антон, Естественно)

[Bodrosh]

Сергей delphinpro, похоже, нашел решение, корректно на ваш взгляд?

DB::table('posts')
            ->leftJoin('projects', 'posts.project_id', '=', 'projects.id')
            ->leftJoin('users', 'projects.user_id', '=', 'users.id')
            ->leftJoin('service_user', 'users.id', '=', 'service_user.user_id')
            ->leftJoin('services', 'service_user.service_id', '=', 'services.id')
            ->groupBy('service_user.user_id', 'posts.id')
            ->select('posts.*', 'service_user.user_id', DB::raw("SUM(service_user.status) as sum_status") )
            ->paginate(30)

[Антон Антон]

Bodrosh, тогда в чем вопрос? как добавить with?

[Антон Антон]

Bodrosh,

Сергей delphinpro, похоже, нашел решение, корректно на ваш взгляд?

А говорите, что раздел документации читали (

[Дмитрий]

Bodrosh, если у вас группировка по posts.id, service_user.user_id то вы не можете выбрать posts.*

[Bodrosh]

Дмитрий, с DB::statement("SET SQL_MODE=''"); могу, а как исправить? В SELECT должно быть то же самое, что и в GROUP BY? (все поля?)

[Дмитрий]

Bodrosh, тоже что в group_by и можно добавлять агрегирующие функции.

А вот за игры с sql_mode - можно при деплое так хорошо по шапке схлопотать, мало не покажется.

[Bodrosh]

Дмитрий, Спасибо, небольшой вопрос - если в groupBy много полей и есть текстовые (title, data - longtext), это влияет на производительность или СУБД сама разберется что ей нужно?

->groupBy('service_user.user_id', 'posts.id', 'project_id', 'title', 'data', 'created_at', 'updated_at', 'type')

[Дмитрий]

Bodrosh, для того что бы понять чего там субд есть explain

[Bodrosh]

Дмитрий, спасибо, ознакомлюсь.

Answer 1

[Bodrosh]

Запрос SQL:

SELECT p.id, p.data, u.id as user_id, SUM(su.status) as sum_status
            FROM `posts` as p
            LEFT JOIN projects as p ON p.id = p.project_id
            LEFT JOIN users as u ON u.id = p.user_id
            LEFT JOIN service_user as su ON su.user_id = u.id
            GROUP BY su.user_id, p.id, p.data

Итоговый результат:

$posts = DB::table('posts')
            ->leftJoin('projects', 'posts.project_id', '=', 'projects.id')
            ->leftJoin('users', 'projects.user_id', '=', 'users.id')
            ->leftJoin('service_user', 'users.id', '=', 'service_user.user_id')
            ->leftJoin('services', 'service_user.service_id', '=', 'services.id')
            ->groupBy('service_user.user_id', 'posts.id', 'posts.data')
            ->select('posts.id',  'posts.data',  'service_user.user_id', DB::raw("SUM(service_user.status) as sum_status") )
            ->paginate(50);

Comments

[Dmitry Bay]

Это не решение.

запрос с получением связанных данных

Где у вас?
Собственно QueryBuilder и на выходе вы получаете массив данных где связные данные в жадной загрузке?

У вас будет eloquent модель, без полезных данных. Возможно лучше использовать -
->select('posts.*','service_user.user_id', DB::raw("SUM(service_user.status) as sum_status"))

[Dmitry Bay]

Плюс вашего метода только в том, что с ним удобнее работать в гите, и обновлениях, чем с чистым sql.

Answer 2

[ka4an90]

https://laravel.su/docs/8.x/queries#joins

вот тут почитай, есть join-ы простые, есть расширенные, дока вроде понятная, вопросов быть не должно