Как составить запрос mysql?

Question

[rklimcorp]

Здравствуйте, у меня возник вопрос в следующем, есть таблица с объявлениями "Items" у каждого объявления есть своя категория, в зависимости от того какая категория у объявления такие к нему подходят и атрибуты, как я понял тут связь один ко многим ( items.category-> attr.category_id ). Список всех атрибутов находиться в таблице "Attr". У каждого объявления свои значения атрибутов и находятся они в таблице "Attr_items" тут тоже как я понял связь один ко многим( items.id-> attr_items.id_item ) "на скрине стрелочка не в ту сторону"

Вопрос:
1) Необходимо взять объявления категории "Авто/мото" у которых атрибут марка = Audi , а атрибут модель равно = "Audi А8"

2) Необходимо взять объявления категории "Авто/мото" у которых атрибут марка = Audi , а год выпуска от 2005 до 2015

Вот скрины:
prntscr.com/edn3lq
prntscr.com/edn3sg

SELECT * FROM `items` inner join `attr_item` ON `items`.id = `attr_item`.id_item AND (( `attr_item`.id_attr = '1' AND `attr_item`.val = '2' ) AND ( `attr_item`.id_attr = '2' AND `attr_item`.val = '11' ) ) WHERE (`category`='2')

я додумался только до такого (тут другие категории и объявления) помогите пожалуйста

Answer 1

[Денис Великанов]

У вас между таблицами ITEMS и ATTR связь M:N (многие ко многим) поэтому логичнее всего выбирать из вспомогательной таблицы ATTR_ITEMS и связывать с ней уже прочие таблицы.
То есть основное тело запроса (до конструкции WHERE) будет что-то вроде

SELECT *
FROM `attr_items` ai
inner join `items` i on ai.id_item = i.id
inner join `attr` a on ai.id_attr = a.id

Не совсем понятно, как определяется значение для категории.
Допустим, что категория "Авто/мото" имеет id = 2, тогда для первой части вопроса:

SELECT *
FROM `attr_items` ai
inner join `items` i on ai.id_item = i.id
inner join `attr` a on ai.id_attr = a.id
WHERE
/* условие для категории */
(a.category_id = i.category and i.category = 2) and
/* условие для марки автомобиля */
(a.name = 'Марка' and ai.val = 'Audi') and
/* условие для модели автомобиля*/
(a.name = 'Модель' and ai.val = 'Audi А8')

для второй части вопроса:

SELECT *
FROM `attr_items` ai
inner join `items` i on ai.id_item = i.id
inner join `attr` a on ai.id_attr = a.id
WHERE
/* условие для категории */
(a.category_id = i.category and i.category = 2) and
/* условие для марки автомобиля */
(a.name = 'Марка' and ai.val = 'Audi') and
/* условие для года выбуска автомобиля*/
(a.name = 'Годвыпуска' and ai.val BETWEEN 2005 and 2015)

Обращаю внимание, что поле ATTR_ITEMS.VAL похоже имеет тип данных varchar, поэтому BETWEEN к для года выпуска нему может быть не применимо в том виде, что я указал. Возможно надо использовать конструкцию LIKE.

P.S. На мой взгляд, такая структура БД пригодна только для маленьких объемов данных. Но, к сожалению, она часто применяется в 1С-Bitrix на продакшене, что приводит к тормозам.

Comments

[rklimcorp]

Денис, спасибо большое за ответ, но не работает такой запрос, джоинит он все верно, а вот ищет не верно, выдает пустой запрос, я думаю это из за того что
стоит условие "AND" между скобками, подскажите пожалуйста как можно это обыграть?
Конструкция через IN не подойдет, надо четко искать по атрибутам

/* условие для категории */
(a.category_id = i.category and i.category = 2) and
/* условие для марки автомобиля */
(a.name = 'Марка' and ai.val = 'Audi') and
/* условие для года выбуска автомобиля*/
(a.name = 'Годвыпуска' and ai.val BETWEEN 2005 and 2015)

[Денис Великанов]

rklimcorp: Только второй запрос пустой или оба? Если только второй, то проблема в BETWEEN, как я и говорил. Вообще изначально проблема в паршивой структуре БД, т.к. мешать строковые значения с числовыми в одном поле - это плохо.
Поскольку речь идёт о СУБД MySQL, то можно вместо BETWEEN использовать строковый оператор сравнения по регулярному выражению REGEXP.
/* условие для года выбуска автомобиля*/
(a.name = 'Годвыпуска' and ai.val REGEXP "^20(0[0-9]|1[0-5])$")

В-общем, поэкспериментируйте с регулярным выражением в REGEXP.