How to select data from EAV structure with multiple values

Question

Here is my sql structure

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) unsigned NOT NULL,
  `sku` varchar(32) NOT NULL,
  `name` tinytext NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=409 ;

INSERT INTO `products` (`id`, `sku`, `name`, `created_at`, `updated_at`) VALUES
(1, '111', 'Item 1', '', '0000-00-00 00:00:00'),
(2, '222', 'Item 2', '', '0000-00-00 00:00:00'),
(3, '333', 'Item 3', '', '0000-00-00 00:00:00');

CREATE TABLE IF NOT EXISTS `product_attributes` (
`id` int(10) unsigned NOT NULL,
  `attribute_id` int(10) unsigned NOT NULL,
  `value_id` int(10) unsigned NOT NULL,
  `product_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4820 ;

INSERT INTO `product_attributes` (`id`, `attribute_id`, `value_id`, `product_id`) VALUES
(1, 11, 1, 1),
(2, 12, 2, 1),
(3, 13, 3, 1),
(4, 11, 1, 2),
(5, 12, 5, 2),
(6, 13, 6, 2),
(7, 11, 1, 3),
(8, 12, 8, 3),
(9, 13, 3, 3);

I want to select products with n selected values. For example ((attribute_id=11 AND value_id=1) AND (attribute_id=13 AND value_id=3)). I expect to get product 1 and 3.

Try this query, but no luck

select 
    p.id, 
    p.name, 
    pa.attribute_id, 
    pa.value_id 
FROM products p 
JOIN product_attributes pa 
ON p.id=pa.product_id 
WHERE ((pa.attribute_id=11 AND pa.value_id=1) AND (pa.attribute_id=13 AND pa.value_id=3))

Use a subquery for each value, then join them to get the products that have all of them. — Barmar, Jun 04 '17 at 04:02
@Barmar, not sure I understand you. You mean something like this `JOIN (select p.id, p.name, pa.attribute_id, pa.value_id WHERE (pa.attribute_id=11 AND pa.value_id=1))`? — user3013494, Jun 04 '17 at 04:33

score 2 · Accepted Answer · answered Jun 04 '17 at 05:05

2

You need to use a solution similar to

How to return rows that have the same column values in MySql

but check multiple columns

SELECT product_id
FROM product_attributes AS pa
GROUP by product_id
HAVING SUM(pa.attribute_id=11 AND pa.value_id=1) > 0
AND SUM(pa.attribute_id=13 AND pa.value_id=3) > 0

Another solution is to join the queries for each set of values.

SELECT pa1.product_id
FROM product_attributes AS pa1
JOIN product_attributes AS pa2 ON pa1.product_id = pa2.product_id
WHERE pa1.attribute_id=11 AND pa1.value_id=1
AND pa2.attribute_id=13 AND pa2.value_id=3

answered Jun 04 '17 at 05:05

Barmar

741,623
53
500
612

Thank you! Can you give your advice, which query is fastest? – user3013494 Jun 04 '17 at 13:46
I suppose, that HAVING is slowing final query, correct? – user3013494 Jun 04 '17 at 13:52
Yes. The first query does lots of aggregation of all the data, then filters it down to the rows you want. The second can use indexes to find the rows, and then just pair them up. – Barmar Jun 04 '17 at 20:03

score 0 · Answer 2 · answered Jun 04 '17 at 09:43

With the query you have in your question, the where clause will never be true because for a single row, attribute_id will never equal both 11 and 13 while value_id = both 1 and 3.

Try this:

select 
    p.id, 
    p.name, 
    pa1.attribute_id, 
    pa1.value_id, 
    pa2.attribute_id, 
    pa2.value_id 
FROM products p 
JOIN product_attributes pa1 
ON p.id = pa1.product_id 
JOIN product_attributes pa2 
ON p.id = pa2.product_id 
WHERE ((pa1.attribute_id=11 AND pa1.value_id=1) AND (pa2.attribute_id=13 AND pa2.value_id=3))

It joins to the attribute table twice so you can separate the two conditions and test for both being true.

Thank you! It wotks for me too. – user3013494 Jun 04 '17 at 13:47 — user3013494, Jun 04 '17 at 13:47

How to select data from EAV structure with multiple values

2 Answers2