Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MySQL > Помогите сделать выборку из каталога с отдельной т

Автор: Reverent 1.2.2016, 02:38

Помогите сделать выборку из каталога с отдельной таблицей свойств. Добрый день, уважаемые форумчани. Уже несколько дней ломаю голову, как можно реализовать следующую задачу, которая стоит в проекте. Представьте, что у нас есть каталог наподобие такого: id_catalog name А так же у нас есть таблица, которая хранит свойства каждого товара и его значение. id_catalogParam id_catalog label value Как видим мы имеем связь один ко многим. Например у нас есть товар пылесос, а у него может быть очень много параметров. Все вроед выглядет идеально, но проблема наступает на этапе выборки, когда мне нужно создать фильтр. Например я хочу найти все пылесосы, с определенной мощностью и ценой, я пишу. Код SELECT  catalog.*, catalogParam.*  FROM  catalog  LEFT JOIN catalogParam ON catalog.id_catalog = catalogParam.id_catalog  WHERE      (     label = 'Мощность'      AND     value = '300'     ) OR      (     label = 'Цена'      AND     value = '7000'     ); Но таким образом он находит товары либо с мощностью 300, либо с ценой 7000. А мне нужно полное соответствие, тогда я заменяю OR на AND. Но поиск ничего не дает. И это вполне понятно, нет такой записи где бы value равнялось и тому и другому. Так как решить эту проблему, как провести эту выборку, чтобы она дала товары полного соотевствия.

Автор: ksnk 1.2.2016, 08:17

Есть такая функция - EXISTS Код SELECT  c.* FROM  catalog as c WHERE      EXISTS( select .id_catalog from catalogParam                   where id_catalog =c.id_catalog and  label = 'Мощность' AND value = '300'      )  AND     EXISTS( select .id_catalog from catalogParam                   where id_catalog =c.id_catalog and  label = 'Цена' AND value >= 300 and  value <= 1000     )  Тонкость в том, что запрос не сможет выдать все свойства товара за раз, только ID товара. Придется потом отдельным запросом их выковыривать.

Автор: igorold 1.2.2016, 09:19

Код SELECT catalog.*, catalogParam.*,count(catalogParam.id_catalog) FROM catalog LEFT JOIN catalog.id_catalog = catalogParam.id_catalog WHERE (label = 'Мощность' AND value = '300') OR (label = 'Цена' AND value = '7000') group by  catalog.id_catalog having(count(catalogParam.id_catalog) = 2); Этот ответ добавлен с нового Винграда - http://ru.vingrad.com/Pomogite-sdelat-vyborku-iz-kataloga-s-otdelnoy-t-id56ae9b18ae20156f088b4567#findElement_E7045_56aef902ae20156b57f1e3df_0

Автор: Akina 1.2.2016, 11:33

Классический подход - это обработка таблицы параметров в подзапросе с получением только тех объектов, для которых найден необходимый комплекс характеристик. Т.е. подзапрос выглядит приблизительно так: Код SELECT id_catalog FROM catalogParam  WHERE      (label = 'Мощность' AND value = '300') OR      (label = 'Цена' AND value = '7000') GROUP BY id_catalog HAVING COUNT(id_catalogParam) = 2 Все указанные фильтры включаются в секцию WHERE через OR, причём тут допустимы любые типы условий, например: Код label = 'Мощность' AND value = '300' -- строго 300 label = 'Мощность' AND value >= '300' -- не менее 300 label = 'Цена' AND value+0 BETWEEN 3000 AND 5000 -- от 3000 до 5000 Количество соответствующих фильтров указывается в секции HAVING, причём там может задаваться и количество, не равное количеству отдельных фильтров - например, если всего указано 5 фильтров, то секция может быть такой: Код HAVING COUNT(id_catalogParam) = 5 -- строго все 5 фильтров HAVING COUNT(id_catalogParam) >= 3 -- не менее 3 из этих 5 Полученный подзапрос даёт все id_catalog, которые соответствуют набору фильтров и требуемому количеству соответствий, и это значение используется для отбора записей основной таблицы.

Автор: ksnk 1.2.2016, 11:49

Akina, "Классика" - это бОльшее соответствие стандарту SQL? В смысле EXISTS - это такая mysql фича, которая другими системами может и не поддерживаться. А вот в mysql реальности есть ли заметные преимущества "классики" перед ESISTS подходом? Интерес шкурный  Переписывать мне систему или и так пойдет?

Автор: Akina 1.2.2016, 12:04

Цитата(ksnk @  1.2.2016,  12:49 ) в mysql реальности есть ли заметные преимущества "классики" перед EXISTS подходом? Если каталог содержит миллион товаров, из которых набору из десятка фильтров соответствует только десяток записей, EXISTS со своими коррелированными подзапросами отправится курить за угол. Ибо описанный подход изрядно "худеет" самую пухлую из участвующих в этом действе таблиц. Однако если набору фильтров соответствует половина товаров, то EXISTS, несомненно, выиграет. Кроме того, лично я считаю, что для динамического построения запросов EXISTS-шаблон более сложен. Если кто считает иначе - спорить не буду, это очень субъективно. А ещё я считаю, что запрос с EXISTS хуже читаем с точки зрения понимания логики текста запроса. Ну и EXISTS не очень-то предназначен для построения запроса, когда требуется соответствие не строго всем фильтрам, а не менее указанного количества (как в примере "не менее 3 из этих 5"). Цитата(ksnk @  1.2.2016,  12:49 ) В смысле EXISTS - это такая mysql фича, которая другими системами может и не поддерживаться. По-моему, её поддерживают все и вся...

Автор: tzirechnoy 1.2.2016, 21:05

EXISTS есть как миниму в SQL92.

Автор: Zloxa 2.2.2016, 10:25

Цитата(Akina @  1.2.2016,  13:04 )  EXISTS со своими коррелированными подзапросами  Хотел было поспорить, но походу это платформспецифик маси, покуда он не умеет выполнять exists, траснформируя его в http://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html#semi-joins. Увы,  Цитата It must be an IN (or =ANY) subquery that appears at the top level of the WHERE or ON clause, possibly as a term in an AND expression. Далее, сорри - . Потому как не про MySql, а про единственную боль-мень нормальную СУБД (oracle) , которая умеет exists выполнять семиджойном, а не корреляхой. А так вобще экзистс вполне себе может выполняться и джойном. При высокой селективности и при индексированной паре (тип, значение), отбираем по индексу самый селективный предикат, потом нестедлупим второй.  Код SQL> create table entity(id primary key) as select level from dual connect by level <= 1e6; Table created SQL> create table property(entity_id, type, value, primary key (entity_id, type)) as select trunc(level / 4) + 1, mod(level-1, 4) + 1 , level from dual connect by level <= 4e6; Table created SQL> create index property_i1 on property(type, value); SQL> select * from property order by 1 fetch first 10 rows only;  ENTITY_ID       TYPE      VALUE ---------- ---------- ----------          1          1          1          1          2          2          1          3          3          2          4          4          2          1          5          2          2          6          2          3          7          3          4          8          3          1          9          3          2         10 10 rows selected Index created Код SQL> set timing on SQL> select   2      e.*   3  from   4      entity e   5  where   6      exists (select null from property p where p.type = 1 and p.value =1 and p.entity_id = e.id) and   7      exists (select null from property p where p.type = 2 and p.value =2 and p.entity_id = e.id);         ID ----------          1 Executed in 0.015 seconds План Код PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 938718327 -------------------------------------------------------------------------------- | Id  | Operation                              | Name         | Rows  | Bytes | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                       |              |     1 |    33 | |   1 |  NESTED LOOPS SEMI                     |              |     1 |    33 | |   2 |   NESTED LOOPS                         |              |     1 |    19 | |   3 |    SORT UNIQUE                         |              |     1 |    14 | |   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| PROPERTY     |     1 |    14 | |*  5 |      INDEX RANGE SCAN                  | PROPERTY_I1  |     1 |       | |*  6 |    INDEX UNIQUE SCAN                   | SYS_C0053015 |     1 |     5 | |*  7 |   TABLE ACCESS BY INDEX ROWID          | PROPERTY     |     1 |    14 | |*  8 |    INDEX UNIQUE SCAN                   | SYS_C0053016 |     1 |       | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    5 - access("P"."TYPE"=2 AND "P"."VALUE"=2)    6 - access("P"."ENTITY_ID"="E"."ID")    7 - filter("P"."VALUE"=1)    8 - access("P"."ENTITY_ID"="E"."ID" AND "P"."TYPE"=1) 23 rows selected Здесь он сначала по индексу находит property по  p.type = 2 and p.value = 2, затем по индексу подтягивает его entity, затем по индексу смотрит для него property по type = 1 и фильтрует по value = 1 Хавингом получается трохан подольше Код SQL> select   2      *   3  from   4      entity   5  where   6      id in   7      (   8         select entity_id from property p where p.type = 1 and p.value = 1 or p.type = 2 and p.value = 2  group by entity_id having count(*) = 2   9      )  10  ;         ID ----------          1 Executed in 0.063 seconds При низкой селективности, казалось бы, должно быть наоборот. Ведь джойн потребует двух фулсканов там где having потребует одного. Но практика это не подтверждает Код SQL> select sum(id)   2  from   3  (   4      select   5          e.*   6      from   7          entity e   8      where   9          exists (select null from property p where p.type = 1 and p.value > 1 and p.entity_id = e.id) and  10          exists (select null from property p where p.type = 2 and p.value > 2 and p.entity_id = e.id)  11  );      SUM(ID) ---------- 5000004999 Executed in 1.326 seconds SQL> select sum(id)   2  from   3  (   4      select   5          *   6      from   7          entity   8      where   9          id in  10          (  11             select entity_id from property p where p.type = 1 and p.value > 1 or p.type = 2 and p.value > 2  group by entity_id having count(*) = 2  12          )  13  )  14  ;    SUM(ID) ---------- 5000004999 Executed in 3.198 seconds

Автор: Angel_666 2.2.2016, 12:57

Не знаю как вариант. 1. выбрать id товара по одному свойству допустим цене (получили 30 id) 2. следующий запрос по другому свойству мощности но с ограничением id уже полученных товаров. 3. ну и так далее... Этот ответ добавлен с нового Винграда - http://ru.vingrad.com/Pomogite-sdelat-vyborku-iz-kataloga-s-otdelnoy-t-id56ae9b18ae20156f088b4567#findElement_E7045_56b07d72ae2015976af19e1e_0

Автор: Akina 2.2.2016, 15:46

Цитата(Angel666 @  2.2.2016,  13:57 ) как вариант. Гм... это вариант, навеянный привычкой вытащить все данные на клиента и там их обрабатывать, вместо того, чтобы поручить это SQL-серверу.

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)