Query based on recorded criteria

Question

There are two tables,

Categories(Id, Name)
Products(Id, Text, CategoryId)

There is another table for filtering products:

Filter(CategoryIds, ContainText)

CategoryIds in Filter table is a comma-separated: 100,101

We want to query products based on criteria extracted from Filters table.

Example: Filters have only two records:

CategoryIds   |   ContainText
-----------------------------
100,101       |   A
200,201       |   B

Here are products we want to query:

containting text 'A' in categories 100 or 101

Or

containting text 'B' in categories 200 or 201

We would not like to use dynamic query.

Thanks for any help.

Your `Filter` tables needs normalizing. Storing comma separated values like this is a very bad idea. — Giorgos Betsos, Apr 25 '16 at 07:18
if you have any idea to normalizing Filter table please hint me. — Jafar Elahi, Apr 25 '16 at 07:20
I think it has clear data, there are some products by some categories. — Jafar Elahi, Apr 25 '16 at 07:20
You need to change it to 2 tables, one for the text and one for the categories linked to that text. — Zohar Peled, Apr 25 '16 at 07:21
You also need to [read this post.](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — Zohar Peled, Apr 25 '16 at 07:22
Yes, it is a good idea to change Filter table to 2 tables. thank you. but I think the query will be complicated anyway. — Jafar Elahi, Apr 25 '16 at 07:23

Felix Pamittan · Answer 1 · 2016-04-25T07:36:03.057

As per Giorgos comment, you need to normalize your Filter table. If you're stuck with this design, here is a solution:

First, you need to have a string splitter. Here is one taken from Aaron Bertrand's article:

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN 
   (  
      SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + REPLACE(@List, @Delimiter, '</i><i>') 
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
   );

Then, you need to split your Filter table into separate rows. Then do JOINs on the Products, Categories and the splitted Filter table:

WITH CteFilter(CategoryId, ContainText) AS(
    SELECT
        CAST(s.Item AS INT), f.ContainText
    FROM Filter f
    CROSS APPLY dbo.SplitStrings_XML(f.CategoryIds, ',') s
)
SELECT p.*
FROM Products p
INNER JOIN Categories c
    ON c.Id = p.CategoryId
INNER JOIN CteFilter f
    ON f.CategoryId = c.Id
    AND p.Text LIKE '%' + f.ContainText + '%'

@JafarElahi No problem. If you could normalize your table, I suggest you do so. — Felix Pamittan, Apr 25 '16 at 07:55

score 1 · Answer 2 · answered Apr 25 '16 at 07:38

Assuming normalization is possible, here is a simple query that will do the trick:

Create sample tables:

DECLARE @Categories as table 
(
    id int identity(1,1), 
    name char(1)
)

DECLARE @Products as table 
(
    id int identity(1,1), 
    name varchar(100),
    categoryId int
)

DECLARE @Filter as table 
(
    id int identity(1,1), 
    ContainText varchar(100)
)

DECLARE @FilterCategories as table 
(
    FilterId int, 
    CategoryId int,
    PRIMARY KEY(filterId, CategoryId)
)

Populate sample tables:

INSERT INTO @Categories 
VALUES ('A'),('B'),('C'),('D'),('E')

INSERT INTO @Products (name, categoryId) 
VALUES ('cat A', 1),('category A', 1), ('cat B', 2), ('category B', 2)

INSERT INTO @Filter 
VALUES ('gory'), ('cat')

INSERT INTO @FilterCategories 
VALUES (1, 1), (1, 2), (2, 1)

The query:

SELECT  DISTINCT p.id As ProductId, 
        p.name As ProductName,
        c.name As CategoryName
FROM @Filter f
INNER JOIN @FilterCategories fc ON(f.id = fc.FilterId)
INNER JOIN  @Products p ON(p.categoryId = fc.CategoryId)
INNER JOIN @Categories c ON(p.categoryId = c.id)
WHERE p.name LIKE '%'+ f.ContainText +'%'

Results:

ProductId   ProductName                    CategoryName
----------- ------------------------------ ------------
1           cat A                          A
2           category A                     A
4           category B                     B

DhruvJoshi · Answer 3 · 2016-04-25T07:58:18.737

1

You can also try the below query which does not require normalization. So in case you are unable to update your tables, try the below query which is simple JOIN

--create table categories(id int, name varchar(100));
--create table products(id int, text varchar(100), categoryid int);
--create table filters(categoryids varchar(100), containtext varchar(10));
--insert into filters values
--('100,101','A'),
--('200,201','B');
--insert into products values 
--(1,'Random',100),
--(2,'rps',101),
--(3,'rps',200),
--(4,'rpsb',201);
--insert into categories values 
--(100,'Cat100'),
--(101,'Cat101'),
--(200,'Cat200'),
--(201,'Cat201');

select 
  P.*,C.Name 
from Products P join Categories C 
      on C.Id=P.CategoryId 
join Filters F 
     on text like '%'+ContainText+'%' 
          and   ','+ CategoryIds +',' like '%,'+cast(C.ID as varchar(100)) +',%'

edited Apr 25 '16 at 07:58

answered Apr 25 '16 at 07:48

DhruvJoshi

17,041
6
41
60

Good approach! but I think performance will not be efficient. because product table is a big table and string operation on it will be low actually. – Jafar Elahi Apr 25 '16 at 08:04
@JafarElahi The performance will be below par when you compare against a normalized table where we store category id as int. However if you are stuck with design, you may go this route. The query took 4 seconds on my machine for a million rows. – DhruvJoshi Apr 25 '16 at 08:07

score 0 · Answer 4 · answered Apr 25 '16 at 07:43

To normalize Filters table you can use something like this:

DECLARE @xml xml

;WITH Filters AS (
SELECT *
FROM (VALUES
('100,101', 'A'),
('200,201', 'B'),
('300,301,302', 'C')
) as t(CategoryIds, ContainText)
)

SELECT @xml =(
SELECT CAST ('<b>' + ContainText + '<a>'+REPLACE(CategoryIds,',','</a><a>') + '</a></b>' as xml)
FROM Filters
FOR XML PATH('')
)

SELECT  t.v.value('../.','nvarchar(1)') as ContainText,
        t.v.value('.','int') as CategoryId
FROM @xml.nodes('/b/a') as t(v)

Output:

ContainText CategoryId
----------- -----------
A           100
A           101
B           200
B           201
C           300
C           301
C           302

(7 row(s) affected)

Then you can join this table to Products and Categories:

SELECT *
FROM NormFilters nf
INNER JOIN Categories c  -- (Id, Name)
    ON nf.CategoryId = c.Id
INNER JOIN Products p  --(Id, Text, CategoryId)
    ON c.CategoryId = p.CategoryId AND p.[Text] LIKE '%' + nf.ContainText +'%'

Query based on recorded criteria

4 Answers4