Как правильно составить запрос mySQL с выборкой данных из другой таблицы?

Question

[Leatington]

У меня есть три таблицы:
- owners (администраторы)
- clients (клиенты, каждый относится к какому-то администратору (поле token связано с аналогичным полем в таблице owners), один клиент может также относиться к нескольким администраторам одновременно (может быть две записи)
- logs (логирование действий клиентов, записывается уникальный идентификатор (serial, связано с аналогичным полем в таблице clients)
Мне нужно получить все логи всех клиентов определённого администратора, передав в запросе его token. Помогите составить запрос, пожалуйста
Скриншоты базы данных для большего удобства

Comments

[FanatPHP]

Это ты элементарный джойн сложным запросом называешь?

[Leatington]

FanatPHP, да)

Answer 1

[Мирон]

select logs.id
, logs.serial
, logs.action
, logs.ip
, logs.date
from owners owners
inner join clients clients on clients.token = owners.token
inner join logs logs on logs.serial = clients.serial
where owners.token = 'abcd...'

Comments

[Leatington]

Спасибо, но если client относится к двум администраторам (имеет две записи), оно выводит все его записи, игнорируя связь

[Мирон]

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

Записи
--------
Админ
токен - 1
токен - 2
токен - 3

Клиент
Токен - 1 | Сериал 1
Токен - 2 | Сериал 1
Токен - 3 | Сериал 3

Лог
№ 1 | Сериал - 1 | Запись ...
№ 2 | Сериал - 1 | Запись ...
№ 3 | Сериал - 3 | Запись ...
№ 4 | Сериал - 3 | Запись ...
-----
Запрос по токен 1
Выводятся записи под № 1,2
-----
Запрос по токен 2
Выводятся записи под № 1,2
-----
Запрос по токен 3
Выводятся записи под № 3,4
----
Поясните, что неправильно.

[Leatington]

В логе serial связан с serial таблицы клиентов, а serial таблицы клиентов не может иметь два уникальных токена (структура таблицы клиентов)
Грубо говоря:
клиент 1 зашёл в сеть под токеном админа 123 - запись добавилась в лог
клиент 1 зашёл в сеть под токеном админа 321 запись добавилась в лог
клиент 2 зашёл в сеть под токеном админа 321 запись добавилась в лог

Когда админ 123 получает логи клиента 1 он не должен получить логи, которые клиент добавлял, будучи в сети под токеном админа 321. Я так понял структура логов неправильная и мне нужно добавить туда ещё и токен - я не добавлял его т.к думал, что это можно избежать, добавив связь, но как я понимаю, по другому это нереально?

[Мирон]

Верно)
Ещё одним недостатком запроса, который предложил выше, в случае, если у админа нет клиентов, он не выдаст ни одной строчки. Наверное надо прояснить, если строка с токеном админа и NULL значениями поможет в таком случае пояснить, что админ есть, но соответствующих записей пока не создано. Так же можно, наверное, подтвердить присутствие клиентов, у которых нет логов.
одним из возможных изменений будет, как Вы и предложили, поле в родстве logs, соответствующий запрос:
select logs.id
, logs.serial
, logs.action
, logs.ip
, logs.date
from owners owners
left join clients clients on clients.token = owners.token
left join logs logs on logs.serial = clients.serial
and logs.token = owners.token
where owners.token = 'abcd...'
-----
но такое изменение увеличит размер каждой записи logs. Если записи частые, то есть смысл сделать родство "sessions", занести в эту таблицу поле serial, поле token, а в logs ввести поле sessions, тогда запрос будет таким

select logs.id
, logs.serial
, logs.action
, logs.ip
, logs.date
from owners owners
left join clients clients on clients.token = owners.token
left join (sessions sessions
inner join logs logs on logs.session = clients.session)
on sessions.token = clients.token and sessions.serial = clients.serial
where owners.token = 'abcd...'

MySQL не силен в выполнении таких запросов, поэтому его лучше разбить на следующие подзапросы, с использованием временных таблиц

DROP IF EXISTS TEMPORARY TABLE temp_clients;
DROP IF EXISTS TEMPORARY TABLE temp_sessions;
DROP IF EXISTS TEMPORARY TABLE temp_logs1;
DROP IF EXISTS TEMPORARY TABLE temp_logs2;
CREATE IF NOT EXISTS TEMPORARY TABLE temp_clients (token, serial);
CREATE IF NOT EXISTS TEMPORARY TABLE temp_sessions (sessions, token, serial);
CREATE IF NOT EXISTS TEMPORARY TABLE temp_logs1 ( token, serial, id, session, action, ip, date);
CREATE IF NOT EXISTS TEMPORARY TABLE temp_logs2 ( token, serial, id, action, ip, date, important_owner_property);

INSERT INTO temp_clients (token, serial)
select owners.token
, clients.serial
from owners owners
left join clients clients on clients.token = owners.token
where owners.token = 'abc...';

INSERT INTO temp_sessions (session, token, serial)
select sessions.session
, clients.token
, clients.serial
from temp_clients clients
left join sessions sessions on sessions.token = clients.token and sessions.serial = clients.serial;

insert into temp_logs1
( token, serial, session, id, action, ip, date)
select sessions.token
, sessions.serial
, sessions.session
, logs.id
, logs.action
, logs.ip
, logs.date
from temp_sessions sessions
inner join logs logs on logs.session = sessions.session

insert into temp_logs2
( token, serial, id, action, ip, date)
select clients.token
, clients.serial
, logs.id
, logs.action
, logs.ip
, logs.date
from temp_clients clients
left join temp_logs1 logs on logs.token= clients.token and logs.serial = clients.serial;

update logs
set important_owner_property = owners.mportant_owner_property
from temp_logs2 logs
inner join owners owners on owners.token = logs.token
where owners.mportant_owner_property is not NULL;

SELECT * FROM temp_logs2;