Как фильтровать результаты с помощью реляционного деления в динамическом поисковом запросе?

Используя следующий запрос, я собираюсь отфильтровать результаты по выбранному tags или categories:

DECLARE @categories NVARCHAR(MAX),
        @tags NVARCHAR(MAX);

SELECT @categories = '1,2,4',  -- comma separated category ids
       @tags = '2,3'           -- comma separated tag ids

SELECT p.id,
       p.title,
       p.price
FROM tbl_products p
  LEFT JOIN tbl_product_categories pc ON @categories IS NOT NULL AND pc.product_FK = p.id
  LEFT JOIN tbl_product_tags pt ON @tags IS NOT NULL AND pt.product_FK = p.id
WHERE ( p.price >= @min_price OR @min_price IS NULL )
  AND ( p.price <= @max_price OR @max_price IS NULL )
  AND ( pc.category_FK IN (SELECT value FROM STRING_SPLIT(@categories, ',')) OR @categories IS NULL )
  AND ( pt.tag_FK IN (SELECT value FROM STRING_SPLIT(@tags, ',')) OR @tags IS NULL)
GROUP BY p.id
HAVING COUNT(p.id) = ( (SELECT COUNT(*) FROM STRING_SPLIT(@categories, ',')) + (SELECT COUNT(*) FROM STRING_SPLIT(@tags, ',')) )

Но это не дает ожидаемых результатов! Я подозреваю, что часть HAVING не используется правильно, поскольку она не производит правильный подсчет каждый раз на основе переданных тегов и идентификаторов категорий.

Кто-нибудь знает, как мы могли бы реализовать такие ситуации, применить реляционное разделение для извлечения продуктов, у которых все эти переданы @categories и @tags вместе ??? Есть способ лучше?

- обновление: например, используйте следующую дату образца:

tbl_products:
id  title     price
===================
1   mouse       10
2   keyboard    18
3   iphone 8    100
4   note 8      90

tbl_product_categories:
product_FK category_FK
======================
1           1
2           1
3           2
4           2

tbl_product_tags:
product_FK tag_FK
=================
1           1
3           1
3           2
4           2

поэтому, если мы передадим @categories = '2', @tags = '1,2' и min_price = 50, мы должны получить iphone 8


sql sql-server relational-division
person dNitro    schedule 28.10.2017    source источник
comment
Мы можем больше помочь, если вы добавите несколько примеров данных из ваших таблиц и ожидаемого результата вашего запроса.   -  person Abdullah Dibas    schedule 28.10.2017
comment
@AbdullahDibas, конечно; обновлено.   -  person dNitro    schedule 28.10.2017

Ответы (2)


arrow_upward
0
arrow_downward

Судя по вашим образцам данных, я думаю, что вы присоединяетесь не к тому столбцу tag_FK вместо product_FK, поэтому LEFT JOIN в таблице tbl_product_tags должно быть:

LEFT JOIN tbl_product_tags pt ON @tags IS NOT NULL AND pt.product_FK = p.id

Кроме того, я не думаю, что есть необходимость использовать оператор HAVING в вашем запросе, мне кажется, что вы используете его как дополнительную проверку; поскольку ваш запрос уже выполняет фильтрацию. Однако условие после оператора HAVING неверно, и лучший пример, подтверждающий это, - это сам ваш пример:

1. count of p.Id  = 1 (p.Id = 3 ... iPhone 8)
2. count of categories = 1 (category: 2)
3. count of tags = 2  (tags: 1, 2)

тогда в этом случае количество p.Id не равно количеству переданных категорий и тегов.

ОБНОВЛЕНИЕ: на основе @dtNiro запрос должен быть следующим:

DECLARE @categories NVARCHAR(MAX),
        @tags NVARCHAR(MAX);

SELECT @categories = '1,2,4',  -- comma separated category ids
       @tags = '2,3'           -- comma separated tag ids

SELECT p.id,
       p.title,
       p.price
FROM tbl_products p
  LEFT JOIN tbl_product_categories pc ON @categories IS NOT NULL AND pc.product_FK = p.id
  LEFT JOIN tbl_product_tags pt ON @tags IS NOT NULL AND pt.product_FK = p.id
WHERE ( p.price >= @min_price OR @min_price IS NULL )
  AND ( p.price <= @max_price OR @max_price IS NULL )
  AND ( pc.category_FK IN (SELECT value FROM STRING_SPLIT(@categories, ',')) OR @categories IS NULL )
  AND ( pt.tag_FK IN (SELECT value FROM STRING_SPLIT(@tags, ',')) OR @tags IS NULL)
GROUP BY p.id
HAVING (@tags IS NULL OR (COUNT(p.id) = (SELECT COUNT(*) FROM STRING_SPLIT(@tags, ','))))
person Abdullah Dibas    schedule 28.10.2017
comment
проблема соединения была опечаткой, исправлено :) HAVING здесь, потому что нам нужно иметь tags 1 и 2 одновременно. подобные решения, предлагаемые здесь: stackoverflow.com/a/7774879/5048383. если мы не проверяем счетчик, он будет сопоставлять продукты с tag 1 или tag 2, но нам нужны оба одновременно - person dNitro; 28.10.2017
comment
@dNitro Может ли продукт входить в несколько категорий? - person Abdullah Dibas; 28.10.2017
comment
Tanx @Abdullah Dibas. но я думаю, что мы также должны учитывать categories count, как ответ SqlZim. - person dNitro; 28.10.2017
comment
Я не рассматривал фильтрацию по количеству категорий, так как думал, что каждый продукт связан только с одной категорией, поэтому столбец, в котором количество p.Id может быть больше единицы, - это tag_FK, а не category_FK. - person Abdullah Dibas; 28.10.2017

arrow_upward

arrow_downward

person    schedule
comment
поразительнй. работает как шарм. по ссылкам я прочитал Условия динамического поиска и должен прочитать три других. Танкс человек. - person dNitro; 28.10.2017
comment
@DNitro Рад помочь! - person SqlZim; 28.10.2017