T-SQL Query for Vertical Table Structure

Question

I'm working on an e-commerce project. Now I have to build a filter for product listing page. My tables are below.

Products

id title      | description           | Etc.
-- ---------- | --------------------- | -----------
1  Product  1 | Product 1 description | xxx
2  Product  2 | Product 2 description | xxx
3  Product  3 | Product 3 description | xxx
4  Product  4 | Product 4 description | xxx
5  Product  5 | Product 5 description | xxx

Specifications

id title      | Etc.
-- ---------- | ------
1  Color      | xxx
2  Display    | xxx

ProductSpecifications

id          | productId   | specificationId | value
----------- | ----------- | --------------- | -----
1           | 1           | 1               | Red
2           | 1           | 2               | LED
3           | 2           | 1               | Red
4           | 2           | 2               | OLED
5           | 3           | 1               | Blue
6           | 3           | 2               | LED
7           | 4           | 1               | Blue
8           | 4           | 2               | OLED

Users of e-commerce must be able to filter multiple options at the same time. I mean, a user may want to search for "(Red or Blue) and OLED" TVs.

I tried something but i couldn't write the right stored procedure. I guess, i'm stuck here and i need some help.

EDIT :

After some answers, I need to update some additional information here.

The specifications are dynamic. So filters are also dynamic. I generate filters by using a bit column named allowFilter. So I cant use strongly typed parameters like @color or @display

Users may not use filter. Or they may use one or more filter. You can find the query that i'm working on here:

ALTER PROCEDURE [dbo].[ProductsGetAll]
@categoryId int,
@brandIds varchar(max),
@specIds varchar(max),
@specValues varchar(max),
@pageNo int,
@pageSize int,
@status smallint,
@search varchar(255),
@sortOrder smallint
as
/*
TODO: Modify query to use sortOrder
*/
select * into #products
from
(
    select ROW_NUMBER() OVER (order by p.sortOrder) as rowId,p.*
    from Products p left join ProductSpecifications ps on ps.productId = p.id
    where
    (@status = -1
        or (@status = -2 and (p.status = 0 or p.status = 1))
        or (p.status = @status)
    )
    and (@categoryId = -1 or p.categoryId = @categoryId)
    and (@brandIds = '' or p.brandId in (select ID from fnStringToBigIntTable(@brandIds,',')))
    and (
        @search = ''
        or p.title like '%' + @search + '%'
        or p.description like '%' + @search + '%'
        or p.detail like '%' + @search + '%'
    )
    and (@specIds = ''
        or (
            ps.specificationId in (select ID from fnStringToBigIntTable(@specIds,','))
            and ps.value in (@specValues)
        )
    )
) x
where
(rowId > @pageSize * (@pageNo - 1) and rowId <= @pageSize * @pageNo)

select * from #products
select * from Categories where id in (select categoryId from #products)
select * from Brands where id in (select brandId from #products)

select count(p.id)
from Products p left join ProductSpecifications ps on ps.productId = p.id
where 
(@status = -1
    or (@status = -2 and (p.status = 0 or p.status = 1))
    or (p.status = @status)
)
and (@categoryId = -1 or p.categoryId = @categoryId)
and (@brandIds = '' or p.brandId in (select ID from fnStringToBigIntTable(@brandIds,',')))
and (
    @search = ''
    or p.title like '%' + @search + '%'
    or p.description like '%' + @search + '%'
    or p.detail like '%' + @search + '%'
)
and (@specIds = ''
    or (
        ps.specificationId in (select ID from fnStringToBigIntTable(@specIds,','))
        and ps.value in (@specValues)
    )
)

drop table #products

My problem is the part of:

and (@specIds = ''
        or (
            ps.specificationId in (select ID from fnStringToBigIntTable(@specIds,','))
            and ps.value in (@specValues)
        )
    )

I can totally change of this part and the parameters that used in this part.

Answer 1

Firstly, I have to thank you @alex. I used table valued paramters to solve my problem.

Type:

CREATE TYPE [dbo].[specificationsFilter] AS TABLE(
    [specId] [int] NULL,
    [specValue] [varchar](50) NULL
)

Stored Procedure:

ALTER PROCEDURE [dbo].[ProductsGetAll]
@categoryId int,
@brandIds varchar(max),
@specifications specificationsFilter readonly,
@pageNo int,
@pageSize int,
@status smallint,
@search varchar(255),
@sortOrder smallint
as
declare @filterCount int
set @filterCount = (select count(distinct specId) from @specifications)
/*
ORDER BY
    TODO: Modify query to use sortOrder
*/
select * into #products
from
(
    select ROW_NUMBER() OVER (order by p.sortOrder) as rowId,p.*
    from Products p
    where
    (@status = -1
        or (@status = -2 and (p.status = 0 or p.status = 1))
        or (p.status = @status)
    )
    and (@categoryId = -1 or p.categoryId = @categoryId)
    and (@brandIds = '' or p.brandId in (select ID from fnStringToBigIntTable(@brandIds,',')))
    and (
        @search = ''
        or p.title like '%' + @search + '%'
        or p.description like '%' + @search + '%'
        or p.detail like '%' + @search + '%'
    )
    and (@filterCount = 0
        or (
            p.id in (
                select productId
                from ProductSpecifications ps, @specifications s
                where
                ps.specificationId = s.specId
                and ps.value = s.specValue
                group by productId
                having sum(1) >= @filterCount
            )
        )
    )
) x
where
(rowId > @pageSize * (@pageNo - 1) and rowId <= @pageSize * @pageNo)

select * from #products
select * from Categories where id in (select categoryId from #products)
select * from Brands where id in (select brandId from #products)

select count(p.id)
from Products p
where 
(@status = -1
    or (@status = -2 and (p.status = 0 or p.status = 1))
    or (p.status = @status)
)
and (@categoryId = -1 or p.categoryId = @categoryId)
and (@brandIds = '' or p.brandId in (select ID from fnStringToBigIntTable(@brandIds,',')))
and (
    @search = ''
    or p.title like '%' + @search + '%'
    or p.description like '%' + @search + '%'
    or p.detail like '%' + @search + '%'
)
and (@filterCount = 0
    or (
        p.id in (
            select productId
            from ProductSpecifications ps, @specifications s
            where
            ps.specificationId = s.specId
            and ps.value = s.specValue
            group by productId
            having sum(1) >= @filterCount
        )
    )
)

drop table #products

.Net Code to create Data Table paramter:

    private DataTable GetSpecificationFilter(string specificationFilter)
    {
        DataTable table = new DataTable();
        table.Columns.Add("specId", typeof(Int32));
        table.Columns.Add("specValue", typeof(string));

        string[] specifications = specificationFilter.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
        foreach(string specification in specifications)
        {
            string[] specificationParams = specification.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
            int specificationId = Convert.ToInt32(specificationParams[0]);
            string[] specificationValues = specificationParams[1].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            foreach(string value in specificationValues)
            {
                table.Rows.Add(specificationId, value);
            }
        }
        return table;
    }

And my query string structure:

?specs=1:Red,Blue;3:LED,OLED

This is a complete solution to filter product specifications in a vertical table sturcture. I used this for an e-commerce project. I hope this solution helps you for similar cases.

Answer 2

You need a way to pass in the specifications and their values. One approach is to use group by and having for the overall query:

select ps.product_id
from product_specifications ps join
     specifications s
     on ps.specification_id = s.specification_id
where (s.name = @title1 and ps.value = @value1) or
      (s.name = @title2 and ps.value = @value2)
having count(*) = 2;  -- "2" is the number of specifications you are checking

This version requires adding in the specifications and values as separate variables. There are similar approaches, where you can pass in the value using a temporary variable or values clause. It is unclear what method of passing in the values works best in your particular case.

Answer 3

Update

Table valued parameters should be used in this case. (See related)

---

older answer
_{which appears to be a variation on what was happening in op's original stored procedure.}

This is not the best approach, but this should get the job done.

CREATE PROCEDURE GetData
    @Color CHAR(2) -- "10" is only red, "01" is only green, "11" is both red and green
,   @Display CHAR(2) -- "10" is LED, "01" is OLED, "11" is both LED and OLED
AS
BEGIN
    DECLARE @Values TABLE (Value NVARCHAR(10))

    IF SUBSTRING(@Color, 1, 1) = '1'   BEGIN INSERT INTO @Values (Value) VALUES ('Red') END
    IF SUBSTRING(@Color, 2, 1) = '1'   BEGIN INSERT INTO @Values (Value) VALUES ('Green') END   
    IF SUBSTRING(@Display, 1, 1) = '1' BEGIN INSERT INTO @Values (Value) VALUES ('LED') END
    IF SUBSTRING(@Display, 2, 1) = '1' BEGIN INSERT INTO @Values (Value) VALUES ('OLED') END

    SELECT      *
    FROM        productspecifications ps
    INNER JOIN  products p
    ON          p.id = ps.productid
    INNER JOIN  specifications s
    ON          ps.specificationid = s.id
    WHERE       ps.Value IN (SELECT * FROM @Values)
END

This example is very specific to tables you provided in question.

Explanation of how it works

You pass two strings which consist of only zeros and ones (ex.: "0010110"). Your stored procedure will know to interpret 1 at index 0 in string @Color as Red and 1 at index 1 in @Color as Blue. Same thing for LED vs OLED. Your stored procedure will have many IF statements to check for every index in every string and store corresponding values in some temporary table (or temporary table variable if you there are not too many values). Then when you query your tables just put a single WHERE clause which check where value in ProductSpecifications table is present in the temporary table you just created.

How would it work

If you want (red or blue) and LED then @Color = "10" and @Display = "10".
If you want blue and OLED then @Color = "01" and @Display = "01".
If you want all then @Color = "11" and @Display = "11".

Pros

• You can achieve that (red or blue) and LED logic effect

Cons

• You have to know which index in the passed string corespondent to which value
• Logic is "leaking" from stored procedure into code (lack of encapsulation)

Conclusion

This is not a good solution. I personally don't like it, but it would get the job done. If somebody knows how to improve this that would be amazing. I would love to learn a better solution myself.
Also, it appeared to me that you have the need to pass "array" of data as parameter to stored procedure, so I think you may want to look at different ways on how to do that. The one in example I provided is one way of achieving "array passing", but there are many other and better ways.

Answer 4

I think you need FIRST a foreign key to do what you want .

You can add a field to the Products table and call it specification , this will be your foreign key .

After that to do what you want try to use a GROUP BY expression

Answer 5

I think if there is only value parameter this works, or add more search parameters u like

CREATE PROCEDURE usp_ProductSpecifications (@value)
    AS
    BEGIN
        SELECT p.id
            ,p.NAME
            ,s.etc
            ,ps.value
            ,p.etc
        FROM productspecifications ps
        INNER JOIN products p
            ON p.id = ps.productid
        INNER JOIN specifications s
            ON ps.specificationid = s.id
        WHERE ps.value = @value
    END

Answer 6

Please try below suggested solution, hope it helps!!

Create Procedure SearchByCriteria
       @Color  VARCHAR(100) = NULL,
       @Display VARCHAR(100) = NULL
       AS  
       BEGIN
        IF @Color IS NOT NULL
        SET @Color = '%' + REPLACE (@Color,',','% OR ') + '%'
       SELECT 
       fROM PRoduct p
       INNER JOIN ProductSpecification ps ON ps.ProductId = p.productID
       LEFT OUTER JOIN specification scolor ON scolor.ID = ps.SpecificationID
                         and scolor.Id = 1
       LEFT OUTER JOIN specification sDisplay ON sdisplay.ID = ps.SpecificationID
                         and sdisplay.Id = 2
       WHERE (@Color IS NULL OR  scolor.etc like @Color)
       AND (@Display IS NULL OR  Sdisplay like @Display)

       END 
       GO