Учитывая таблицу с именем item которой есть «основные» записи, такие как:
item_id item_type item_name item_description 1 clothing T-Shirt Get your t-shirt here 2 clothing Polo Shirt Another kind of short-sleeve shirt 3 computer Macbook 2016 Macbook Pro with retina
И еще одна таблица под названием custom_fields , которая, как я полагаю, будет называться структурой EAV (Entity-Attribute-Value):
id item_id attribute value 1 1 size large 2 1 color purple 3 2 size medium 4 2 color green 5 3 memory 16GB 6 3 hard drive 512GB SSD
Каков наилучший способ закодировать запрос, который будет включать все указанные / настроенные атрибуты, которые также поддерживали бы ORDER BY ?
Факты:
1. Структура данных не может измениться.
2. Атрибуты неизвестны (настроены пользователем). 3. Существует технически неограниченное количество «атрибутов» (например, size или color выше), которые могут быть настроены для данного типа предметов (практически, я не могу представить более 50).
4. Какие атрибуты / не включены в запрос, неизвестны (настроены пользователем).
Предполагая, что указанные атрибуты существуют в массиве, например:
$attributes = ['size', 'color'];
Кажется, что некоторый PHP, который делает что-то вроде:
$counter = 1; $from = 'item AS i'; $fields = 'name, description'; $order_by = 'size'; foreach( $attributes AS $attribute ) { $table_alias = "a{$counter}"; $fields.= ",{$table_alias}.value AS {$attribute}"; $from.= " INNER JOIN custom_fields AS {$table_alias} ON i.item_id = {$table_alias}.item_id AND {$table_alias}.attribute = '{$attribute}'"; $counter++; } $query = "SELECT {$fields} FROM {$from} {$where} ORDER BY {$order_by}";
Создал бы запрос, который можно было бы использовать как для включения данных, так и для разрешения ORDER BY. Этот запрос будет выглядеть примерно так:
SELECT name, description, a1.value AS size, a2.value AS color FROM item AS i INNER JOIN custom_fields AS a1 ON i.item_id = a1.item_id AND a1.attribute = 'size' INNER JOIN custom_fields AS a2 ON i.item_id = a2.item_id AND a2.attribute = 'color'
Однако, если есть 20 + столбцов, я обеспокоен тем, что этот запрос кажется неприятным (20 JOINS ) и может иметь проблемы с производительностью.
Есть ли лучший способ структурировать это? Есть ли какой-то метод, например, использование временных таблиц, которые могут быть более полезными или эффективными?
Обновить:
Версия MySQL не может быть гарантирована, чтобы быть чем-то конкретным, поэтому это должно быть совместимо с любой версией, поэтому, пожалуйста, предположите, что версия 5.0 .