It would be nice to have quantity indication in de de filtering.
You could then even leave out attribute options that do not produce a result, which is in the current filtering a little dumb.
Anybody suggestions for the queriing?
CREATE PROCEDURE [dbo].[Nop_ProductLoadAllFiltered]
(
@CategoryID int = 0,
@FilteredSpecs nvarchar(300) = null --filter by attributes (comma-separated list). e.g. 14,15,16
)
AS
BEGIN
--filter by attributes
SET @FilteredSpecs = isnull(@FilteredSpecs, '')
CREATE TABLE #FilteredSpecs
(
SpecificationAttributeOptionID int not null
)
INSERT INTO #FilteredSpecs (SpecificationAttributeOptionID)
SELECT CAST(data as int) FROM dbo.[NOP_splitstring_to_table](@FilteredSpecs, ',');
DECLARE @SpecAttributesCount int
SELECT @SpecAttributesCount = COUNT(1) FROM #FilteredSpecs
SELECT DISTINCT p.ProductID
FROM Nop_Product p with (NOLOCK)
LEFT OUTER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON p.ProductID=pcm.ProductID
LEFT OUTER JOIN Nop_Product_Manufacturer_Mapping pmm with (NOLOCK) ON p.ProductID=pmm.ProductID
LEFT OUTER JOIN Nop_ProductVariant pv with (NOLOCK) ON p.ProductID = pv.ProductID
WHERE
(
p.Published = 1
AND
pv.Published = 1
AND
p.Deleted=0
AND (
@CategoryID IS NULL OR @CategoryID=0
OR (pcm.CategoryID=@CategoryID)
)
AND
(
(getutcdate() between isnull(pv.AvailableStartDateTime, '1/1/1900') and isnull(pv.AvailableEndDateTime, '1/1/2999'))
)
AND
(
--filter by specs
@SpecAttributesCount = 0
OR
(
NOT EXISTS(
SELECT 1
FROM #FilteredSpecs [fs]
WHERE [fs].SpecificationAttributeOptionID NOT IN (
SELECT psam.SpecificationAttributeOptionID
FROM dbo.Nop_Product_SpecificationAttribute_Mapping psam
WHERE psam.AllowFiltering = 1 AND psam.ProductID = p.ProductID
)
)
)
)
)
SET ROWCOUNT 0
DROP TABLE #FilteredSpecs
END
ALTER PROCEDURE [dbo].[Nop_SpecificationAttributeOptionFilter_LoadByFilter]
(
@CategoryID int,
@FilteredSpecs nvarchar(300) = null --filter by attributes (comma-separated list). e.g. 14,15,16
)
AS
BEGIN
--display order
CREATE TABLE #ProductList
(
ProductID int not null PRIMARY KEY,
)
INSERT INTO #ProductList EXEC Nop_ProductLoadAllFiltered @CategoryID, @FilteredSpecs
SELECT
sa.SpecificationAttributeID,
sa.Name 'SpecificationAttributeName',
sa.DisplayOrder,
sao.SpecificationAttributeOptionID,
sao.Name 'SpecificationAttributeOptionName',
COUNT(p.ProductId) 'ProductCount'
FROM Nop_Product_SpecificationAttribute_Mapping psam with (NOLOCK)
INNER JOIN Nop_SpecificationAttributeOption sao with (NOLOCK) ON
sao.SpecificationAttributeOptionID = psam.SpecificationAttributeOptionID
INNER JOIN Nop_SpecificationAttribute sa with (NOLOCK) ON
sa.SpecificationAttributeID = sao.SpecificationAttributeID
INNER JOIN Nop_Product_Category_Mapping pcm with (NOLOCK) ON
pcm.ProductID = psam.ProductID
INNER JOIN Nop_Product p ON
psam.ProductID = p.ProductID
WHERE
p.Published = 1
AND
p.Deleted=0
AND
pcm.CategoryID = @CategoryID
AND
psam.AllowFiltering = 1
AND
p.ProductId in (
SELECT
pv.ProductID
FROM
Nop_ProductVariant pv
WHERE
pv.Published = 1 AND
getutcdate() between isnull(pv.AvailableStartDateTime, '1/1/1900') and isnull(pv.AvailableEndDateTime, '1/1/2999')
)
AND
p.ProductId IN (SELECT ProductID FROM #ProductList)
GROUP BY
sa.SpecificationAttributeID,
sa.Name,
sa.DisplayOrder,
sao.SpecificationAttributeOptionID,
sao.Name
ORDER BY sa.DisplayOrder, sa.Name, sao.Name
DROP TABLE #ProductList
END