Написать запрос с подсчетом рейтингов и группировкой по компаниям
Завершена
Проект: Городские и общественные бани
| Планируемый запуск: | Дата начала: | 07.03.2021 | Планируемое завершение: | Дата завершения: | 07.03.2021 |
Описание задачи
| Проект | Задача | Статус | Дата постановки | Начало | Конец | Кто создал |
|---|---|---|---|---|---|---|
| Городские и общественные бани | Написать запрос с подсчетом рейтингов и группировкой по компаниям | Завершена | 07.03.2021 10:18:32 | 07.03.2021 21:52:16 | 07.03.2021 23:11:24 | |
| Городские и общественные бани | Написать запрос с подсчетом рейтингов и группировкой по компаниям | Завершена | 07.03.2021 10:18:32 | 07.03.2021 17:05:19 | 07.03.2021 20:54:07 | |
| Городские и общественные бани | Написать запрос с подсчетом рейтингов и группировкой по компаниям | Завершена | 07.03.2021 10:18:32 | 07.03.2021 12:03:52 | 07.03.2021 12:39:26 | |
| Городские и общественные бани | Написать запрос с подсчетом рейтингов и группировкой по компаниям | Завершена | 07.03.2021 10:18:32 | 07.03.2021 10:18:35 | 07.03.2021 11:43:05 |
Вот это был подводный камень... При плановой оценке в часик, было потрачено более 6 часов с усердными изысканиями.
Задача стояла в формировании выборки средних рейтингов по компаниями и типам оценок.
Призма хоть и умеет уже в группировку, но весьма ограничено и с сортировкой в таких случаях все очень плохо. Вот такой запрос был на призме:
return ctx.prisma.bani684_society_votes.groupBy({
by: [
"type",
"target_id",
],
avg: {
vote_value: true,
},
where: {
target_class: "modResource",
type: {
not: null,
},
},
})
Но проблема в первую очередь в том, что сортировка в призме в groupBy возможна только по полям, указанным в by (то есть по которым группируется), но не по значениям avg и т.п. (а надо-то именно по ним, чтобы от больших оценок к меньшим отсортировать).
Вторая проблема в том, что к такой конструкции призма не позволяет добавить select(), то есть выборку дополнительных полей. В моем случае надо было сразу получить и данные компаний, чтобы не делать потом кучу запросов на получение данных компаний для каждого результата в отдельности.
Сначала быстро накидал чистый SQL-запрос, но там нет типизации нифига, то есть результат просто как any. А хотелось, чтобы с проверкой типов и т.п. В итоге подключил knex. Тот тоже уже немного в ТС умеет, но все же во многом путается. Пришлось конструировать 3-хуровневую конструкцию, чтобы с типами было боле менее ОК. Конечно не идеально, но в целом весьма удовлетворительно получилось. Вот такой запрос получился: https://github.com/gorodskie-bani-ru/nextjs/blob/078e051afeafc76e19c804a88ba3da284972952e/server/nexus/types/Query/definitions/society/Vote/index.ts#L80-L195
На выходе вот такой SQL:
select
`type`,
`target_id`,
`vote_value_avg`,
`company_id`,
`company_pagetitle`,
`company_createdby`,
`company_createdon`,
`company_published`,
`company_searchable`,
`company_template`,
`company_description`,
`company_longtitle`,
`company_image`
from
(
select
`votes` .*,
`company`.`id` as `company_id`,
`company`.`pagetitle` as `company_pagetitle`,
`company`.`createdby` as `company_createdby`,
`company`.`createdon` as `company_createdon`,
`company`.`description` as `company_description`,
`company`.`longtitle` as `company_longtitle`,
`company`.`published` as `company_published`,
`company`.`searchable` as `company_searchable`,
`company`.`template` as `company_template`,
`tvs_image`.`value` as `company_image`
from
(
select
avg(`vote_value`) as `vote_value_avg`,
`type`,
`target_id`
from
`bani684_society_votes`
where
`type` is not null
and `target_id` is not null
group by
`type`,
`target_id`
order by
`vote_value_avg` desc) as `votes`
inner join `bani684_site_content` as `company` on
`company`.`id` = `target_id`
left join `bani684_site_tmplvar_contentvalues` as `tvs_image` on
`tvs_image`.`tmplvarid` = 3
and `tvs_image`.`contentid` = `company`.`id`) as `t`
Кстати, вот здесь можно поиграться с knex: https://dgadelha.github.io/knex-playground/