Xpoint
   [напомнить пароль]

Выбор из трех таблиц, нужно хитрое левое объединение

Метки: [без меток]
[удл]
2005-10-07 03:36:07 [обр] Закиров Руслан(12/343)[досье]

БД: MySQL и Pg без подзапросов.
Запрос:

SELECT o.* FROM
  objects o
    LEFT JOIN group_members gm ON( o.group = gm.group )
    LEFT JOIN principals p ON( p.id = rm.member AND p.type = 'user')
WHERE
     o.xxx LIKE 'yyy'
ORDER BY p.Name ASC;

Описание запроса:
Есть объекты(objects), которые мы выбираем по признаку или все(не так важно, считаем что условия выборки не связаны с другими таблицами). К объекту всегда привязанна группа(objects.group). Состав групп описывается в group_members, в группу может входить от 0 до N действующих лиц(principals). У действующих лиц есть два поля: тип(type) и имя(name).
Нужно выбрать объекты и отсортировать их имени действующего лица с типом 'user'.

Четыре ситуации:

  • в состав группы никто не входит
  • в состав группы входят только пользователи(principals.type = 'user')
  • пользователи не входят в состав группы, но группа не пустая
  • в состав группы входят не только пользователи, но и другие действующие лица

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

Первая и третья ситуации решена LEFT JOIN'ом, а порядок становится неважным, так как пользователей нет.
Во второй ситуации все хорошо, но вот в последнем случае все плохо. Допустим есть объект 1 и объект 2, у каждого в группе по пользователю, а у объекта 2 в группе есть еще не пользователь, а другое действующее лицо. При сортировке объект 2 окажется в выборке раньше, так как для него есть запись, где p.name IS NULL.

спустя 10 часов [обр] Закиров Руслан(12/343)[досье]

Поигрался вчера еще с задачкой и получилось, что в MySQL решается вопрос с помощью DISTINCT, а вот Pg не любит, когда выражение сортировки не входит в результаты при включеном DISTINCT.

И я еще забыл отметить зачем нужен хитрый LEFT JOIN. Обратите внимание на второй LEFT JOIN таблицы principals в таблицу group_members, у меня очень большое желание заменить его на INNER JOIN(ну или переместить условие объединения в WHERE, что одно и тоже), но такое решение портит ситуацию, когда в группу не входят пользователи - объект исчезает из результатов.

спустя 2 дня 19 часов [обр] Денис Гетман(5/228)[досье]
 Мне кажется, что здесь ошибка какая-то, так как не должна в выборку попадать запись с пустым именем. Попробуйте в ORDER BY выражение вставить, типа CASE WHEN p.name ISNULL THEN "zzz" ELSE p.name END
спустя 8 часов [обр] Закиров Руслан(12/343)[досье]

Денис Гетман[досье] Ошибки нет. Записи в таблице Principals - это различные действующие лица(ДЛ), такие как пользователи, группы или как вариант компьютеры, определяемы через IP адрес. То есть все, что может как-то воздействовать на объекты системы и изменять их. Поле type определяет тип, и в данном конкретном запросе меня интересуют только пользователи(type = 'user'). Любое ДЛ может быть членом группы и получается, что если в группе есть не только пользователи, но и ДЛ другого типа, то результаты запроса будут следующие:

o.idgm.groupgm.memberp.typep.name
112NULLNULL
113userxxx

Опишу первую запись: объекту #1 соответствует группа #1(связь 1:1 всегда) в которую входят два ДЛ с идентификаторами 2 и 3, ДЛ #1 некторого типа, но не пользователь, а ДЛ #3 - пользователь.

Вот в результатах первая запись мне не нужна совсем, потому что NULLы сортируются либо превыми, либо последними всегда, то есть их не должно быть, если для объекта есть записи, где нет NULLов. Проблема в том, что я не могу присоединять таблицу principals с помощью INNER JOIN, так как объекты не должны пропадать из выборки даже если в их группу не входят пользователи или вообще никто не входит.

Перелопатив информацию по всем типам JOIN'ов, я понял, что не могу этого сделать не используюя VIEW или подзапросов, наверное идеальным решением будут создать VIEW user_group_members из следующего запроса:

SELECT gm.group, gm.member, p.name, p.foo, p.bar
FROM group_members gm, principals p
WHERE gm.member = p.id AND p.type = 'user';

И использовать следующий запрос для сортировки:

SELECT o.* FROM
  objects o
    LEFT JOIN user_group_members ugm ON( o.group = gm.group )
WHERE
     o.xxx LIKE 'yyy'
ORDER BY ugm.name ASC;

тогда точно будет получаться, что если в группе объекта нету пользователя, то и запись для него одна, где ugm.* = NULL.

На данный момент добавлен ряд заплаток, которые работают при текущих услових, но не работают в общем случае, так что в обозримом будущем придется встраивать поддержку подзапросов и переходить полностью на MySQL 4.1, хотя для MySQL аншлось решение с помощь DISTINCT(нас интересовуют данные только из таблицы objects), MySQL при DISTINCT запросе и сортировке по полю не входящему в результат выкидывает записи с NULLами, если для одного объекта есть записи без нулов, но возможно, что это побочный эффект физической последовательности данных и результат изменится, если вставить данные в другом порядке.

Тему можно переименовать и в архив или удалить.

спустя 1 год 10 месяцев [обр] Закиров Руслан(12/343)[досье]

На днях подробнее разобрался с новой возможностью mysql 5.0 и сразу вспомнил про эту тему.

Сразу приведу запросы и результаты.

Тестовый набор:

mysql> SELECT *
    -> FROM o
    ->     LEFT JOIN gm ON o.id = gm.object
    ->     LEFT JOIN p  ON p.id = gm.principal;
+------+-------------------+--------+-----------+------+-------+-----------------+
| id   | name              | object | principal | id   | type  | name            |
+------+-------------------+--------+-----------+------+-------+-----------------+
|    1 | some object       |      1 |         1 |    1 | user  | some user       |
|    2 | another object    |      2 |         2 |    2 | group | a group         |
|    2 | another object    |      2 |         3 |    3 | user  | another user    |
|    4 | with group only   |      4 |         4 |    4 | group | hey-ho, a group |
|    3 | without relations |   NULL |      NULL | NULL | NULL  | NULL            |
+------+-------------------+--------+-----------+------+-------+-----------------+
5 rows in set (0.01 sec)

Запрос из первого сообщения в теме:

mysql> SELECT *
    -> FROM o
    ->     LEFT JOIN gm ON o.id = gm.object
    ->     LEFT JOIN p  ON p.id = gm.principal AND p.type = 'user'
    -> ORDER BY p.name ASC;
+------+-------------------+--------+-----------+------+------+--------------+
| id   | name              | object | principal | id   | type | name         |
+------+-------------------+--------+-----------+------+------+--------------+
|    4 | with group only   |      4 |         4 | NULL | NULL | NULL         |
|    2 | another object    |      2 |         2 | NULL | NULL | NULL         |
|    3 | without relations |   NULL |      NULL | NULL | NULL | NULL         |
|    2 | another object    |      2 |         3 |    3 | user | another user |
|    1 | some object       |      1 |         1 |    1 | user | some user    |
+------+-------------------+--------+-----------+------+------+--------------+

Новый запрос:

mysql> SELECT *
    -> FROM o
    ->     LEFT JOIN ( gm JOIN p ON p.id = gm.principal AND p.type = 'user' )
    ->     ON o.id = gm.object
    -> ORDER BY p.name ASC;
+------+-------------------+--------+-----------+------+------+--------------+
| id   | name              | object | principal | id   | type | name         |
+------+-------------------+--------+-----------+------+------+--------------+
|    4 | with group only   |   NULL |      NULL | NULL | NULL | NULL         |
|    3 | without relations |   NULL |      NULL | NULL | NULL | NULL         |
|    2 | another object    |      2 |         3 |    3 | user | another user |
|    1 | some object       |      1 |         1 |    1 | user | some user    |
+------+-------------------+--------+-----------+------+------+--------------+
Powered by POEM™ Engine Copyright © 2002-2005