Форум пользователей MySQL

nexus · 06.02.2012 15:54:11

Здравствуйте!

Помогите составить правильный запрос для поиска в базе данных.
Ситуация следующая. Имеется таблица товаров:

CREATE TABLE IF NOT EXISTS `products` (

  `product_id` int(11) NOT NULL auto_increment,

  `assortment_id` smallint(6) NOT NULL,

  `name` varchar(255) NOT NULL,

  `price` double NOT NULL,

  PRIMARY KEY  (`product_id`),

  KEY `assortment_id` (`assortment_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

INSERT INTO `site_products` (`product_id`, `assortment_id`,  `name`,  `price`) VALUES

(1, 2, 'Night in the city z',500),

(2, 2, 'Ночной город, м.№2', 1000),

(3, 3, 'Ночной город, м.№3', 800);

И также есть таблица характеристик товаров:

CREATE TABLE IF NOT EXISTS `products_options` (

  `product_id` int(11) NOT NULL,

  `assortment_id` smallint(6) NOT NULL,

  `option_id` int(11) NOT NULL,

  `variant_id` int(11) NOT NULL,

  `option_value` text character set utf8 NOT NULL,

  UNIQUE KEY `product_id` (`product_id`,`option_id`,`variant_id`),

  KEY `option_id` (`option_id`),

  KEY `variant_id` (`variant_id`),

  KEY `assortment_id` (`assortment_id`)

) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

INSERT INTO `site_products_options` (`product_id`, `assortment_id`, `option_id`, `variant_id`, `option_value`) VALUES

(3, 3, 17, 25, 'да'),

(1, 2, 3, 2, 'Для него'),

(1, 2, 4, 4, 'Большой'),

(2, 2, 24, 37, 'Модель №1'),

(1, 2, 24, 36, 'Модель №2'),

(1, 2, 7, 34, 'Белый'),

(2, 2, 23, 0, 'кожа, замша'),

(2, 2, 22, 0, '41см x 32см x 44см x 11,5см'),

(1, 2, 23, 0, 'кожа, замша'),

(1, 2, 22, 0, '41см x 32см x 44см x 11,5см'),

(2, 2, 21, 0, '40 см'),

(2, 2, 5, 6, 'На плечо'),

(2, 2, 4, 22, 'Средний'),

(2, 2, 3, 1, 'Для нее'),

(2, 2, 17, 25, 'да'),

(1, 2, 21, 0, '40 см'),

(1, 2, 5, 6, 'На плечо'),

(1, 2, 3, 1, 'Для нее'),

(1, 2, 20, 28, 'Да'),

(2, 2, 7, 35, 'Серый'),

(1, 2, 19, 27, 'Да'),

(1, 2, 18, 26, 'Подарок'),

(1, 2, 17, 25, 'да'),

(3, 3, 25, 38, 'Модель №3'),

(3, 3, 4, 3, 'Маленький'),

(3, 3, 7, 34, 'Белый')

Один товар может иметь несколько характеристик, например: модель, размер, цвет и т.д.
Задача стоит в том, как организовать фильтр. Т.е. на странице есть фильтр характеристик, и нужно выбрать все товары, для которых совпадают все выбранные характеристики.
Так как данные хранятся не в разных ячейках таблицы, поэтому я не могу сделать стандартную выборку: WHERE option_value="Серый" AND option_value="Модель №3".
Как в такой ситуации правильно составить запрос, для фильтра товаров?!

Отредактированно nexus (06.02.2012 15:56:22)

vasya · 06.02.2012 21:07:52

select `product_id` from `site_products_options` WHERE option_value="Серый" or option_value="Модель №3" group by `product_id` having count(*)=2;

nexus · 07.02.2012 11:43:25

vasya написал:
select `product_id` from `site_products_options` WHERE option_value="Серый" or option_value="Модель №3" group by `product_id` having count(*)=2;

Спасибо. А я уже сделал через JOIN-ны. Т.е.

SELECT DISTINCT p.product_id,p.name,p.price 

FROM products_options o 

JOIN products_options o7 ON o.product_id = o7.product_id 

JOIN products_options o24 ON o.product_id = o24.product_id

JOIN products p ON o.product_id = p.product_id

WHERE o.assortment_id =2 AND (o7.option_id = 7 AND o7.option_value = 'Красный' AND o24.option_id = 24 AND o24.option_value = 'Модель №1')

Тоже работает. Посмотрел EXPLAIN, в вашем случае выбираются все строки из таблицы products_options, а в моем всего 164, так как используется ключ. Эли мой вариант хуже?

Хотя добавил ключ на поле option_value, Ваш запрос показывает:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE o range product_id,option_id,assortment_id,option_value option_value 32 NULL 28 Using where; Using temporary; Using filesort
1 SIMPLE p eq_ref PRIMARY PRIMARY 4 db.o.product_id 1 Using where
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 db.p.cat_id 1

а мой:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE o7 ref product_id,option_id,option_value option_value 32 const 6 Using where; Using temporary
1 SIMPLE o24 ref product_id,option_id,option_value product_id 8 db.o7.product_id,const 8 Using where
1 SIMPLE p eq_ref PRIMARY PRIMARY 4 db.o7.product_id 1 Using where
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 db.p.cat_id 1
1 SIMPLE o ref product_id,assortment_id product_id 4 db.o24.product_id 15 Using where; Distinct

Отредактированно nexus (07.02.2012 11:47:29)

vasya · 07.02.2012 11:54:11

Замените в моем варианте
group by `product_id` having count(*)=2
на
group by `product_id` order by null having count(*)=2
и будет вам счастье

nexus · 07.02.2012 12:05:53

Если добавляю order by, то выдает ошибку. А чем Ваш вариант лучше моего? Хочу разобраться с этим вопросом.

vasya · 07.02.2012 12:53:13

group by `product_id` having count(*)=2 order by null

Ваш вариант делает кучу join (по кол-ву условий), а это плохо.

nexus · 07.02.2012 16:01:40

Скажите, как посчитать общее число строк в выборке? Такой способ не работает:

select count(*) from `products_options` WHERE option_value="Серый" or option_value="Модель №3" group by `product_id` having count(*)=2;

 

simple · 07.02.2012 16:11:57

убрать group by и все что за ним

nexus · 07.02.2012 16:19:15

тогда не верный результат будет. так как стоит условие OR, а мне надо, чтобы выборка шла по все параметрам

AiD3614 · 14.02.2012 09:59:02

помогите как правильно добавить ключь в базу?база имеется, ключ закинул в корень дискп С,стоит винда.я чайник в этом

vasya · 14.02.2012 11:41:54

Ещё бы понять о каком ключе идет речь

AiD3614 · 14.02.2012 14:21:11

Есть биллинговая программа UTM5, вот она коннектится, но потом слетает из-за ключа, нужно ключ от биллинга внести в базу я так понимаю. расширение ключа sql (mysql use utm5 [ c:\] key.sql) поправьте меня

vasya · 14.02.2012 14:53:47

А почитать документацию по установке вашей программы?

В винде: меню пуск - выполнить - cmd
указываете путь к mysql, что-то вроде cd "C:\Program Files\MySQL\MySQL Server 5.0\bin\"

затем
mysql UTM5 < reg_key.sql
это если, файл reg_key.sql находится в той же папке что mysql или указываете путь к этому файлу.

AiD3614 · 17.02.2012 18:30:06

vasya написал:
А почитать документацию по установке вашей программы?

В винде: меню пуск - выполнить - cmd
указываете путь к mysql, что-то вроде cd "C:\Program Files\MySQL\MySQL Server 5.0\bin\"

затем
mysql UTM5 < reg_key.sql
это если, файл reg_key.sql находится в той же папке что mysql или указываете путь к этому файлу.

Спасибо, очень помог, я начинающий так что не бейте сильно))))

nexus · 17.02.2012 18:41:28

а мне кто-нибудь поможет? как же правильно составить запрос для подсчета общего числа строк в выборке, о котором я говорил выше?

select count(*) from `products_options` WHERE option_value="Серый" or option_value="Модель №3" group by `product_id` having count(*)=2;

 

vasya · 19.02.2012 10:43:09

Чем вас не устраивает текущий вариант? Что значит "не работает"?

Возможно вам поможет

select count(*),product_id from `products_options`

WHERE option_value="Серый" or option_value="Модель №3" group by `product_id` with rollup having count(*)=2;

iMaximius · 22.03.2012 13:42:17

Здраствуйте, спасибо за данную тему, вопрос такой возник и я на него нашёл ответ. Но у меня возникла проблема с запросом, когда запрос находит записи удовлетворяющие условиям запроса то он отрабатывает довольно быстро (0.003 - 0.008 сек при таблице в 100 000 записей), а если записей удовлетворяющим условия нет, то отрабатывает чуть ли не (0.05 сек). В чём может быть пролбема и как её можно исправить?

Пример таблицы и запроса

CREATE TABLE IF NOT EXISTS `main_pages_options` (

  `page_id` int(11) NOT NULL,

  `value` varchar(255) NOT NULL,

  `option_id` int(11) NOT NULL,

  PRIMARY KEY (`page_id`,`option_id`),

  KEY `option_id` (`option_id`,`value`)

) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

SELECT page_id

FROM main_pages_options

    WHERE (option_id = 1 AND value = 'Тест 1') 

    OR (option_id = 2 AND value = 'Тест 2') 

    OR (option_id = 3 AND value = 'Тест 3')

GROUP BY page_id 

HAVING COUNT(*) = 3

Спасибо за ответ!

Отредактированно iMaximius (22.03.2012 13:44:43)

Форум пользователей MySQL

#1 06.02.2012 15:54:11

Помогите составить нужный запрос к базе данных

#2 06.02.2012 21:07:52

Re: Помогите составить нужный запрос к базе данных

#3 07.02.2012 11:43:25

Re: Помогите составить нужный запрос к базе данных

vasya написал:

#4 07.02.2012 11:54:11

Re: Помогите составить нужный запрос к базе данных

#5 07.02.2012 12:05:53

Re: Помогите составить нужный запрос к базе данных

#6 07.02.2012 12:53:13

Re: Помогите составить нужный запрос к базе данных

#7 07.02.2012 16:01:40

Re: Помогите составить нужный запрос к базе данных

#8 07.02.2012 16:11:57

Re: Помогите составить нужный запрос к базе данных

#9 07.02.2012 16:19:15

Re: Помогите составить нужный запрос к базе данных

#10 14.02.2012 09:59:02

Re: Помогите составить нужный запрос к базе данных

#11 14.02.2012 11:41:54

Re: Помогите составить нужный запрос к базе данных

#12 14.02.2012 14:21:11

Re: Помогите составить нужный запрос к базе данных

#13 14.02.2012 14:53:47

Re: Помогите составить нужный запрос к базе данных

#14 17.02.2012 18:30:06

Re: Помогите составить нужный запрос к базе данных

vasya написал:

#15 17.02.2012 18:41:28

Re: Помогите составить нужный запрос к базе данных

#16 19.02.2012 10:43:09

Re: Помогите составить нужный запрос к базе данных

#17 22.03.2012 13:42:17

Re: Помогите составить нужный запрос к базе данных

Board footer