Сложный запрос MySql: группировка и фильтрация в запросе?

Question

[Mikhail Derkach]

Добрый день.
Практикуюсь в работе с Rest API на реальном примере.
есть игровой сервер, задача: сделать базу данных для полиции, с поиском по пользователям и их данным. Наверное, типичный CRUD
База - Mysql 8, api сгенерировано через xmysql

Ввиду того, что данные пользователей находятся в разных таблицах, пришел к выводу, что нужно строить запрос через join.

Пример запроса

axios.get('http://***:3000/api/xjoin?_join=user.users,_lj,vehicle.owned_vehicles,_lj,property.owned_properties,_lj&_on1=(user.identifier,eq,vehicle.owner)&_on2=(user.identifier,eq,property.owner)&_fields=user.firstname,user.lastname,user.dateofbirth,user.phone_number,user.job,property.name,vehicle.plate,vehicle.type,vehicle.job&_size=1000')
          .then(function (response) {
              console.log(response)
          })

Пример ответа:

{
        "user_firstname": "A",
        "user_lastname": "B",
        "user_dateofbirth": "30",
        "user_phone_number": "42147",
        "user_job": "mechanic",
        "property_name": "NorthConkerAvenue2044",
        "vehicle_plate": "UVD 395",
        "vehicle_type": "helicopter",
        "vehicle_job": "ambulance"
    },
{
        "user_firstname": "A",
        "user_lastname": "B",
        "user_dateofbirth": "30",
        "user_phone_number": "42147",
        "user_job": "mechanic",
        "property_name": "Modern2Apartment",
        "vehicle_plate": "UVD 395",
        "vehicle_type": "helicopter",
        "vehicle_job": "ambulance"
    },
{
        "user_firstname": "C",
        "user_lastname": "D",
        "user_dateofbirth": "27",
        "user_phone_number": "15607",
        "user_job": "taxi",
        "property_name": null,
        "vehicle_plate": null,
        "vehicle_type": null,
        "vehicle_job": null
    },

Т.к. я не очень силен в mysql, даже сказал бы, очень не силен, у меня возникли следующие вопросы:

1) Возможно ли сгруппировать "property_name" и подобные ключи с разными value в один, чтобы не плодить кучу объектов одинаковых? И как правильно это сделать. Гуглом был найдет gorup_concat, но не понял как его подружить с моими требованиями. В апи есть поддержка dynamic query, куда по сути можно его вставить, переделав запрос под синтаксис самого mysql

2) Как правильно отфильтровать нерелевантные ответы?
Например, есть "vehicle_type": "helicopter", но мне нужен type: car или null, если машины нет. А если машина есть, то "vehicle_job" должен быть null. То же самое с проперти, сгруппировать в один, если несколько или вернуть null, если нет

Фильтруя прям в запросе с помощью

&_where=(vehicle.type,like,car)~and(vehicle.job,is,null)

у меня теряются нужные пользователи

Буду благодарен любой помощи

UPD: tiny.cc/o9thez - база с данными для примера

Comments

[PrAw]

Краткая реакция - да чтоб ты всю жизнь GET параметрами SQL запросы писал.
Исходные данные какие?
Какие таблицы, что надо получить?

[Mikhail Derkach]

PrAw, не умею в постиронию, уж простите
как в доке к апи написано получать, так и получаю)

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

Таблицы:
users - поля firstname, lastname, dateofbirth, phone_number, job
owned_vehicles - поля plate, type, job
owned_properties - поле name

лефт джоин по users.identifier и owned_vehicles/owned_properties.owner

Эквивалентный запрос в mysql

SELECT 
    user.firstname,
    user.lastname,
    user.dateofbirth,
    user.phone_number,
    user.job,
    property.name,
    vehicle.plate,
    vehicle.type,
    vehicle.job
FROM
    users AS user
        LEFT JOIN
    owned_vehicles AS vehicle ON user.identifier = vehicle.owner
        LEFT JOIN
    owned_properties AS property ON user.identifier = property.owner
LIMIT 0 , 10000

[PrAw]

Mikhail Derkach, ответил.
Усложняя получение информации из вопроса - снижаете шансы на ответ.

[Mikhail Derkach]

PrAw, ну, явно ироничные высказывания никак мне не помогают. Я конечно в этой сфере дурачок еще, и не понимаю, почему запрашивать гетом плохо, уж тем более, что сама дока мне говорить это делать

исходные данные я по максимуму предоставил: не будь там деликатных данных, по типу адреса сервера или стим идентификаторов игроков - мог бы отдать на потрошение всю базу, мне не тяжело. Но её и показать стыдно отчасти, т.к. там опенсорс моды со своими требованиями к бд и очень, очень много мусора. Я будучи не опытным в администрировании бд за голову хватаюсь, видя что там происходит. Что говорить о тех, кто явно разбирается в этом более меня

хочется понять, где не прав, что делаю не правильно и получить опыт на будущее. И явно мне не хочется кого-то насильно просить о помощи, триггерить и тому подобное, тем более пытаться прочитать между строк юмор, извлекая зерна рационального.

[PrAw]

Mikhail Derkach, идеальный комплект:
дамп структуры БД - все таблицы, участвующие в запросе + данные для вставки (insert) нескольких строк для демонстрации примера, причем комплект данных достаточный, что там и правильные и неправильные результаты при ожидаемом запросе можно получить.
Кстати, уже на этапе подготовки правильного вопроса резко может дойти правильное решение :)

xmysql - слишком узкий вопрос, скорей всего найдется полтора специалиста по нему, в то время как сам вопрос сводится больше к SQL, так что специалисту по SQL лучше давать в понятном для него виде.

В общем случае мне не нравится апи, которое делает больше, чем Вы ожидаете и торчит куда-то наружу, чтобы в него стучался фронтэнд. Это к утечке всей БД или куче возможностей отредактировать данные без Вашего ведома.

[PrAw]

Mikhail Derkach, заодно, пока глубоко не ушли - посмотрите в сторону CouchDB, MongoDB
возможно, смена БД даст больше удобства в работе

[Mikhail Derkach]

PrAw, приложил ссылку на бд с данными
по поводу апи: эта тулза больше для тренировки навыков и как раз стучусь к ней с фронта. по этому поводу у меня есть отдельные мысли, но они не по сути вопроса, да и сама работа не является коммерческим продуктом, где на это уже бы закрывать глаза не стали. Так, пет проект, набить руку и шишки, прежде чем браться за что-то серьезное

к сожалению, глубоко ушел, но сейчас уже придется долго упорно переписывать, если пересаживаться на ту же Mongo.

Answer 1

[PrAw]

имеет смысл сначала довести SQL запрос до нормального состояния, а потом уже переходить к апи.

Итого - есть пользователи, есть собственность, есть транспорт.

1. Есть определенная непонятность - если мы сгруппировали по юзеру, у него есть несколько объектов собственности во владении и несколько транспортных средств - сколько строк Вы хотите получить в итоге?

SELECT user.*,  
   GROUP_CONCAT(DISTINCT property.name ORDER BY property.name ASC SEPARATOR ', ') AS propertys,
....
   GROUP BY user.identifier ;

Это должно дать список собственности, но вот транспорт так легко не делается - там 3 поля, это уже на грани написания хранимой процедуры

2. Достаточно выводить только venicle_plate при условии если:

WHERE venicle_job is null AND (venicle_type is null or venicle_type='car')

в этом случае group_concat справится.
Навскидку, можно еще вложенными подзапросами сделать:

SELECT id,
 (SELECT GROUP_CONCAT(DISTINCT name  ORDER BY name ASC SEPARATOR ', ') FROM owned_properties WHERE users.identifier = owned_properties.owner GROUP BY owned_properties.owner ) as property,
 (SELECT GROUP_CONCAT(DISTINCT plate ORDER BY plate ASC SEPARATOR ', ') FROM owned_vehicles  
			WHERE job is null AND (type is null or type='car') AND 
			users.identifier = owned_vehicles .owner GROUP BY owned_vehicles .owner ) as venicle
  FROM users;

опять же, сильно похоже, что тут удобней будет документоориентированная noSQL база данных

примера БД под рукой нет, чтобы запросы отладить, но с виду жизнеспособно.

Comments

[Mikhail Derkach]

Спасибо за ответ.

1. Хочу получить одну строку, отсеяв лишнее. в случае в properties все проще

2. Буду пробовать, надеюсь, ваш посыл я понял

к сожалению, на текущий момент от mysql сейчас отказаться невозможно, т.к. это игровой сервер с плагинами, в т.ч. комьюнити плагинами и которые едва не каждый имеют в бд таблицу под свои нужды. Так что работаю с тем что есть

[PrAw]

Mikhail Derkach, скорей всего с вложенными подзапросами будет идеальным вариантом

[PrAw]

Mikhail Derkach, скорей всего вложенный подзапрос сейчас даже полностью корректно работает. И я не уверен, что через функционал библиотеки такое реально выполнить встроенными средствами.

[Mikhail Derkach]

PrAw, огромное вам спасибо :) буду вникать в запросы :)