Sure.
Here's a link: http://dl.dropbox.com/u/402152/ProductLoadAllPaged.sql
I've also copy pasted into this post at the end. Main changes are:
-> use of FREE TEXT TABLE - I've fully replaced the original section with my own - NOP will likely want to switich here based on setting
IF ISNULL(@Keywords, '') != ''
BEGIN
SET @SearchKeywords = 1
SET @Keywords = isnull(@Keywords, '')
SET @Keywords = rtrim(ltrim(@Keywords))
--dodane przez FK
SET @Keywords = dbo.StripPolishLetters(isnull(@Keywords, ''))
SET @Keywords = REPLACE(@Keywords, '-', '')
PRINT @Keywords
SET @sql = '
INSERT INTO #KeywordProducts ([ProductId], [Rank])
SELECT FT1.[key], COALESCE(FT1.RANK, 0)
FROM FREETEXTTABLE(Product, [FullText], @Keywords) as FT1
'
--PRINT (@sql)
EXEC sp_executesql @sql, N'@Keywords nvarchar(200)', @Keywords
END
Note that I chose to add a column called [FullText] to the product column and create index on it and than fill it with data. I think it is acceptable to assume that in order to use full text the user will have to have a certain sql admin skills to get the full text running. These skills should be sufficient to update the column to contains info the store owner wants to search on. Alternatively a system task could periodically update the column from p.name pv.name, p.description, etc etc. so that it contains some data. If this was done via calling a stored procedure it would also be easy to modify without recompile ..
second change -> change of sorting order - freetextable give you a rank which is usefull to show the better results at the top:
IF (@SearchKeywords = 1)
BEGIN
SET @sql_orderby = @sql_orderby + ' (COALESCE(kp.RANK, 0) + COALESCE(pv.DisplayOrder, -100)) desc '
END
In the code above I've merged the rank with display order variable to sort by combined values.
Filip
/****** Object: StoredProcedure [dbo].[ProductLoadAllPaged] Script Date: 02/23/2012 13:25:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ProductLoadAllPaged]
(
@CategoryId int = 0,
@ManufacturerId int = 0,
@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(MAX) = null,
@SearchDescriptions bit = 0,
@FilteredSpecs nvarchar(300) = 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
@PageIndex int = 0,
@PageSize int = 2147483644,
@ShowHidden bit = 0,
@TotalRecords int = null OUTPUT
)
AS
BEGIN
/* Products that filtered by keywords */
CREATE TABLE #KeywordProducts
(
[ProductId] int NOT NULL,
[Rank] int NOT NULL
)
DECLARE
@SearchKeywords bit,
@sql nvarchar(max),
@sql_orderby nvarchar(max)
SET NOCOUNT ON
--filter by keywords
IF ISNULL(@Keywords, '') != ''
BEGIN
SET @SearchKeywords = 1
SET @Keywords = isnull(@Keywords, '')
SET @Keywords = rtrim(ltrim(@Keywords))
--dodane przez FK
SET @Keywords = dbo.StripPolishLetters(isnull(@Keywords, ''))
SET @Keywords = REPLACE(@Keywords, '-', '')
PRINT @Keywords
SET @sql = '
INSERT INTO #KeywordProducts ([ProductId], [Rank])
SELECT FT1.[key], COALESCE(FT1.RANK, 0)
FROM FREETEXTTABLE(Product, [FullText], @Keywords) as FT1
'
--PRINT (@sql)
EXEC sp_executesql @sql, N'@Keywords nvarchar(200)', @Keywords
END
ELSE
BEGIN
SET @SearchKeywords = 0
END
--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
SET @SpecAttributesCount = (SELECT COUNT(1) FROM #FilteredSpecs)
--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 @CategoryId > 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
IF @ShowHidden = 0
OR @PriceMin > 0
OR @PriceMax > 0
OR @OrderBy = 10 /* Price: Low to High */
OR @OrderBy = 11 /* Price: High to Low */
BEGIN
SET @sql = @sql + '
LEFT JOIN ProductVariant pv with (NOLOCK)
ON p.Id = pv.ProductId'
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 @CategoryId > 0
BEGIN
SET @sql = @sql + '
AND pcm.CategoryId = ' + CAST(@CategoryId AS nvarchar(max))
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 product tag
IF ISNULL(@ProductTagId, 0) != 0
BEGIN
SET @sql = @sql + '
AND pptm.ProductTag_Id = ' + CAST(@ProductTagId AS nvarchar(max))
END
IF @ShowHidden = 0
BEGIN
SET @sql = @sql + '
AND p.Published = 1
AND pv.Published = 1
AND pv.Deleted = 0
AND (getutcdate() BETWEEN ISNULL(pv.AvailableStartDateTimeUtc, ''1/1/1900'') and ISNULL(pv.AvailableEndDateTimeUtc, ''1/1/2999''))'
END
--min price
IF @PriceMin > 0
BEGIN
SET @sql = @sql + '
AND (
(
--special price (specified price and valid date range)
(pv.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(pv.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(pv.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
AND
(pv.SpecialPrice >= ' + CAST(@PriceMin AS nvarchar(max)) + ')
)
OR
(
--regular price (price isnt specified or date range isnt valid)
(pv.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(pv.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(pv.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
AND
(pv.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)
(pv.SpecialPrice IS NOT NULL AND (getutcdate() BETWEEN isnull(pv.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(pv.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
AND
(pv.SpecialPrice <= ' + CAST(@PriceMax AS nvarchar(max)) + ')
)
OR
(
--regular price (price isnt specified or date range isnt valid)
(pv.SpecialPrice IS NULL OR (getutcdate() NOT BETWEEN isnull(pv.SpecialPriceStartDateTimeUtc, ''1/1/1900'') AND isnull(pv.SpecialPriceEndDateTimeUtc, ''1/1/2999'')))
AND
(pv.Price <= ' + CAST(@PriceMax 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 dbo.Product_SpecificationAttribute_Mapping psam
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 = ' pv.[Price] ASC'
ELSE IF @OrderBy = 11 /* Price: High to Low */
SET @sql_orderby = ' pv.[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 @CategoryId > 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
--name
--IF LEN(@sql_orderby) > 0 SET @sql_orderby = @sql_orderby + ', '
IF (@SearchKeywords = 1)
BEGIN
SET @sql_orderby = @sql_orderby + ' (COALESCE(kp.RANK, 0) + COALESCE(pv.DisplayOrder, -100)) desc '
END
ELSE
BEGIN
SET @sql_orderby = @sql_orderby + ' COALESCE(pv.DisplayOrder, -100) desc '
END
--SET @sql_orderby = @sql_orderby + ' p.[Name] ASC'
END
SET @sql = @sql + '
ORDER BY' + @sql_orderby
PRINT (@sql)
EXEC sp_executesql @sql
DROP TABLE #FilteredSpecs
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
--return products
SELECT TOP (@RowsToReturn)
p.*
FROM
#PageIndex [pi]
INNER JOIN Product p on p.Id = [pi].[ProductId]
WHERE
[pi].IndexId > @PageLowerBound AND
[pi].IndexId < @PageUpperBound
ORDER BY
[pi].IndexId
DROP TABLE #PageIndex
END
