Price filter for grouped products is not working correctly

Hi

nopCommerce 3.10

If you set a price filter range for the sample product Canon Digital camera(grouped product with item cost 630$ and 670$) and set the price range to -640;640-; the product will not be shown if you filter under $640 eventhough one item cost 630$ only filtering over 640$ will show the product.

Hi Tommy,

Thanks. This is known issue without a good solution yet (more info here).

P.S. Currently you have to set a price for a parent "grouped" product. It'll be used for filtering. But it's more like a temporary "hack"

Here's a solution for NopCommerce 3.1 where Stored Procedures are used (I didn't try tackling the LINQ version as we had no need for it)

The stored procedure ProductLoadAllPaged needs to be changed so that instead of :

  --min price
  IF @PriceMin > 0
  BEGIN
    SET @sql = @sql + '
    AND (
        (
          --special price (specified price and valid date range)
          (p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.SpecialPrice >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
        )
        OR 
        (
          --regular price (price isnt specified or date range isnt valid)
          (p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
        )
      )'
  END
  
  --max price
  IF @PriceMax > 0
  BEGIN
    SET @sql = @sql + '
    AND (
        (
          --special price (specified price and valid date range)
          (p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.SpecialPrice <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
        )
        OR 
        (
          --regular price (price isnt specified or date range isnt valid)
          (p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
        )
      )'
  END

you have :

  --any sort of price (header)
  IF @PriceMin > 0 OR @PriceMax > 0
  BEGIN
    SET @sql = @sql + '
    AND
    ( /* ProductTypeId 5 or 10 */
    (
    p.ProductTypeId = 5 /* ProductType.SimpleProduct */'
  END
  
  --min price
  IF @PriceMin > 0
  BEGIN
    SET @sql = @sql + '
    AND (
        (
          --special price (specified price and valid date range)
          (p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.SpecialPrice >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
        )
        OR 
        (
          --regular price (price isnt specified or date range isnt valid)
          (p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
        )
      )'
  END
  
  --max price
  IF @PriceMax > 0
  BEGIN
    SET @sql = @sql + '
    AND (
        (
          --special price (specified price and valid date range)
          (p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.SpecialPrice <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
        )
        OR 
        (
          --regular price (price isnt specified or date range isnt valid)
          (p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
        )
      )'
  END

  --any sort of price (close simple product search, start grouped product search)
  IF @PriceMin > 0 OR @PriceMax > 0
  BEGIN
    SET @sql = @sql + '
    )
    OR
    (
      p.ProductTypeId = 10 /* ProductType.GroupedProduct */
      AND EXISTS
      (
        SELECT 1 FROM [Product] [SimpleProductInGroup]
        WHERE ProductTypeId = 5 /* ProductType.SimpleProduct */
        AND SimpleProductInGroup.ParentGroupedProductId = p.Id'
  END
  
  --min price
  IF @PriceMin > 0
  BEGIN
    SET @sql = @sql + '
    AND (
        (
          --special price (specified price and valid date range)
          (SimpleProductInGroup.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(SimpleProductInGroup.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(SimpleProductInGroup.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (SimpleProductInGroup.SpecialPrice >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
        )
        OR 
        (
          --regular price (price isnt specified or date range isnt valid)
          (SimpleProductInGroup.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(SimpleProductInGroup.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(SimpleProductInGroup.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (SimpleProductInGroup.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
        )
      )'
  END
  
  --max price
  IF @PriceMax > 0
  BEGIN
    SET @sql = @sql + '
    AND (
        (
          --special price (specified price and valid date range)
          (SimpleProductInGroup.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(SimpleProductInGroup.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(SimpleProductInGroup.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (SimpleProductInGroup.SpecialPrice <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
        )
        OR 
        (
          --regular price (price isnt specified or date range isnt valid)
          (SimpleProductInGroup.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(SimpleProductInGroup.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(SimpleProductInGroup.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (SimpleProductInGroup.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
        )
      )'
  END
  
  --any sort of price (footer)
  IF @PriceMin > 0 OR @PriceMax > 0
  BEGIN
    SET @sql = @sql + '
      )
    ) /* ProductTypeId 5 or 10 */
    )'
  END  

Basically it says that if the product is a grouped one, ignore its price but show the product if it has any associated products whose prices are in the requested range.

Here's the whole stored procedure, just run this script as-is in the right database to make the change:

[code]
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ProductLoadAllPaged]
(
  @CategoryIds    nvarchar(MAX) = null,  --a list of category IDs (comma-separated list). e.g. 1,2,3
  @ManufacturerId    int = 0,
  @StoreId      int = 0,
  @VendorId      int = 0,
  @ParentGroupedProductId  int = 0,
  @ProductTypeId    int = null, --product type identifier, null - load all products
  @VisibleIndividuallyOnly bit = 0,   --0 - load all products , 1 - "visible indivially" only
  @ProductTagId    int = 0,
  @FeaturedProducts  bit = null,  --0 featured only , 1 not featured only, null - load all products
  @PriceMin      decimal(18, 4) = null,
  @PriceMax      decimal(18, 4) = null,
  @Keywords      nvarchar(4000) = null,
  @SearchDescriptions bit = 0, --a value indicating whether to search by a specified "keyword" in product descriptions
  @SearchSku      bit = 0, --a value indicating whether to search by a specified "keyword" in product SKU
  @SearchProductTags  bit = 0, --a value indicating whether to search by a specified "keyword" in product tags
  @UseFullTextSearch  bit = 0,
  @FullTextMode    int = 0, --0 - using CONTAINS with <prefix_term>, 5 - using CONTAINS and OR with <prefix_term>, 10 - using CONTAINS and AND with <prefix_term>
  @FilteredSpecs    nvarchar(MAX) = null,  --filter by attributes (comma-separated list). e.g. 14,15,16
  @LanguageId      int = 0,
  @OrderBy      int = 0, --0 - position, 5 - Name: A to Z, 6 - Name: Z to A, 10 - Price: Low to High, 11 - Price: High to Low, 15 - creation date
  @AllowedCustomerRoleIds  nvarchar(MAX) = null,  --a list of customer role IDs (comma-separated list) for which a product should be shown (if a subjet to ACL)
  @PageIndex      int = 0,
  @PageSize      int = 2147483644,
  @ShowHidden      bit = 0,
  @LoadFilterableSpecificationAttributeOptionIds bit = 0, --a value indicating whether we should load the specification attribute option identifiers applied to loaded products (all pages)
  @FilterableSpecificationAttributeOptionIds nvarchar(MAX) = null OUTPUT, --the specification attribute option identifiers applied to loaded products (all pages). returned as a comma separated list of identifiers
  @TotalRecords    int = null OUTPUT
)
AS
BEGIN
  
  /* Products that filtered by keywords */
  CREATE TABLE #KeywordProducts
  (
    [ProductId] int NOT NULL
  )

  DECLARE
    @SearchKeywords bit,
    @sql nvarchar(max),
    @sql_orderby nvarchar(max)

  SET NOCOUNT ON
  
  --filter by keywords
  SET @Keywords = isnull(@Keywords, '')
  SET @Keywords = rtrim(ltrim(@Keywords))
  IF ISNULL(@Keywords, '') != ''
  BEGIN
    SET @SearchKeywords = 1
    
    IF @UseFullTextSearch = 1
    BEGIN
      --remove wrong chars (' ")
      SET @Keywords = REPLACE(@Keywords, '''', '')
      SET @Keywords = REPLACE(@Keywords, '"', '')
      
      --full-text search
      IF @FullTextMode = 0
      BEGIN
        --0 - using CONTAINS with <prefix_term>
        SET @Keywords = ' "' + @Keywords + '*" '
      END
      ELSE
      BEGIN
        --5 - using CONTAINS and OR with <prefix_term>
        --10 - using CONTAINS and AND with <prefix_term>

        --clean multiple spaces
        WHILE CHARINDEX('  ', @Keywords) > 0
          SET @Keywords = REPLACE(@Keywords, '  ', ' ')

        DECLARE @concat_term nvarchar(100)        
        IF @FullTextMode = 5 --5 - using CONTAINS and OR with <prefix_term>
        BEGIN
          SET @concat_term = 'OR'
        END
        IF @FullTextMode = 10 --10 - using CONTAINS and AND with <prefix_term>
        BEGIN
          SET @concat_term = 'AND'
        END

        --now let's build search string
        declare @fulltext_keywords nvarchar(4000)
        set @fulltext_keywords = N''
        declare @index int    
    
        set @index = CHARINDEX(' ', @Keywords, 0)

        -- if index = 0, then only one field was passed
        IF(@index = 0)
          set @fulltext_keywords = ' "' + @Keywords + '*" '
        ELSE
        BEGIN    
          DECLARE @first BIT
          SET  @first = 1      
          WHILE @index > 0
          BEGIN
            IF (@first = 0)
              SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' '
            ELSE
              SET @first = 0

            SET @fulltext_keywords = @fulltext_keywords + '"' + SUBSTRING(@Keywords, 1, @index - 1) + '*"'          
            SET @Keywords = SUBSTRING(@Keywords, @index + 1, LEN(@Keywords) - @index)            
            SET @index = CHARINDEX(' ', @Keywords, 0)
          end
          
          -- add the last field
          IF LEN(@fulltext_keywords) > 0
            SET @fulltext_keywords = @fulltext_keywords + ' ' + @concat_term + ' ' + '"' + SUBSTRING(@Keywords, 1, LEN(@Keywords)) + '*"'  
        END
        SET @Keywords = @fulltext_keywords
      END
    END
    ELSE
    BEGIN
      --usual search by PATINDEX
      SET @Keywords = '%' + @Keywords + '%'
    END
    --PRINT @Keywords

    --product name
    SET @sql = '
    INSERT INTO #KeywordProducts ([ProductId])
    SELECT p.Id
    FROM Product p with (NOLOCK)
    WHERE '
    IF @UseFullTextSearch = 1
      SET @sql = @sql + 'CONTAINS(p.[Name], @Keywords) '
    ELSE
      SET @sql = @sql + 'PATINDEX(@Keywords, p.[Name]) > 0 '


    --localized product name
    SET @sql = @sql + '
    UNION
    SELECT lp.EntityId
    FROM LocalizedProperty lp with (NOLOCK)
    WHERE
      lp.LocaleKeyGroup = N''Product''
      AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
      AND lp.LocaleKey = N''Name'''
    IF @UseFullTextSearch = 1
      SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
    ELSE
      SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
  

    IF @SearchDescriptions = 1
    BEGIN
      --product short description
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(p.[ShortDescription], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, p.[ShortDescription]) > 0 '


      --product full description
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(p.[FullDescription], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, p.[FullDescription]) > 0 '



      --localized product short description
      SET @sql = @sql + '
      UNION
      SELECT lp.EntityId
      FROM LocalizedProperty lp with (NOLOCK)
      WHERE
        lp.LocaleKeyGroup = N''Product''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''ShortDescription'''
      IF @UseFullTextSearch = 1
        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
      ELSE
        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
        

      --localized product full description
      SET @sql = @sql + '
      UNION
      SELECT lp.EntityId
      FROM LocalizedProperty lp with (NOLOCK)
      WHERE
        lp.LocaleKeyGroup = N''Product''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''FullDescription'''
      IF @UseFullTextSearch = 1
        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
      ELSE
        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
    END

    --SKU
    IF @SearchSku = 1
    BEGIN
      SET @sql = @sql + '
      UNION
      SELECT p.Id
      FROM Product p with (NOLOCK)
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(p.[Sku], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, p.[Sku]) > 0 '
    END

    IF @SearchProductTags = 1
    BEGIN
      --product tag
      SET @sql = @sql + '
      UNION
      SELECT pptm.Product_Id
      FROM Product_ProductTag_Mapping pptm with(NOLOCK) INNER JOIN ProductTag pt with(NOLOCK) ON pt.Id = pptm.ProductTag_Id
      WHERE '
      IF @UseFullTextSearch = 1
        SET @sql = @sql + 'CONTAINS(pt.[Name], @Keywords) '
      ELSE
        SET @sql = @sql + 'PATINDEX(@Keywords, pt.[Name]) > 0 '

      --localized product tag
      SET @sql = @sql + '
      UNION
      SELECT pptm.Product_Id
      FROM LocalizedProperty lp with (NOLOCK) INNER JOIN Product_ProductTag_Mapping pptm with(NOLOCK) ON lp.EntityId = pptm.ProductTag_Id
      WHERE
        lp.LocaleKeyGroup = N''ProductTag''
        AND lp.LanguageId = ' + ISNULL(CAST(@LanguageId AS nvarchar(max)), '0') + '
        AND lp.LocaleKey = N''Name'''
      IF @UseFullTextSearch = 1
        SET @sql = @sql + ' AND CONTAINS(lp.[LocaleValue], @Keywords) '
      ELSE
        SET @sql = @sql + ' AND PATINDEX(@Keywords, lp.[LocaleValue]) > 0 '
    END

    --PRINT (@sql)
    EXEC sp_executesql @sql, N'@Keywords nvarchar(4000)', @Keywords

  END
  ELSE
  BEGIN
    SET @SearchKeywords = 0
  END

  --filter by category IDs
  SET @CategoryIds = isnull(@CategoryIds, '')  
  CREATE TABLE #FilteredCategoryIds
  (
    CategoryId int not null
  )
  INSERT INTO #FilteredCategoryIds (CategoryId)
  SELECT CAST(data as int) FROM [nop_splitstring_to_table](@CategoryIds, ',')  
  DECLARE @CategoryIdsCount int  
  SET @CategoryIdsCount = (SELECT COUNT(1) FROM #FilteredCategoryIds)

  --filter by attributes
  SET @FilteredSpecs = isnull(@FilteredSpecs, '')  
  CREATE TABLE #FilteredSpecs
  (
    SpecificationAttributeOptionId int not null
  )
  INSERT INTO #FilteredSpecs (SpecificationAttributeOptionId)
  SELECT CAST(data as int) FROM [nop_splitstring_to_table](@FilteredSpecs, ',')
  DECLARE @SpecAttributesCount int  
  SET @SpecAttributesCount = (SELECT COUNT(1) FROM #FilteredSpecs)

  --filter by customer role IDs (access control list)
  SET @AllowedCustomerRoleIds = isnull(@AllowedCustomerRoleIds, '')  
  CREATE TABLE #FilteredCustomerRoleIds
  (
    CustomerRoleId int not null
  )
  INSERT INTO #FilteredCustomerRoleIds (CustomerRoleId)
  SELECT CAST(data as int) FROM [nop_splitstring_to_table](@AllowedCustomerRoleIds, ',')
  
  --paging
  DECLARE @PageLowerBound int
  DECLARE @PageUpperBound int
  DECLARE @RowsToReturn int
  SET @RowsToReturn = @PageSize * (@PageIndex + 1)  
  SET @PageLowerBound = @PageSize * @PageIndex
  SET @PageUpperBound = @PageLowerBound + @PageSize + 1
  
  CREATE TABLE #DisplayOrderTmp
  (
    [Id] int IDENTITY (1, 1) NOT NULL,
    [ProductId] int NOT NULL
  )

  SET @sql = '
  INSERT INTO #DisplayOrderTmp ([ProductId])
  SELECT p.Id
  FROM
    Product p with (NOLOCK)'
  
  IF @CategoryIdsCount > 0
  BEGIN
    SET @sql = @sql + '
    LEFT JOIN Product_Category_Mapping pcm with (NOLOCK)
      ON p.Id = pcm.ProductId'
  END
  
  IF @ManufacturerId > 0
  BEGIN
    SET @sql = @sql + '
    LEFT JOIN Product_Manufacturer_Mapping pmm with (NOLOCK)
      ON p.Id = pmm.ProductId'
  END
  
  IF ISNULL(@ProductTagId, 0) != 0
  BEGIN
    SET @sql = @sql + '
    LEFT JOIN Product_ProductTag_Mapping pptm with (NOLOCK)
      ON p.Id = pptm.Product_Id'
  END
  
  --searching by keywords
  IF @SearchKeywords = 1
  BEGIN
    SET @sql = @sql + '
    JOIN #KeywordProducts kp
      ON  p.Id = kp.ProductId'
  END
  
  SET @sql = @sql + '
  WHERE
    p.Deleted = 0'
  
  --filter by category
  IF @CategoryIdsCount > 0
  BEGIN
    SET @sql = @sql + '
    AND pcm.CategoryId IN (SELECT CategoryId FROM #FilteredCategoryIds)'
    
    IF @FeaturedProducts IS NOT NULL
    BEGIN
      SET @sql = @sql + '
    AND pcm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
    END
  END
  
  --filter by manufacturer
  IF @ManufacturerId > 0
  BEGIN
    SET @sql = @sql + '
    AND pmm.ManufacturerId = ' + CAST(@ManufacturerId AS nvarchar(max))
    
    IF @FeaturedProducts IS NOT NULL
    BEGIN
      SET @sql = @sql + '
    AND pmm.IsFeaturedProduct = ' + CAST(@FeaturedProducts AS nvarchar(max))
    END
  END
  
  --filter by vendor
  IF @VendorId > 0
  BEGIN
    SET @sql = @sql + '
    AND p.VendorId = ' + CAST(@VendorId AS nvarchar(max))
  END
  
  --filter by parent grouped product identifer
  IF @ParentGroupedProductId > 0
  BEGIN
    SET @sql = @sql + '
    AND p.ParentGroupedProductId = ' + CAST(@ParentGroupedProductId AS nvarchar(max))
  END
  
  --filter by product type
  IF @ProductTypeId is not null
  BEGIN
    SET @sql = @sql + '
    AND p.ProductTypeId = ' + CAST(@ProductTypeId AS nvarchar(max))
  END
  
  --filter by parent product identifer
  IF @VisibleIndividuallyOnly = 1
  BEGIN
    SET @sql = @sql + '
    AND p.VisibleIndividually = 1'
  END
  
  --filter by product tag
  IF ISNULL(@ProductTagId, 0) != 0
  BEGIN
    SET @sql = @sql + '
    AND pptm.ProductTag_Id = ' + CAST(@ProductTagId AS nvarchar(max))
  END
  
  --show hidden
  IF @ShowHidden = 0
  BEGIN
    SET @sql = @sql + '
    AND p.Published = 1
    AND p.Deleted = 0
    AND (getutcdate() BETWEEN ISNULL(p.AvailableStartDateTimeUtc, ''1/1/1900'') and ISNULL(p.AvailableEndDateTimeUtc, ''1/1/2999''))'
  END

  --any sort of price (header)
  IF @PriceMin > 0 OR @PriceMax > 0
  BEGIN
    SET @sql = @sql + '
    AND
    ( /* ProductTypeId 5 or 10 */
    (
    p.ProductTypeId = 5 /* ProductType.SimpleProduct */'
  END
  
  --min price
  IF @PriceMin > 0
  BEGIN
    SET @sql = @sql + '
    AND (
        (
          --special price (specified price and valid date range)
          (p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.SpecialPrice >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
        )
        OR
        (
          --regular price (price isnt specified or date range isnt valid)
          (p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
        )
      )'
  END
  
  --max price
  IF @PriceMax > 0
  BEGIN
    SET @sql = @sql + '
    AND (
        (
          --special price (specified price and valid date range)
          (p.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.SpecialPrice <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
        )
        OR
        (
          --regular price (price isnt specified or date range isnt valid)
          (p.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(p.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(p.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (p.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
        )
      )'
  END

  --any sort of price (close simple product search, start grouped product search)
  IF @PriceMin > 0 OR @PriceMax > 0
  BEGIN
    SET @sql = @sql + '
    )
    OR
    (
      p.ProductTypeId = 10 /* ProductType.GroupedProduct */
      AND EXISTS
      (
        SELECT 1 FROM [Product] [SimpleProductInGroup]
        WHERE ProductTypeId = 5 /* ProductType.SimpleProduct */
        AND SimpleProductInGroup.ParentGroupedProductId = p.Id'
  END
  
  --min price
  IF @PriceMin > 0
  BEGIN
    SET @sql = @sql + '
    AND (
        (
          --special price (specified price and valid date range)
          (SimpleProductInGroup.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(SimpleProductInGroup.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(SimpleProductInGroup.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (SimpleProductInGroup.SpecialPrice >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
        )
        OR
        (
          --regular price (price isnt specified or date range isnt valid)
          (SimpleProductInGroup.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(SimpleProductInGroup.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(SimpleProductInGroup.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (SimpleProductInGroup.Price >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
        )
      )'
  END
  
  --max price
  IF @PriceMax > 0
  BEGIN
    SET @sql = @sql + '
    AND (
        (
          --special price (specified price and valid date range)
          (SimpleProductInGroup.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(SimpleProductInGroup.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(SimpleProductInGroup.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (SimpleProductInGroup.SpecialPrice <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
        )
        OR
        (
          --regular price (price isnt specified or date range isnt valid)
          (SimpleProductInGroup.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(SimpleProductInGroup.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(SimpleProductInGroup.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
          AND
          (SimpleProductInGroup.Price <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
        )
      )'
  END
  
  --any sort of price (footer)
  IF @PriceMin > 0 OR @PriceMax > 0
  BEGIN
    SET @sql = @sql + '
      )
    ) /* ProductTypeId 5 or 10 */
    )'
  END  
    
  --show hidden and ACL
  IF @ShowHidden = 0
  BEGIN
    SET @sql = @sql + '
    AND (p.SubjectToAcl = 0 OR EXISTS (
      SELECT 1 FROM #FilteredCustomerRoleIds [fcr]
      WHERE
        [fcr].CustomerRoleId IN (
          SELECT [acl].CustomerRoleId
          FROM [AclRecord] acl with (NOLOCK)
          WHERE [acl].EntityId = p.Id AND [acl].EntityName = ''Product''
        )
      ))'
  END
  
  --show hidden and filter by store
  IF @StoreId > 0
  BEGIN
    SET @sql = @sql + '
    AND (p.LimitedToStores = 0 OR EXISTS (
      SELECT 1 FROM [StoreMapping] sm with (NOLOCK)
      WHERE [sm].EntityId = p.Id AND [sm].EntityName = ''Product'' and [sm].StoreId=' + CAST(@StoreId AS nvarchar(max)) + '
      ))'
  END
  
  --filter by specs
  IF @SpecAttributesCount > 0
  BEGIN
    SET @sql = @sql + '
    AND NOT EXISTS (
      SELECT 1 FROM #FilteredSpecs [fs]
      WHERE
        [fs].SpecificationAttributeOptionId NOT IN (
          SELECT psam.SpecificationAttributeOptionId
          FROM Product_SpecificationAttribute_Mapping psam with (NOLOCK)
          WHERE psam.AllowFiltering = 1 AND psam.ProductId = p.Id
        )
      )'
  END
  
  --sorting
  SET @sql_orderby = ''  
  IF @OrderBy = 5 /* Name: A to Z */
    SET @sql_orderby = ' p.[Name] ASC'
  ELSE IF @OrderBy = 6 /* Name: Z to A */
    SET @sql_orderby = ' p.[Name] DESC'
  ELSE IF @OrderBy = 10 /* Price: Low to High */
    SET @sql_orderby = ' p.[Price] ASC'
  ELSE IF @OrderBy = 11 /* Price: High to Low */
    SET @sql_orderby = ' p.[Price] DESC'
  ELSE IF @OrderBy = 15 /* creation date */
    SET @sql_orderby = ' p.[CreatedOnUtc] DESC'
  ELSE /* default sorting, 0 (position) */
  BEGIN
    --category position (display order)
    IF @CategoryIdsCount > 0 SET @sql_orderby = ' pcm.DisplayOrder ASC'
    
    --manufacturer position (display order)
    IF @ManufacturerId > 0
    BEGIN
      IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
      SET @sql_orderby = @sql_orderby + ' pmm.DisplayOrder ASC'
    END
    
    --parent grouped product specified (sort associated products)
    IF @ParentGroupedProductId > 0
    BEGIN
      IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
      SET @sql_orderby = @sql_orderby + ' p.[DisplayOrder] ASC'
    END
    
    --name
    IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
    SE

Thanks for the fix ylrsi.

Is there any word on getting work item : "Filtering products associated to a parent "grouped" product' ?

Doesnt really make sense that no filters show up for Grouped Products. Are there any work-arounds we can add in?

thanks

patmancini, I'm not quite sure what you're asking.

You asked,
> Is there any word on getting work item : "Filtering products associated to a parent "grouped" product' ?
> Doesnt really make sense that no filters show up for Grouped Products. Are there any work-arounds we can add in?

Do you mean that when you're inside a grouped product page you want to be able to filter the related simple products?

I think that as this thread relates to price filtering in particular, you'd do better creating a new thread with an appropriate title and asking your question a little more clearly (give an example).

Yes in the second response of this thread Andrei mentions the work item - https://nopcommerce.codeplex.com/workitem/11599 which is "Filtering products associated to a parent "grouped" product" and I wanted to know if there was any progress.

Have you managed this?

hi ,

i am working on filters  previously i was set sort by "low to high" and it was work fine but now i have add plugin add i have done setting for product .
now i am not able to set default sort by low to high

can you help me

thanks

Hi,

I've solved it in version 4.50.

In the ProductService class SearchProductsAsync action I've added the following checks.

            if (priceMin.HasValue && priceMax.HasValue)
            {
                var products = productsQuery.AsEnumerable();

                if (priceMin > 0 && priceMax > 0)
                    productsQuery = _productRepository.Table.Where(p => products.Any(x => x.ParentGroupedProductId > 0 ? x.ParentGroupedProductId == p.Id : x.Id == p.Id));
                else
                {
                    var simpleProductsIds = products.Where(x => x.ProductTypeId == (int)ProductType.SimpleProduct && x.ParentGroupedProductId == 0).Select(x => x.Id);
                    var groupedProductIds = products.Where(x => x.ProductTypeId == (int)ProductType.GroupedProduct).Select(x => x.Id);
                    var parentGroupedProductIds = _productRepository.Table
                        .Where(p => groupedProductIds.Any(x => x == p.ParentGroupedProductId) && p.Price <= 0 && !p.Deleted)
                        .Select(x => x.ParentGroupedProductId)
                        .AsEnumerable();

                    var totalProductsFreeIds = new List<int>();
                    totalProductsFreeIds.AddRange(simpleProductsIds);
                    totalProductsFreeIds.AddRange(parentGroupedProductIds);

                    productsQuery = productsQuery.Where(x => totalProductsFreeIds.Any(y => y == x.Id));
                }
            }

It should be added right after the query:

            visibleIndividuallyOnly = !priceMin.HasValue && !priceMax.HasValue;
             productsQuery = 
                from p in productsQuery
                where !p.Deleted &&
                    (!visibleIndividuallyOnly || p.VisibleIndividually) &&
                    (vendorId == 0 || p.VendorId == vendorId) &&
                    (
                        warehouseId == 0 ||
                        (
                            !p.UseMultipleWarehouses ? p.WarehouseId == warehouseId :
                                _productWarehouseInventoryRepository.Table.Any(pwi => pwi.WarehouseId == warehouseId && pwi.ProductId == p.Id)
                        )
                    ) &&
                    (productType == null || p.ProductTypeId == (int)productType) &&
                    (showHidden ||
                            DateTime.UtcNow >= (p.AvailableStartDateTimeUtc ?? DateTime.MinValue) &&
                            DateTime.UtcNow <= (p.AvailableEndDateTimeUtc ?? DateTime.MaxValue)
                    ) &&
                    (priceMin == null || p.Price >= priceMin) &&
                    (priceMax == null || p.Price <= priceMax)
                select p;

In this way, when filtering by price it shows the products grouped based on the price of their children, and also when wanting to obtain the products with price "0".

I know it's not the best solution, but it works.

Regards!

Hi,

I've changed the logic commented in the last post, because it did not work correctly.

First thing is to move the spec attribute check below productsQuery and remove some conditions from linq Where:

productsQuery = productsQuery.Where(p => !p.Deleted &&
                (vendorId == 0 || p.VendorId == vendorId) &&
                (showHidden ||
                    DateTime.UtcNow >= (p.AvailableStartDateTimeUtc ?? DateTime.MinValue) &&
                    DateTime.UtcNow <= (p.AvailableEndDateTimeUtc ?? DateTime.MaxValue)))
                .Select(x => x);

            if (filteredSpecOptions?.Count > 0)
            {
                var specificationAttributeIds = filteredSpecOptions
                    .Select(sao => sao.SpecificationAttributeId)
                    .Distinct();

                foreach (var specificationAttributeId in specificationAttributeIds)
                {
                    var optionIdsBySpecificationAttribute = filteredSpecOptions
                        .Where(o => o.SpecificationAttributeId == specificationAttributeId)
                        .Select(o => o.Id);

                    var productSpecificationQuery =
                        from psa in _productSpecificationAttributeRepository.Table
                        where psa.AllowFiltering && optionIdsBySpecificationAttribute.Contains(psa.SpecificationAttributeOptionId)
                        select psa;

                    productsQuery =
                        from p in productsQuery
                        where productSpecificationQuery.Any(pc => pc.ProductId == p.Id)
                        select p;
                }
            }

Now, we add the following code segment:

            if (priceMin.HasValue || priceMax.HasValue)
            {
                List<int> productsFiltered = new();
                
                productsFiltered.AddRange(productsQuery
                    .Where(x => x.ProductTypeId == (int)ProductType.SimpleProduct && x.ParentGroupedProductId == 0 &&
                        (priceMin == null || x.Price >= priceMin) &&
                        (priceMax == null || x.Price <= priceMax))
                    .Select(x => x.Id));

                var parentsGroupedIds = productsQuery
                    .Where(x => x.ProductTypeId == (int)ProductType.GroupedProduct && x.ParentGroupedProductId == 0)
                    .Select(x => x.Id);

                var childsGroupedParentId = productsQuery
                    .Where(x => x.ProductTypeId == (int)ProductType.SimpleProduct && x.ParentGroupedProductId > 0 &&
                        (priceMin == null || x.Price >= priceMin) &&
                        (priceMax == null || x.Price <= priceMax))
                    .Select(x => x.ParentGroupedProductId);

                productsFiltered.AddRange(parentsGroupedIds.Where(x => childsGroupedParentId.Any(y => y == x)));

                productsQuery = productsQuery
                    .Where(x => productsFiltered.Any(y => y == x.Id));
            }

            productsQuery = productsQuery.Where(x => (!visibleIndividuallyOnly || x.VisibleIndividually) &&
            (productType == null || x.ProductTypeId == (int)productType));

            if (warehouseId > 0)
                productsQuery = productsQuery.Where(x => warehouseId == 0 || !x.UseMultipleWarehouses ?
                x.WarehouseId == warehouseId :
                _productWarehouseInventoryRepository.Table.Any(pwi => pwi.WarehouseId == warehouseId && pwi.ProductId == x.Id));