Как правильно написать запрос при помощи Query Builder?

Question

[VoRoN1999]

Использую Laravel 6.

Есть таблицы:

users - пользователи, в себе содержит id и другие поля.

achievements - достижения пользователя - привязывается по id пользователя. (У одного пользователя может быть несколько достижений) ключевые поля: event_id, participation_id, individuality_id (все id берутся из таблицы coefficients)

achievements_category - категория к которой относится достижение. (Привязывается по id достижения. 1 категория у 1 достижения)

coefficients - баллы, которые начисляются за достижение. (привязывается по 3 полям к таблице achievements) - имеет поле coefficient. Тип: double(3,1)

Задача.

Нужно вывести всех пользователей. У каждого пользователя:
1) Общее число достижений и баллы за них: Получаем все достижения. У каждого достижения 3 коэффициента, которые перемножаем между собой и округляем до десятых. Сортируем пользователей по полученной цифре.
2) Топ 3 категории. Получаем все достижения по категориями, выводим 3 лучшие по баллам, которые считаем аналогично первому пункту.

Мой запрос:

User::query()
            ->select('achievements.user_id',
                DB::raw('round(sum(k1.coefficient * k2.coefficient * k3.coefficient), 1) as points'),
                'users.*')
            ->whereNotNull('email_verified_at')
            ->with(['getAchievements' => function ($query) use ($request) {
                $query->where([['achievements.status', Achievement::STATUS_SUCCESS], ['achievements.is_approved', 1]])
                    ->select(['category_id', 'user_id', 'achievements_category.name',
                        DB::raw('round(sum(k1.coefficient * k2.coefficient * k3.coefficient), 1) as points')])
                    ->join('achievements_category', function ($join) {
                        $join->on('achievements.category_id', '=', 'achievements_category.id');
                    })
                    ->join('coefficients as k1', 'achievements.event_id', '=', 'k1.id')
                    ->join('coefficients as k2', 'achievements.participation_id', '=', 'k2.id')
                    ->join('coefficients as k3', 'achievements.individuality_id', '=', 'k3.id')
                    ->groupBy(['category_id', 'user_id'])->orderByDesc('points')->limit(3);
            }])
            ->rightJoin('achievements', function ($join) {
                $join->on('users.id', '=', 'achievements.user_id')
                    ->where([['achievements.status', Achievement::STATUS_SUCCESS], ['achievements.is_approved', 1]])
                    ->join('coefficients as k1', 'achievements.event_id', '=', 'k1.id')
                    ->join('coefficients as k2', 'achievements.participation_id', '=', 'k2.id')
                    ->join('coefficients as k3', 'achievements.individuality_id', '=', 'k3.id');
            })
            ->groupBy(['achievements.user_id'])
            ->orderByDesc('points')->paginate(15);

Он работает, но в нем есть 3 проблемы:

1) Дублирование запросов к БД (возможно получится избежать)
2) Неверно происходит умножение и округление. Должно получится -> 36,5, а итог 36,2

При такой структуре считает верно:

$achievement =  Achievement::query()
            ->select('achievements.id', DB::raw('sum(k1.coefficient * k2.coefficient * k3.coefficient) as points'))
            ->where([['status', Achievement::STATUS_SUCCESS], ['is_approved', 1]])
            ->join('coefficients as k1', 'achievements.event_id', '=', 'k1.id')
            ->join('coefficients as k2', 'achievements.participation_id', '=', 'k2.id')
            ->join('coefficients as k3', 'achievements.individuality_id', '=', 'k3.id')
            ->groupBy('achievements.id')
            ->where('user_id', $this->id)->get();

$achievement->sum(function ($count) {
                return round($count->points, 1);
            });

3) limit 3 работает глобально. Получается, что если у первого пользователя будет 3 достижения, то у других ТОП 3 уже не будет выводится. Если убрать лимит, то выводятся не ТОП 3 категории, а вообще все, которые есть у пользователя.

Answer 1

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

Предлагаю переделать структуру и денормализовать данные. При получении ачивки записывать в какую-то таблицу признак необходимости пересчета для пользюка, асинхронно пересчитывать по расписанию раз в минуту или с помощью очередей. Хотя если пересчет делается меньше условной полусекунды, можно и синхронно при получении ачивки это делать. Хранить топ три можно в виде json поля в той самой таблице (как и баллы). Каждый раз при обновлении страницы делать столько джоинов ради этого слишком накладно, очень большое соотношение read/write.
Ну а получать это все не через query builder, а с помощью eloquent и отношений.

Comments

[VoRoN1999]

Соглашусь, что есть смысл переделать структуру, но на данный момент мне нужно сделать, чтобы это хоть как-то работало.

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

VoRoN1999, так добавить таблицу, заполнить её и добавить процесс пересчета при получении ачивок - это тоже "будет работать". При этом трудозатраты час, ну два, если вместе с чтением документации. А то напоминает "точить некогда, пилить надо"