Filter by attributes

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?

No suggestions, so I tried to do it myself and got it working. After all not that hard. I was a little afraid of performance, but it's performing faster than I hoped.

1) Create a slimmed down version of SP Nop_ProductLoadAll:

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

2) In SP Nop_SpecificationAttributeOptionFilter_LoadByFilter make some changes:
a) call SP Nop_ProductLoadAllFiltered from step 1 and put in temp table #ProductList
b) add COUNT(p.ProductId) to select list
c) add extra WHERE clause: p.ProductId IN (SELECT ProductID FROM #ProductList)

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

3) Modify BusinessLogic and DataAccess to accomodate for the extra field (count).

I hope to have my project online within next weeks.
Best regards,
Rinusripsus