Improved keyword searching in NopCommerce

27. October 2014 14:15 by John Dillon | 0 Comments

27. October 2014 14:15 by John Dillon, 0 Comments

Keyword searching in NopCommerce by default didn’t seem to work as well as we would have expected. Our main issues were:

  1. It doesn’t handle plurals (e.g. searching for "dinosaurs" doesn’t find anything with "dinosaur" in it)
  2. Results aren’t ranked in order of relevance

We’ve made some relatively simple modifications which in our opinion make it work much more effectively. All the necessary code is in the stored procedure ProductLoadAllPaged.

1. Ranking results

SQL fulltextsearch has a Rank feature which creates a score for a match, enabling you to order results by this value. The higher the rank the more relevant it is (in theory). Its a complicated algorithm which I wont... OK cant explain, but the point is it seems to work pretty well.

In order for this to work you need to use CONTAINSTABLE rather than CONTAINS as NopCommerce does by default. I expect the reason it uses CONTAINS is that CONTAINSTABLE can only be used on single tables, you cant as easily join queries to search on multiple tables. Because NopCommerce is multi lingual, it needs to also search on the LocalizedProperty table for multi language variations. We don’t need this in our current setup so for us it was simple enough, just remove this lookup. For those of you who do need multiple joins, this is possible using a schema bound indexed view with a fulltextindex created on that. This can be a bit fiddly to setup, and its slightly annoying moving forward because if you ever make any schema changes to the tables involved you need to recreate the index each time.... however it does work.

All you then need to do is add in the fields you want to search on and it should work just fine.

One other point on our code is you'll notice we've not included the default NOP logic to build the fields to search on based on variables like "IF @SearchDescriptions = 1". This is simply because we don’t need this functionality to be CMS driven, we're happy to hard code this. It should be easy enough to modify the code should you need this.

2. Plurals

In order to handle plurals you need to use for example FORMSOF (INFLECTIONAL, "dinosaurs").

One additional requirement we had was for it to also match on partial strings, so for example if I enter "dino" it needs to find "dinosaurs". This is because we use an auto complete search in the header.
What this meant was we needed to build up 2 separate strings to search on, and use an OR operator to split these:
i.e.
1. FORMSOF (INFLECTIONAL, action) AND FORMSOF (INFLECTIONAL, dino)
2. "dino*" AND "action*"
Resulting in (FORMSOF (INFLECTIONAL, action) AND FORMSOF (INFLECTIONAL, dino)) OR ("dino*" AND "action*")

Because we use the rank feature to order results it naturally means the more relevant results come first.

You can see the resulting code in action here: http://www.mulberrybush.co.uk. The client certainly considered it to be an improvement.

I’ve included the code below in case it’s of use to anyone else.



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,
    @WarehouseId        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,
        Rank int
    )

    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 @fulltext_inflex nvarchar(4000)
                set @fulltext_inflex = N''
                declare @index int       
       
                set @index = CHARINDEX(' ', @Keywords, 0)

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

                        SET @fulltext_keywords = @fulltext_keywords + '"' + SUBSTRING(@Keywords, 1, @index - 1) + '*"'
                        set @fulltext_inflex = @fulltext_inflex + 'FORMSOF (INFLECTIONAL, "' + 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)) + '*"'
                    IF LEN(@fulltext_inflex) > 0
                        SET @fulltext_inflex = @fulltext_inflex + ' ' + @concat_term + ' ' + 'FORMSOF (INFLECTIONAL, "' + SUBSTRING(@Keywords, 1, LEN(@Keywords)) + '")'   
                END
                --PRINT @fulltext_keywords
                --PRINT @fulltext_inflex
                -- NOTE we dont use @concat_term here because it means we cannot get partial matching from either /or option
                SET @Keywords = '(' + @fulltext_inflex + ') OR (' + @fulltext_keywords + ')'
            END
        END
        ELSE
        BEGIN
            --usual search by PATINDEX
            SET @Keywords = '%' + @Keywords + '%'
        END
        --PRINT @Keywords

        --product name
        SET @sql = '
        INSERT INTO #KeywordProducts ([ProductId],Rank)       
        SELECT p.Id, Rank
        FROM Product p with (NOLOCK)
        INNER JOIN CONTAINSTABLE(Product, (Name,Sku,ShortDescription,FullDescription), @Keywords) As Ftt ON Ftt.[KEY]=p.id
        '

        ----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 warehouse
    IF @WarehouseId > 0
    BEGIN
        SET @sql = @sql + '
        AND p.WarehouseId = ' + CAST(@WarehouseId 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
   
    --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
   
    --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 LEN(@Keywords) > 0 /* Keyword searches ordered by rank */
        SET @sql_orderby = ' kp.Rank DESC'
    ELSE 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 + ', '
        SET @sql_orderby = @sql_orderby + ' p.[Name] ASC'
    END
   
    SET @sql = @sql + '
    ORDER BY' + @sql_orderby
   
    --PRINT (@sql)
    EXEC sp_executesql @sql

    DROP TABLE #FilteredCategoryIds
    DROP TABLE #FilteredSpecs
    DROP TABLE #FilteredCustomerRoleIds
    DROP TABLE #KeywordProducts

    CREATE TABLE #PageIndex
    (
        [IndexId] int IDENTITY (1, 1) NOT NULL,
        [ProductId] int NOT NULL
    )
    INSERT INTO #PageIndex ([ProductId])
    SELECT ProductId
    FROM #DisplayOrderTmp
    GROUP BY ProductId
    ORDER BY min([Id])

    --total records
    SET @TotalRecords = @@rowcount
   
    DROP TABLE #DisplayOrderTmp

    --prepare filterable specification attribute option identifier (if requested)
    IF @LoadFilterableSpecificationAttributeOptionIds = 1
    BEGIN       
        CREATE TABLE #FilterableSpecs
        (
            [SpecificationAttributeOptionId] int NOT NULL
        )
        INSERT INTO #FilterableSpecs ([SpecificationAttributeOptionId])
        SELECT DISTINCT [psam].SpecificationAttributeOptionId
        FROM [Product_SpecificationAttribute_Mapping] [psam] with (NOLOCK)
        WHERE [psam].[AllowFiltering] = 1
        AND [psam].[ProductId] IN (SELECT [pi].ProductId FROM #PageIndex [pi])

        --build comma separated list of filterable identifiers
        SELECT @FilterableSpecificationAttributeOptionIds = COALESCE(@FilterableSpecificationAttributeOptionIds + ',' , '') + CAST(SpecificationAttributeOptionId as nvarchar(4000))
        FROM #FilterableSpecs

        DROP TABLE #FilterableSpecs
     END

    --return products
    SELECT TOP (@RowsToReturn)
        p.*
    FROM
        #PageIndex [pi]
        INNER JOIN Product p with (NOLOCK) on p.Id = [pi].[ProductId]
    WHERE
        [pi].IndexId > @PageLowerBound AND
        [pi].IndexId < @PageUpperBound
    ORDER BY
        [pi].IndexId
   
    DROP TABLE #PageIndex
END

blog comments powered by Disqus