Задать вопрос
@skeevy
Frontend WebDev
mysql

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

Добрый день.
Практикуюсь в работе с 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 - база с данными для примера
  • Вопрос задан
  • 284 просмотра
7 комментариев
Подписаться 1 Средний 7 комментариев
Решения вопроса 1
@remzalp
Программер чего попало на чем попало
имеет смысл сначала довести 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 база данных

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

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
Вакансии с Хабр Карьеры
Программист C++ Builder / базы данных MySQL
RU Electronics • Москва
от 180 000 до 200 000 ₽
Инженер технической поддержки с английским языком и знанием PHP/MySQL
IT-Aces
от 100 000 до 150 000 ₽
Системный аналитик
ROBOTMIA • Новосибирск
от 100 000 ₽
Ещё вакансии
Заказы с Хабр Фриланса
Починить лайки и удаление сторис на React Native
18 апр. 2024, в 15:52
2500 руб./за проект
Здравствуйте требуется переводчик на португальский
18 апр. 2024, в 15:39
500 руб./в час
Сделать публикация в play market
18 апр. 2024, в 15:32
1000 руб./за проект
Ещё заказы