Меню
Что нового?

Вложенные запросы

scorpion-ds

scorpion-ds

Новичок
Создаю универсальный модуль для каталога, с настраиваемыми полями. поля настраиваются в целом для всего каталога и не категории/вида товара.

Использую такие таблицы:

таблица позиций, заголовок внесен в нее:
CREATE TABLE `catalogue_position` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=cp1251;

набор полей
CREATE TABLE `catalogue_fields` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`type` enum('text','textarea','chexbox','list') DEFAULT NULL,
`listName` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=cp1251;

устанавливает сортировку поле для каталога (в общем-то ее можно будет выкинуть)
CREATE TABLE `catalogue_pf` (
`fieldID` int(11) DEFAULT NULL,
`sortInd` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

здесь будут хранится сами данные
CREATE TABLE `catalogue_data` (
`infID` int(11) unsigned NOT NULL,
`fieldID` int(11) DEFAULT NULL,
`infData` text
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;


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

SELECT p.id AS p_id, p.title AS p_title, (SELECT infData FROM catalogue_data WHERE infID = p_id AND fieldID = 1) AS field_1, (SELECT infData FROM catalogue_data WHERE infID = p_id AND fieldID = 2) AS field_2, (SELECT infData FROM catalogue_data WHERE infID = p_id AND fieldID = 4) AS pos_status FROM catalogue_position AS p

Нормально ли будет работать такой сложны запрос и не будет ли вызвать тормозов?

На этой базе я только проектирую работу, еще не продумывал индексных полей, так что сильно помидорами не закидывать.
 
scorpion-ds

scorpion-ds

Новичок
Да на первый взгляд нормально, пока данных мало, да и вообще интересует на сколько такой подход правильный.
К примеру есть вариант делать "альтэртэйбл" когда нужно создать новые поля.
 
c0dex

c0dex

web.dev 2002-...
Команда форума
Партнер клуба
scorpion-ds
Не надо альтер тейблов. Индексы расставь и все.
 
zerkms

zerkms

TDD infected
Команда форума
EAV никогда не был и не будет удобным и быстрым.

Никаких нормальных универсальных каталогов не существует.

Все "универсальные" каталоги, которые вы видите в интернете - ебей, market.yandex, амазон, итд итп в итоге реализованы как N таблиц (по числу типов) со специфическими атрибутами (знания косвенные, сделанные на основе наблюдений. Но я очень удивлюсь, если я кардинально ошибаюсь).
 
Активист

Активист

Активист
Команда форума
А бы сказал даже не по числу типов, а на каждую конечную в дереве группу по таблице.
 
fixxxer

fixxxer

К.О.
Партнер клуба
Я допускаю, что некоторые такие каталоги реализованы на документно-ориентированных СУБД. Что, впрочем, ничего не меняет в контексте сабжа )
 
prolis

prolis

Новичок
scorpion-ds написал(а):
Создаю универсальный модуль для каталога:
fieldID = 1,fieldID = 2, fieldID = 4
Нажмите для раскрытия...
"универсальный" модуль должен поддерживать N свойств товара, а не 3 жестко фиксированных. Далее необходимо будет устанавливать фильтр по значениям свойств, но так как поле в БД текстовое, и тип не указан, то реализация будет ого-го как неподъемная
 
scorpion-ds

scorpion-ds

Новичок
Активист написал(а):
А бы сказал даже не по числу типов, а на каждую конечную в дереве группу по таблице.
Нажмите для раскрытия...
То есть я вижу это так, есть какая-то общая таблица где хранятся ID и другие общие поля товаров/позиций, другая/другие таблицы связаны со своими категориями и набором полей, которые "приклеиваются" к основной таблице?
 
scorpion-ds

scorpion-ds

Новичок
prolis написал(а):
"универсальный" модуль должен поддерживать N свойств товара, а не 3 жестко фиксированных. Далее необходимо будет устанавливать фильтр по значениям свойств, но так как поле в БД текстовое, и тип не указан, то реализация будет ого-го как неподъемная
Нажмите для раскрытия...
Как я выше сказал, что пример реализации, N свойств товара предусматривается, но запрос для них по моей идей должен формироваться на уровне PHP, потому уже передаваться в MySQL.
С текстовым полем действительно не хорошо выходит, по этому есть поле "`type` enum('text','textarea','chexbox','list')", которое будет хранить тип данных (текущий список типов еще изменится), точнее то как их следует обрабатывать.
 
zerkms

zerkms

TDD infected
Команда форума
scorpion-ds написал(а):
То есть я вижу это так, есть какая-то общая таблица где хранятся ID и другие общие поля товаров/позиций, другая/другие таблицы связаны со своими категориями и набором полей, которые "приклеиваются" к основной таблице?
Нажмите для раскрытия...
так точно.
 
scorpion-ds

scorpion-ds

Новичок
Сейчас рассматриваю такой вариант:

таблица позиций:
CREATE TABLE `catalogue_position` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=cp1251;

поля:
CREATE TABLE `catalogue_fields` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`typeID` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
`listName` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `typeID` (`typeID`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=cp1251;

таблица типов, на каждый тип есть еще по таблице:
CREATE TABLE `catalogue_data_types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`typeName` varchar(20) DEFAULT NULL,
`title` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=cp1251;

CREATE TABLE `catalogue_data_int` (
`infID` int(11) unsigned NOT NULL,
`fieldID` int(11) DEFAULT NULL,
`infData` int(11) DEFAULT NULL,
KEY `infID` (`infID`),
KEY `fieldID` (`fieldID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

CREATE TABLE `catalogue_data_text` (
`infID` int(11) unsigned NOT NULL,
`fieldID` int(11) DEFAULT NULL,
`infData` text,
KEY `infID` (`infID`),
KEY `fieldID` (`fieldID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

Создано 15 полей для позиций (ни каких категорий со своими полями нет, все единное), далее с помощью тестового скрипта заполняю все случайными данными, всего 250 позиций.

До индексации полей, на запрос уходило 48 секунд, теперь 150 мс на 20 строк и 500 мс на все 250 строк, в общем-то тоже не мало, учитывая что это БД сайта.
Сейчас вот думаю останавливаться на таком варианте, или искать другие пути.
 
zerkms

zerkms

TDD infected
Команда форума
500мс это просто дохера для 250строк. Это ооооооооочень дохера.
 
scorpion-ds

scorpion-ds

Новичок
да, забыл про сам запрос:

SELECT p.id AS id
, data_text_1.infData AS data_name,
data_text_2.infData AS data_desc_min,
data_text_3.infData AS data_desc_big,
data_int_4.infData AS data_status,
data_text_5.infData AS data_configs,
data_text_6.infData AS data_inform,
data_int_7.infData AS data_price,
data_text_8.infData AS data_config_1,
data_text_9.infData AS data_config_2,
data_text_10.infData AS data_config_3,
data_text_11.infData AS data_config_4,
data_text_12.infData AS data_config_5,
data_text_13.infData AS data_config_6,
data_text_14.infData AS data_config_7,
data_text_15.infData AS data_config_8
FROM catalogue_position AS p LEFT JOIN catalogue_data_text AS data_text_1 ON data_text_1.infID = p.id AND data_text_1.fieldID = 1
LEFT JOIN catalogue_data_text AS data_text_2 ON data_text_2.infID = p.id AND data_text_2.fieldID = 2
LEFT JOIN catalogue_data_text AS data_text_3 ON data_text_3.infID = p.id AND data_text_3.fieldID = 3
LEFT JOIN catalogue_data_int AS data_int_4 ON data_int_4.infID = p.id AND data_int_4.fieldID = 4
LEFT JOIN catalogue_data_text AS data_text_5 ON data_text_5.infID = p.id AND data_text_5.fieldID = 5
LEFT JOIN catalogue_data_text AS data_text_6 ON data_text_6.infID = p.id AND data_text_6.fieldID = 6
LEFT JOIN catalogue_data_int AS data_int_7 ON data_int_7.infID = p.id AND data_int_7.fieldID = 7
LEFT JOIN catalogue_data_text AS data_text_8 ON data_text_8.infID = p.id AND data_text_8.fieldID = 8
LEFT JOIN catalogue_data_text AS data_text_9 ON data_text_9.infID = p.id AND data_text_9.fieldID = 9
LEFT JOIN catalogue_data_text AS data_text_10 ON data_text_10.infID = p.id AND data_text_10.fieldID = 10
LEFT JOIN catalogue_data_text AS data_text_11 ON data_text_11.infID = p.id AND data_text_11.fieldID = 11
LEFT JOIN catalogue_data_text AS data_text_12 ON data_text_12.infID = p.id AND data_text_12.fieldID = 12
LEFT JOIN catalogue_data_text AS data_text_13 ON data_text_13.infID = p.id AND data_text_13.fieldID = 13
LEFT JOIN catalogue_data_text AS data_text_14 ON data_text_14.infID = p.id AND data_text_14.fieldID = 14
LEFT JOIN catalogue_data_text AS data_text_15 ON data_text_15.infID = p.id AND data_text_15.fieldID = 15
;
 
Активист

Активист

Активист
Команда форума
Я бы посоветовал вам сделать несколько сталбцов (N) , назваить из param1, param2, param3, paramK... , paramN-1, paramN, где N максимальное количество параметров, завести еще одну таблицу, где будет название, тип параметра для каждого k, и дергать двумя запросами,
первым - название/типы, вторым значения.
 
scorpion-ds

scorpion-ds

Новичок
Активист написал(а):
Я бы посоветовал вам сделать несколько сталбцов (N) , назваить из param1, param2, param3, paramK... , paramN-1, paramN, где N максимальное количество параметров, завести еще одну таблицу, где будет название, тип параметра для каждого k, и дергать двумя запросами,
первым - название/типы, вторым значения.
Нажмите для раскрытия...
Так у нас был разработан другой проект, транспортных средств, вариант рабочий, но тут нужен "альтэртэйбл", сейчас хотелось же уйти от такой структуры, но пока на получается ...
 
Активист

Активист

Активист
Команда форума
alter table иногда (при увеличении N) вполне ХОРОШЕЕ решение, нежели ваш запрос выше, зачем мудрить?
 
scorpion-ds

scorpion-ds

Новичок
Избавился от таблиц для каждого типа данных, теперь имеем:
CREATE TABLE `catalogue_data` (
`infID` int(11) unsigned NOT NULL,
`fieldID` int(11) DEFAULT NULL,
`intData` int(11) DEFAULT NULL,
`textData` text,
`datetimeData` datetime DEFAULT NULL,
KEY `infID` (`infID`),
KEY `fieldID` (`fieldID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AVG_ROW_LENGTH=73;

новый тип новое поле.

Далее использую такой запрос:
SELECT p.id AS id
, MAX(CASE WHEN fieldData.fieldID = 4 THEN fieldData.intData END) AS status,
MAX(CASE WHEN fieldData.fieldID = 7 THEN fieldData.intData END) AS price,
MAX(CASE WHEN fieldData.fieldID = 1 THEN fieldData.textData END) AS name,
MAX(CASE WHEN fieldData.fieldID = 2 THEN fieldData.textData END) AS desc_min,
MAX(CASE WHEN fieldData.fieldID = 3 THEN fieldData.textData END) AS desc_big,
MAX(CASE WHEN fieldData.fieldID = 5 THEN fieldData.textData END) AS configs,
MAX(CASE WHEN fieldData.fieldID = 6 THEN fieldData.textData END) AS inform,
MAX(CASE WHEN fieldData.fieldID = 8 THEN fieldData.textData END) AS config_1,
MAX(CASE WHEN fieldData.fieldID = 9 THEN fieldData.textData END) AS config_2,
MAX(CASE WHEN fieldData.fieldID = 10 THEN fieldData.textData END) AS config_3,
MAX(CASE WHEN fieldData.fieldID = 11 THEN fieldData.textData END) AS config_4,
MAX(CASE WHEN fieldData.fieldID = 12 THEN fieldData.textData END) AS config_5,
MAX(CASE WHEN fieldData.fieldID = 13 THEN fieldData.textData END) AS config_6,
MAX(CASE WHEN fieldData.fieldID = 14 THEN fieldData.textData END) AS config_7,
MAX(CASE WHEN fieldData.fieldID = 15 THEN fieldData.textData END) AS config_8,
MAX(CASE WHEN fieldData.fieldID = 16 THEN fieldData.datetimeData END) AS added
FROM catalogue_data AS fieldData RIGHT JOIN catalogue_position AS p ON (fieldData.infID = p.id) GROUP BY p.id

В итоге 250 записей за 78 мс, это уже значительно лучше.
 
Войдите или зарегистрируйтесь для ответа.
Сверху