T-SQL query for vertical table structure
I am working on an e-commerce project. Now I need to create a filter for the product detail 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
E-commerce users should be able to filter multiple parameters at the same time. I mean, the user might want to search for "(red or blue) and OLED TVs."
I tried something, but I couldn't write the correct stored procedure. I guess I'm stuck here and I need help.
EDIT:
After some answers, I need to update more information here.
The characteristics are dynamic. Therefore, filters are also dynamic. I am generating filters using a bit of a column named allowFilter. So I cannot use strongly typed parameters like @color
or@display
Users can choose not to use the filter. Or they can use one or more filters. You can find the query 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 this:
and (@specIds = ''
or (
ps.specificationId in (select ID from fnStringToBigIntTable(@specIds,','))
and ps.value in (@specValues)
)
)
I can completely change this part and the parameters used in this part.
source to share
First, I have to thank you @alex. I used table parameters to solve my problem.
A 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 parameter:
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
It is a complete solution for filtering product characteristics in a vertical canteen structure. I used this for an e-commerce project. Hope this solution helps you in cases like this.
source to share
You need a way to pass specifications and their values. One approach is to use group by
and having
for a general 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 the addition of specifications and values โโas separate variables. There are similar approaches where you can pass a value using a temporary variable or values
. It is not clear which method of passing values โโis best for your particular case.
source to share
Update
Table values should be used in this case. (Cm. )
senior answer
which appears to be a variation of what was going on in the original stored procedure op.
This is not the best approach, but it 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 the tables you mentioned.
Explanation of how it works
You are passing two strings of only zeros and ones (for example: "0010110"). Your stored procedure will know to interpret 1 at index 0 in the string @Color
as Red
and 1 at index 1 @Color
as Blue
. The same goes for LEDs and OLEDs. Your stored procedure will have many IF statements to check each index on each row and store the corresponding values โโin some temporary table (or temporary table variable if you don't have too many values). Then, when you query your tables, just put one clause WHERE
that checks where the value in the ProductSpecifications table is present in the temporary table you just created.
How it works
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 everything, then @Color = "11"
and @Display = "11"
.
Pros
- You can achieve this
(red or blue) and LED
boolean effect
Against
- You should know which index in the passed root column corresponds to the value
- Logic "leaking" from stored procedure to code (no encapsulation)
Conclusion
This is not a good decision. I personally don't like this, but it will be done. If anyone knows how to improve this, that would be awesome. I would like to know the best solution myself.
Also, it seemed to me that you need to pass an "array" of data as a parameter to the stored procedure, so I think that you may need to look at different ways to do this. One example I have provided is one way to achieve "array passing", but there are many other and better ways.
source to share
I think if there is only a value parameter this works or adds 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
source to share
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
source to share