How to filter results using relational division in a dynamic search query?

Question

Using following query i'm going to filter results based on selected tags or 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, ',')) )

But it does not produce expected results! I am suspicious that HAVING part does not employed correctly as it does not produce right count every time based on passed tags and category ids.

Does anyone know how we could implement such situations, apply relational division to extract products which have all these passed @categories and @tags in common??? Any better way?

-- update: for example use the following sample date:

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

so if we pass @categories = '2' and @tags = '1,2' and min_price = 50 then we should get an iphone 8

We can help more if you could add some sample data of your tables and the expected output of your query. — Abdullah Dibas, Oct 28 '17 at 08:58

SqlZim · Accepted Answer · 2017-10-28T12:58:52.740

Instead of trying to add counts from your variables, you can use count(distinct [tags|categories]) equals the count for the respective parameter like so:

declare @categories nvarchar(max), @tags nvarchar(max), @min_price int, @max_price int;
select 
    @categories = '2'  -- comma separated category ids
  , @tags = '1,2'      -- comma separated tag ids
  , @min_price = 0
  , @max_price = power(2,30)

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, p.title, p.price
having (count(distinct pc.category_fk) = (select count(*) from string_split(@categories, ',')) or @categories is null) 
   and (count(distinct pt.tag_fk) = (select count(*) from string_split(@tags, ',')) or @tags is null)

demo: dbfiddle.uk demo

returns:

+----+----------+-------+
| id |  title   | price |
+----+----------+-------+
|  3 | iphone 8 |   100 |
+----+----------+-------+

When it comes to performance, you will benefit from rewriting this as a procedure with dynamic sql execution, or at least option (recompile) as shown in these references:

Here is an example of a dynamic sql search procedure for your query that uses exists ...having count()... instead of left join... where... having count(distinct ...) that simplifies the plan a bit (plan comparison demo):

create procedure product_search (
    @categories nvarchar(max)
  , @tags nvarchar(max)
  , @min_price int
  , @max_price int
) as 
begin;
set nocount, xact_abort on;
declare @sql nvarchar(max);
declare @params nvarchar(256);
set @params = '@categories nvarchar(max), @tags nvarchar(max), @min_price int, @max_price int';
set @sql = ';
select
    p.id
  , p.title
  , p.price
from tbl_products p
where 1=1'
if @min_price is not null
set @sql = @sql + '
  and p.price >= @min_price';
if @max_price is not null
set @sql = @sql + '
  and p.price <= @max_price';
if @categories is not null 
set @sql = @sql + '
  and exists (
      select 1 
      from tbl_product_categories ic
      where ic.product_fk = p.id
        and ic.category_fk in (select value from string_split(@categories, '',''))
      having count(*) = (select count(*) from string_split(@categories, '',''))
      )';
if @tags is not null 
set @sql = @sql + '
  and exists (
      select 1 
      from tbl_product_tags it
      where it.product_fk = p.id
        and it.tag_fk in (select value from string_split(@tags, '',''))
      having count(*) = (select count(*) from string_split(@tags, '',''))
      )';

exec sp_executesql @sql, @params, @categories, @tags, @min_price, @max_price;
end;

executed like so:

declare @categories nvarchar(max), @tags nvarchar(max), @min_price int, @max_price int;
select 
    @categories = null  -- comma separated category ids
  , @tags = '1,2'      -- comma separated tag ids
  , @min_price = null
  , @max_price = power(2,30)

exec product_search @categories, @tags, @min_price, @max_price

demo: dbfiddle.uk demo

returns:

+----+----------+-------+
| id |  title   | price |
+----+----------+-------+
|  3 | iphone 8 |   100 |
+----+----------+-------+

fabulous. works like a charm. from the links i've read [Dynamic Search Conditions](http://www.sommarskog.se/dyn-search.html) and must read other three ones. Tanx man. — dNitro, Oct 28 '17 at 15:09

Abdullah Dibas · Answer 2 · 2017-10-28T11:01:17.370

From your sample data I think you're joining on the wrong column tag_FK instead of product_FK, so the LEFT JOIN on the table tbl_product_tags should be:

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

Also, I don't think there is a need to use HAVING statement in your query, it seems to me that you're using it as an extra check; since your query is already doing the filtering job. However, the condition after HAVING statement is not correct, and the best example to prove that is your example itself:

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)

then in this case the count of p.Id is not equal to the count of the passed categories and tags.

UPDATE : based on @dtNiro the query should be as follows:

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, ','))))

the join problem was a typo, corrected :) `HAVING` is there because we need to have `tags` 1 and 2 at the same time. like solutions proposed here: https://stackoverflow.com/a/7774879/5048383. if we does not check count then it will matchs products with `tag 1` or `tag 2` but we need both at the same time — dNitro, Oct 28 '17 at 10:10
Tanx @Abdullah Dibas. but i think we should also take `categories` count into account like SqlZim 's answer. — dNitro, Oct 28 '17 at 15:11
I didn't consider filtering on categories count since I thought that each product is linked to only one category, therefore the column that may make the count of p.Id higher than one is tag_FK not category_FK. — Abdullah Dibas, Oct 28 '17 at 16:59

How to filter results using relational division in a dynamic search query?

2 Answers2