SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 28.05.2016 18:14:35

remenikomer
Завсегдатай
Зарегистрирован: 28.05.2016
Сообщений: 26

Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

Много где читал информацию по подобно задаче, но конкретных решений так и не нашел.
Нужно вывести объекты через фильтрацию по доп.полям (различные параметры). В каждом разделе параметры объектов свои.
Параметры (они же доп.поля) со всеми вариантами выбора (назовем их свойства), типом отображения фильтра (список, чекбокс, интервал и т.п.) сохраняются в одной таблице (db_fields). Значения, которые выбираются из этих доп.полей для каждого объекта хранятся в другой таблице.
Итак....
Есть три таблицы:
db_object
id, name и т.п.
db_fields (доп.поля)
id, object_id, field_id, name (название поля), un_name (ЧПУ название поля)
db_data (выбранные значения для объекта)
id, object_id, field_id, values (TEXT, например, "black|red" или "1.8")

Пользователь выбирает в блоке фильтров параметры, например,
1. цвет: красный, синий, зеленый
2. вес: от 1 до 5 кг
3. мощность: от 500 Вт
и т.д.
Делается выборка таких параметров, группируется (если надо) по id объекта. И данные строки выбираются из таблицы объектов через IN(), где дальше и вся проблема.
В условии выбирается примерно так:

цвет (название доп.поля) REGEXP (красный|синий) И мощность > 500 И ( вес < 2,5 ИЛИ вес > 1 )

Например так:

SELECT db_data.id_object
        FROM db_data
            INNER JOIN db_fields
                ON db_data.id_field = db_fields.id
        WHERE
               ( db_fields.un_name = 'revers_u_myasorubki' AND db_data.values REGEXP '[[:<:]](yes|no)[[:>:]]' )
               AND   ( db_fields.un_name = 'proizvoditelnost_myasorubki'  AND db_data.values >= 1.5 AND db_data.values <= 4   )
               AND   ( db_fields.un_name = 'moshhnost_myasorubki'  AND db_data.values >= 120  AND db_data.values <= 600)
        GROUP BY db_data.id_object

Но так ничего не выведет, т.к. таблица "двухмерная". Нужно как-то их объединить другим способом.
Подскажите, пожалуйста, кто как делает с фильтрами?

Отредактированно remenikomer (28.05.2016 18:29:36)

Неактивен

 

#2 28.05.2016 19:56:53

klow
Старожил
Зарегистрирован: 06.12.2014
Сообщений: 411

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

Примерно так


SELECT * FROM db_object o
WHERE EXISTS (
    SELECT 1
        FROM db_data d
            JOIN db_fields f1 ON d.field_id = f1.id
            JOIN db_fields f2 ON d.field_id = f2.id
            JOIN db_fields f3 ON d.field_id = f3.id
        WHERE d.object_id = o.id
               ( f1.un_name = 'revers_u_myasorubki' AND d.values REGEXP '[[:<:]](yes|no)[[:>:]]' )
               AND   ( f2.un_name = 'proizvoditelnost_myasorubki'  AND d.values >= 1.5 AND db_data.values <= 4   )
               AND   ( f3.un_name = 'moshhnost_myasorubki'  AND d.values >= 120  AND db_data.values <= 600)
     )
       

И таблица превращается в "трехмерную".

Отредактированно klow (28.05.2016 20:02:01)

Неактивен

 

#3 28.05.2016 23:19:25

remenikomer
Завсегдатай
Зарегистрирован: 28.05.2016
Сообщений: 26

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

klow написал:

Примерно так ...
И таблица превращается в "трехмерную".

Подставил я конкретные данные и выдает ошибку:

FUNCTION o.id does not exist Запрос3.sql

Не могу понять в чем ошибка.
Запрос:


SELECT * FROM db_objects o
WHERE EXISTS (
    SELECT 1
        FROM db_fieldsdata d
            JOIN db_fields f1 ON d.id_field = f1.id
            JOIN db_fields f2 ON d.id_field = f2.id
            JOIN db_fields f3 ON d.id_field = f3.id

        WHERE d.id_object = o.id
               ( f1.un_name = 'revers_u_myasorubki' AND d.val REGEXP '[[:<:]](yes|no)[[:>:]]' )
               AND   ( f2.un_name = 'proizvoditelnost_myasorubki'  AND d.val >= 1.5 AND db_fieldsdata.val <= 4   )
               AND   ( f3.un_name = 'moshhnost_myasorubki'  AND d.val >= 120  AND db_fieldsdata.val <= 600)
)
       

И еще подскажите, пожалуйста, что за  единица: "SELECT 1". Опечатка?

Отредактированно remenikomer (28.05.2016 23:20:20)

Неактивен

 

#4 28.05.2016 23:42:57

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

опечатка, нужно
         WHERE d.id_object = o.id AND

Неактивен

 

#5 28.05.2016 23:44:12

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

remenikomer написал:

И еще подскажите, пожалуйста, что за  единица: "SELECT 1". Опечатка?

Нет, не опечатка. Просто выбирается единица

Неактивен

 

#6 28.05.2016 23:49:03

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

Но, это я комментирую чужой ответ исходя из соображений здравого смысла. Что именно нужно я не очень понял исходя из постановки задачи.
Если вы приведете пример тестовых данных на десяток строк ( create table.., insert into..) и какой результат должен быть при заданных параметрах, возможно я подскажу другой ответ.

Неактивен

 

#7 28.05.2016 23:52:52

remenikomer
Завсегдатай
Зарегистрирован: 28.05.2016
Сообщений: 26

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

vasya написал:

опечатка, нужно
         WHERE d.id_object = o.id AND

стало работать, но выводит 0 строк. Если оставить только один параметр (фильтр), например

AND ( f1.un_name = 'sokovyzhimalka_v_myasorubke' AND d.val REGEXP '[[:<:]](otsutstvuet|shnekovaya)[[:>:]]' )

То объекты выводятся. А если указать два фильтра, типа:

AND ( f1.un_name = 'revers_u_myasorubki' AND d.val REGEXP '[[:<:]](yes|no)[[:>:]]' )
AND ( f1.un_name = 'sokovyzhimalka_v_myasorubke' AND d.val REGEXP '[[:<:]](otsutstvuet|shnekovaya)[[:>:]]' )

Все пусто.
По отдельности каждое условие срабатывает. Оба сразу нет.

У меня раньше было так:

remenikomer написал:


IN (
SELECT  db_fieldsdata.id_object
FROM db_fieldsdata
  INNER JOIN db_fields
   ON db_fieldsdata.id_field = db_fields.id
WHERE
  ( db_fields.un_name = 'power' AND db_fieldsdata.val >= 0.5 AND db_fieldsdata.val <= 101.5 )
AND ( db_fields.un_name = 'color' AND db_fieldsdata.val REGEXP '[[:<:]]("red|black")[[:>:]]' )
GROUP BY db_fieldsdata.id_object
)
 

Результат такой же 0 строк при двух параметрах, а при одном выводит объекты.
Если фильтры ставить через OR, то срабатывают все. Но, как понимаете, это не сужает поиски, а суммирует.

Отредактированно remenikomer (28.05.2016 23:57:50)

Неактивен

 

#8 28.05.2016 23:58:15

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

Интуитивно, вам нужно что-то вроде

SELECT db_data.id_object
        FROM db_data
            INNER JOIN db_fields
                ON db_data.id_field = db_fields.id
        WHERE
               ( db_fields.un_name = 'revers_u_myasorubki' AND db_data.values REGEXP '[[:<:]](yes|no)[[:>:]]' )
               OR   ( db_fields.un_name = 'proizvoditelnost_myasorubki'  AND db_data.values >= 1.5 AND db_data.values <= 4   )
               OR   ( db_fields.un_name = 'moshhnost_myasorubki'  AND db_data.values >= 120  AND db_data.values <= 600)
        GROUP BY db_data.id_object HAVING count(*)=3


но без примера с тестовыми данными сложно сказать точно

Неактивен

 

#9 29.05.2016 00:00:23

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

Если я правильно понимаю, то у вас проблема типа этой

Неактивен

 

#10 29.05.2016 00:04:02

remenikomer
Завсегдатай
Зарегистрирован: 28.05.2016
Сообщений: 26

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

vasya написал:

Если я правильно понимаю, то у вас проблема типа этой

Нет. Там у ТС один параметр (назовем его фильтр) и у него выбираются различные свойства. Там можно все через REGEXP вывести, я думаю и таблицы по-другому сделать.
В моем же случае несколько фильтров и у каждого фильтра по несколько свойств. Как в интернет-магазинах фильтруются товары по параметрам, сужая круг поиска.

vasya написал:

Но, это я комментирую чужой ответ исходя из соображений здравого смысла. Что именно нужно я не очень понял исходя из постановки задачи.
Если вы приведете пример тестовых данных на десяток строк ( create table.., insert into..) и какой результат должен быть при заданных параметрах, возможно я подскажу другой ответ.

Файл с двумя таблицами: поля и данные объектов. Вот из них и нужно выбрать объекты по параметрам.

Отредактированно remenikomer (29.05.2016 00:07:14)


Прикрепленные файлы:
Attachment Icon db_f_d.zip, Размер: 4,194 байт, Скачано: 703

Неактивен

 

#11 29.05.2016 00:28:38

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

Оно?

MariaDB [test]> SELECT id_object FROM db_fieldsdata fd join db_fields d on id_fi
eld=d.id
    -> where (un_name = 'revers_u_myasorubki' AND val REGEXP '[[:<:]](yes|no)[[:
>:]]'
)
    -> or (un_name = 'proizvoditelnost_myasorubki' AND val >= 1.5 AND val <= 4)
    -> or (un_name = 'moshhnost_myasorubki'  AND val >= 120  AND val <= 600)
    -> group by 1 having count(*)=3;
+-----------+
| id_object |
+-----------+
|        53 |
|        62 |
|        84 |
|        98 |
|       114 |
|       183 |
|       207 |
|       211 |
+-----------+
8 rows in set (0.02 sec)

Неактивен

 

#12 29.05.2016 00:35:52

remenikomer
Завсегдатай
Зарегистрирован: 28.05.2016
Сообщений: 26

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

vasya написал:

Оно?

MariaDB [test]> SELECT id_object FROM db_fieldsdata fd join db_fields d on id_fi
eld=d.id
    -> where (un_name = 'revers_u_myasorubki' AND val REGEXP '[[:<:]](yes|no)[[:
>:]]'
)
    -> or (un_name = 'proizvoditelnost_myasorubki' AND val >= 1.5 AND val <= 4)
    -> or (un_name = 'moshhnost_myasorubki'  AND val >= 120  AND val <= 600)
    -> group by 1 having count(*)=3;
+-----------+
| id_object |
+-----------+
|        53 |
|        62 |
|        84 |
|        98 |
|       114 |
|       183 |
|       207 |
|       211 |
+-----------+
8 rows in set (0.02 sec)

Дело в том, что если условия писать через OR, то все работает в коде выше и в вашем случае. Но мне нужно учитывать сразу все фильтры, т.е. через оператор AND.
Иначе что получается. Выбрать мясорубку с реверсом и без, или выбрать мясорубку с производительностью от 1,5 до 4кг. Т.е. будет минимум 2 мясорубки, совершенно разные. А на самом деле подразумевается, что нужно выбрать мясорубку, где реверс есть или нет (yes|no), при этом она еще должна иметь производительность от 1,5 до 4 кг, цвет синий, бренд LG и т.п. Примерно такая логика. Так круг поиска будет сужаться.

Отредактированно remenikomer (29.05.2016 00:38:24)

Неактивен

 

#13 29.05.2016 00:43:16

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

круг поиска и сужается, там кроме or ещё есть group by 1 having count(*)=3  (это означает, что мясорубка должна удовлетворять всем трем условиям)
я не зря просил вас привести результат, который должен быть на тестовых данных smile

Неактивен

 

#14 29.05.2016 00:56:49

remenikomer
Завсегдатай
Зарегистрирован: 28.05.2016
Сообщений: 26

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

vasya написал:

круг поиска и сужается, там кроме or ещё есть group by 1 having count(*)=3  (это означает, что мясорубка должна удовлетворять всем трем условиям)
я не зря просил вас привести результат, который должен быть на тестовых данных smile

Будут тестить завтра. Сегодня уже голова замучена... такто, шустрый запрос, вроде то, что нужно smile
А что такое: group by 1? Что такое единица в этом случае?
having count(*)=3, я так понимаю 3 это кол-во параметров, его нужно подставлять динамически?

Отредактированно remenikomer (29.05.2016 00:58:28)

Неактивен

 

#15 29.05.2016 01:04:59

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

group by 1 аналогично group by id_object
1 это номер поля в части select

да, кол-во параметров, которым должен удовлетворять объект

Неактивен

 

#16 29.05.2016 10:16:13

remenikomer
Завсегдатай
Зарегистрирован: 28.05.2016
Сообщений: 26

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

vasya написал:

group by 1 аналогично group by id_object
1 это номер поля в части select

да, кол-во параметров, которым должен удовлетворять объект

огромное спасибо. Несколько дней голова пухла, у кого только не спрашивал, никто не могу ничего сделать.
Подскажите, пожалуйста, еще по такому нюансу.
В fieldsdata.val сохраняются выбранные значения полей, тип поля TEXT. Например, там может быть "black|red", т.е. текст, либо целое (2) или float (1,75). Ваш пример вроде все выбирает правильно. Но мне некоторые советовали еще можно привести к типу так:
AND cast( db_fieldsdata.val as DECIMAL(4,2) <= 1.5 ). Другие сказали, что лучше этого не делать.
К вам больше доверия smile

Неактивен

 

#17 29.05.2016 15:10:34

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

дока говорит, что смысла нет

To cast a string to a numeric value in numeric context, you normally do not have to do anything other than to use the string value as though it were a number

Неактивен

 

#18 29.05.2016 18:58:56

remenikomer
Завсегдатай
Зарегистрирован: 28.05.2016
Сообщений: 26

Re: Помогите с SQL-запросом. Нужно вывести объекты по параметрам фильтров

vasya написал:

дока говорит, что смысла нет

To cast a string to a numeric value in numeric context, you normally do not have to do anything other than to use the string value as though it were a number

спасибо!

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson