можно ли этот запрос написать иначе

[WMix]

есть 3 таблицы, клиенты, пользователи (клиентов) и права (пользователей)
у клиента несколько пользователей, у пользователя несколько прав, одно из прав "администратор"

Спойлер

mysql> select * from Customers;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | DB          |
|  2 | BVG         |
|  3 | Burger King |
+----+-------------+
3 rows in set (0,00 sec)

mysql> select * from Users;
+----+-------------+-----------+
| id | customer_id | nick      |
+----+-------------+-----------+
|  1 |           1 | DB_Admin  |
|  2 |           1 | DB_User   |
|  3 |           2 | BVG_Admin |
|  4 |           2 | BVG_User  |
+----+-------------+-----------+
4 rows in set (0,00 sec)

mysql> select * from Permissions;
+----+---------+------------+
| id | user_id | permission |
+----+---------+------------+
|  1 |       1 | admin      |
|  2 |       2 | other      |
|  3 |       3 | admin      |
|  4 |       4 | other      |
+----+---------+------------+
4 rows in set (0,00 sec)

необходимо найти всех клиентов и их администраторов

mysql> select c.name as customer, u.nick as administrator
    -> from Customers c
    -> left join (
    ->   Users u join Permissions p on
    ->   u.id = p.user_id and p.permission = 'admin'
    -> ) on u.customer_id = c.id;
+-------------+---------------+
| customer    | administrator |
+-------------+---------------+
| DB          | DB_Admin      |
| BVG         | BVG_Admin     |
| Burger King | NULL          |
+-------------+---------------+
3 rows in set (0,00 sec)

http://sqlfiddle.com/#!9/c06ed6/1/0

чисто теоретически, а можно ли этот запрос написать так, чтоб в join небыло join и без subselect?

 

[Yaponchick]

http://sqlfiddle.com/#!9/c06ed6/7

14ms vs 3ms ?

 

[WMix]

14ms vs 3ms ?

это не важно, мне надо было в orm запрос впиндюрить, а там нет nested join.
хотя за это время я уже хак влепил, (на один nested join). как это рекурсивно сделать, ума не приложу. нужно спокойно разбираться

 

[Yaponchick]

Ну так мой вариант без join внутри join и без подзапроса

 

[WMix]

http://sqlfiddle.com/#!9/8285fc/3 я создал пользователя (не админа) и пропала строка
конечно такой ситуации быть не должно, ну те без админа и пользователей нет, но запрос работает иначе

 

[Yaponchick]

http://sqlfiddle.com/#!9/8285fc/9/1

А если так? Условие в ON можно добавить в орм?

Ан нет, не будет всех админов...

 

[Yaponchick]

http://sqlfiddle.com/#!9/586621/1/1
Новая версия

Но это с условием в ON

 

[WMix]

Условие в ON можно добавить в орм?

да так можно, но тормозить будет (group by)...

 

[WMix]

в новой версии burger king пропал

 

[Yaponchick]

Не, дальше уже не осилю тут уже надо к "задротам" обращаться

 

[WMix]

там правильней было разделить на 2 запроса, а после смапить результаты, но нет же, простых путей не ищем

 

[WMix]

Третья нормальная форма спотыкается на таблице разрешений

а ну да, "user_id | permission" unique
или о чем ты?

вот какая то странная модель данных.

а ты как делаешь?

"разрешения" лучше было называть ролями

 

[Yaponchick]

Тогда вообще можно наоборот сделать
Customer < Users < UserGroups < User:UserGroup

Получится что там будет INNER JOIN между Customer/UserGroups, и будут все Кастомеры/Админы но не перебор ли? Проще уже 2 запроса сделать

Или

Customer < UserGroups < Users...

...

Но тогда мы получим обычную: Customer < (Users, Roles, UserRoles) =>

SELECT *
FROM customer AS c
INNER JOIN roles AS r
LEFT JOIN users AS u ON (c.id = u.customer_id AND r.id = u.id)
WHERE r.name = 'admin'

 

[Yaponchick]

Фидл не хочет отрабатывать

---

http://sqlfiddle.com/#!9/5a552e/4/0

 

[WMix]

http://sqlfiddle.com/#!9/2b1d6/4
я поправил, админ только 1, но да, с subselect получается тоже правильно

Я про то что админ текстом

это только в примере

 

[WMix]

Но тогда мы получим обычную: Customer < (Users, Roles, UserRoles) =>

изза INNER JOIN roles потеряем клиентов "без админа"

 

[Yaponchick]

изза INNER JOIN roles потеряем клиентов "без админа"

http://sqlfiddle.com/#!9/5a552e/4/0

Увы, но там есть Бургер кинг (INNER JOIN !== JOIN)

 

[grigori]

во-первых, group by не тормозит, если использует индекс,
во-вторых, тут задача сводится или к общей "многие ко многим", или к более узкой форме "entity-attribute-value", все магазины на таких запросах живут
только с ограничениями на консистентность надо определиться: могут ли быть юзеры без админов, админы без прав, админы без юзеров, несколько админов на юзера, несколько прав у админа

 

[fixxxer]

Вообще, да, это вариация на тему EAV. Тут либо подзапрос (или CTE), либо GROUP BY. Какие еще варианты-то (кроме денормализации)?

 

[WMix]

INNER JOIN !== JOIN

@Yaponchick, озадачил ) пойду читать ман (первая ссылка https://stackoverflow.com/questions/565620/difference-between-join-and-inner-join)
@grigori, не eav это уже круто, оно похоже конечно, но всего одна роль интересна, есть работники (наши) они закладывают новых клиентов (тут только админа нужно создать, он сам пусть разбирается что к чему)
@fixxxer, дай ссылочку на CTE плиз