Как реализовать сложный (динамический) LEFT JOIN?

Question

[z0091]

Всем привет.

Есть 2 таблицы

CREATE TABLE `catalogmanager_item` ( 
	`id` INT( 10 ) UNSIGNED AUTO_INCREMENT NOT NULL, 
	`type` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 
	`pagetitle` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 
	`parentid` INT( 10 ) NOT NULL DEFAULT '0', 
	`menuindex` INT( 20 ) NOT NULL, 
	`active` TINYINT( 1 ) NOT NULL DEFAULT '1', 
	`delete_object` TINYINT( 1 ) NOT NULL DEFAULT '0', 
	`class` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
	 PRIMARY KEY ( `id` ),
	 INDEX `parentid` USING BTREE ON `modx_catalogmanager_item`( `parentid` )
 )
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ENGINE = INNODB
AUTO_INCREMENT = 15939;

CREATE TABLE `catalogmanager_xtype` ( 
	`id` INT( 11 ) UNSIGNED AUTO_INCREMENT NOT NULL, 
	`object_id` INT( 11 ) UNSIGNED NOT NULL, 
	`type` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, 
	`value` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL, 
	`key` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL, 
	`xtype` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
	 PRIMARY KEY ( `id` ),
	 INDEX `object_id` USING BTREE ON `modx_catalogmanager_xtype`( `object_id` )
 )
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ENGINE = INNODB
AUTO_INCREMENT = 61373;

Суть проблемы в объединении таблиц при SELECT. По сути catalogmanager_xtype предстовляет из себя место хранения доп. свойств объекта catalogmanager_item. Необходимо одним запросом доставать объект целиком.

Если сделать просто LEFT JOIN то получится

mysql> SELECT Item.*, Xtype.key, Xtype.value
    -> FROM catalogmanager_item AS Item
    -> LEFT JOIN catalogmanager_xtype AS Xtype
    -> ON Item.id = Xtype.object_id
    -> WHERE Item.id = 85;
+----+----------+-----------------------------------------+----------+-----------+--------+---------------+----------+-------------+------------------------------------------------------------------------------------------------------------+
| id | type     | pagetitle                               | parentid | menuindex | active | delete_object | class    | key         | value                                                                                                      |
+----+----------+-----------------------------------------+----------+-----------+--------+---------------+----------+-------------+------------------------------------------------------------------------------------------------------------+
| 85 | category | Операционные системы                    |        0 |         0 |      1 |             0 | category | longtitle   | Операционные системы                                                                                       |
| 85 | category | Операционные системы                    |        0 |         0 |      1 |             0 | category | description | Различные операционные системы                                                                             |
| 85 | category | Операционные системы                    |        0 |         0 |      1 |             0 | category | alias       | operacionnye_sistemy                                                                                       |
| 85 | category | Операционные системы                    |        0 |         0 |      1 |             0 | category | keywords    | системы ос оси операционные                                                                                |
| 85 | category | Операционные системы                    |        0 |         0 |      1 |             0 | category | content     | <p>Выберите лицензию на подходящую операционную систему</p>                                                |
| 85 | category | Операционные системы                    |        0 |         0 |      1 |             0 | category | catImg      | i/icons/icon_os.png                                                                                        |
| 85 | category | Операционные системы                    |        0 |         0 |      1 |             0 | category | catImgHover | i/icons/icon_os_active.png                                                                                 |
+----+----------+-----------------------------------------+----------+-----------+--------+---------------+----------+-------------+------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

Мне же необходимо получить вот такую структуру( сейчас запрос генерируется на уровне php )

mysql> SELECT
    -> Item.*,
    -> longtitle.value AS longtitle,
    -> description.value AS description,
    -> alias.value AS alias
    -> FROM `catalogmanager_item` AS `Item`
    -> LEFT JOIN `catalogmanager_xtype` AS `longtitle` ON ( Item.id = longtitle.object_id AND longtitle.key = "longtitle" )
    -> LEFT JOIN `catalogmanager_xtype` AS `description` ON ( Item.id = description.object_id AND description.key = "description" )
    -> LEFT JOIN `catalogmanager_xtype` AS `alias` ON ( Item.id = alias.object_id AND alias.key = "alias" )
    -> WHERE Item.id = 85;
+----+----------+-----------------------------------------+----------+-----------+--------+---------------+----------+-----------------------------------------+------------------------------------------------------------+----------------------+
| id | type     | pagetitle                               | parentid | menuindex | active | delete_object | class    | longtitle                               | description                                                | alias                |
+----+----------+-----------------------------------------+----------+-----------+--------+---------------+----------+-----------------------------------------+------------------------------------------------------------+----------------------+
| 85 | category | Операционные системы                    |        0 |         0 |      1 |             0 | category | Операционные системы                    | Различные операционные системы                             | operacionnye_sistemy |
+----+----------+-----------------------------------------+----------+-----------+--------+---------------+----------+-----------------------------------------+------------------------------------------------------------+----------------------+
1 row in set (0.00 sec)

Т.е название столбца берется из значения Xtype.key, а его значение из Xtype.value. Возможно есть способы сделать это все более динамически.

З.Ы. Кол-во и название свойств объекта в catalogmanager_xtype может отличаться в зависимости от типа объекта

Спасибо

Comments

[Fadmin]

У вас структура БД заточена под динамический запрос, который собирается в php. Проблема то в чем? Собирайте его там и выполняйте, или вам нужно обязательно одним запросом?

Answer 1

[bohdan-shulha]

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

stackoverflow.com/questions/649802/how-to-pivot-a-...

Comments

[bohdan-shulha]

Второй - если нужно просто скрыть сложность запроса - создать для этого view, в который его и завернуть.

[z0091]

По сути для меня тут сложность вот в чем. Необходимо сделать перебора по количеству элементов в таблице catalogmanager_xtype. После этого приjoinить каждый элемент списка отдельно.