Архитектура БД для фильтрации товаров — как выбрать сразу по нескольким свойствам?

Question

[testonder]

Здравствуйте, коллеги!

Пытаюсь организовать хранение товаров и их свойств в MySQL. И при этом иметь возможность фильтровать товары по значениям свойств.
Создано четыре таблицы
`article` - id, name
+----+------------------+
| id | name |
+----+------------------+
| 1 | Футболка |
| 2 | Майка |
+----+------------------+

`prop` - id, name
+----+--------------+
| id | name |
+----+--------------+
| 1 | Цвет |
| 2 | Размер |
+----+--------------+

`prop_value` - id, name
+----+----------------+
| id | value |
+----+----------------+
| 1 | Белый |
| 2 | Красный |
| 3 | Большой |
+----+----------------+

`prop2article` - id, article_id, prop_id, prop_value_id
+----+---------+---------------+------------+
| id | prop_id | prop_value_id | article_id |
+----+---------+---------------+------------+
| 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 1 |
| 3 | 2 | 3 | 1 |
+----+---------+---------------+------------+

Теперь пытаюсь выбрать товар сразу по двум свойствам - Цвет (1) = Белый (1) и Размер (2) = Большой (3):

SELECT DISTINCT article.name
FROM article
INNER JOIN prop2article ON article.id = prop2article.article_id
WHERE
	(prop2article.prop_id = 1 and prop2article.prop_value_id = 1)
	AND
	(prop2article.prop_id = 2 and prop2article.prop_value_id = 3)

И в итоге запрос ничего не отдаёт. Что я делаю не так?
Заранее благодарен за помощь!

Answer 1

[Андрей Ласточкин]

SELECT DISTINCT article.name
FROM article
INNER JOIN prop2article P1 ON article.id = P1.article_id
INNER JOIN prop2article P2 ON article.id = P2.article_id
WHERE
  (P1.prop_id = 1 and P1.prop_value_id = 1)
  AND
  (P2.prop_id = 2 and P2.prop_value_id = 3)

Comments

[testonder]

Теперь понял в чём дело, спасибо Вам! На каждое свойство товара получается нужно делать JOIN.

Answer 2

[Алексей Рытиков]

Ошибка в употреблении в условии WHERE:

WHERE
  (prop2article.prop_id = 1 and prop2article.prop_value_id = 1)
  AND
  (prop2article.prop_id = 2 and prop2article.prop_value_id = 3)

Нужно

WHERE
  (prop2article.prop_id = 1 and prop2article.prop_value_id = 1)
  OR
  (prop2article.prop_id = 2 and prop2article.prop_value_id = 3)

Через INNER JOIN вы получили вот такие записи:
| article.id | article.name | prop2article.id | prop2article.prop_id | prop2article.prop_value_id | prop2article.article_id |
| 1 | Футболка | 1 | 1 | 1 | 1 |
| 1 | Футболка | 2 | 1 | 2 | 1 |
| 1 | Футболка | 3 | 2 | 3 | 1 |
…

В каких из этих записей есть и цвет и размер? Есть только цвет такой-то или размер такой-то.

Comments

[testonder]

Да, точно...
Но мне нужно фильтровать и по заданному цвету и по заданному размеру, а в случае использования OR условия будут работать по отдельности. Возможно ли как-то сделать? Или придётся переделывать структуру таблиц?

[Алексей Рытиков]

testonder: Андрей Ласточкин уже ответил :)