Database-based paged EPiServer searches for CMS 5 R2 SP2
July 7th, 2009 by Adam Najmanowicz | 15 CommentsAparently I have written something on that note before for CMS 4 and it looks like someone still needs it as I got a request for an updated version for it a couple of days ago. So here we go:
for the most part the syntax for the call is equivalent to what is was before so go to my previous article regarding that (check out the old article for details). What I?ve added this time around is:
- the @PropertyName can be declared as ?%? if you want to look in all property names
- @PropertyType can be ?1 if you want to look in all property types otherwise you need to specify type id (this has changed from type name before due to database schema changes)
- additionally this version of the stored proc will only look in the Master language Branch, so it will work for the single language pages and for multi-language but for language agnostic properties. (should you require the language to be variable the change is pretty simple ? I can send you the updated version by email.
/****** Object: StoredProcedure [dbo].[PagedSearch] Script Date: 07/07/2009 12:18:10 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PagedSearch]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PagedSearch]
GO
CREATE Procedure PagedSearch
@Condition varchar(1024),
@PropertyName varchar(1024),
@PropertyType int,
@PageSize int,
@PageNumber int,
@Offset int
AS
BEGIN
DECLARE @RowStart int
DECLARE @RowEnd int
SET @RowStart = @PageSize * @PageNumber + @Offset;
SET @RowEnd = @RowStart + @PageSize + @Offset;
WITH PageRefs AS
(SELECT page.pkID as PageId,
ROW_NUMBER() OVER (ORDER BY pageLang.StartPublish DESC) as RowNumber
FROM tblPage page, tblProperty propValue, tblPageDefinition propDef, tblPageLanguage pageLang
WHERE page.pkID = propValue.fkPageID
AND page.fkMasterLanguageBranchID = pageLang.fkLanguageBranchID
AND page.pkID = pageLang.fkPageID
AND propValue.fkPageDefinitionID = propDef.pkID
AND (@propertyType = -1 or propDef.fkPageDefinitionTypeID = @propertyType) -- is proper type
AND propDef.Searchable = 1 -- the property is searchable
AND propValue.String like @Condition -- contains facets
AND propDef.[Name] like @PropertyName) -- property of proper name
SELECT PageId
FROM PageRefs
WHERE (RowNumber Between @RowStart and @RowEnd) or (@PageSize = 0);
END
GO
However… looking how the schema has changed over time, I am not convinced this approach is really the best one for someone who is not prepared to deal with the changes (e.g. you better be able to change the stored procedure based on the schema changes – or bribe me with pizza and beers for updates :) ).
Additionally this procedure only searches for properties that store their value in Short string field. To make it look into long string you need to Change the highlighted line to.
AND (propValue.LongString like @Condition)
or alternatively to look in both change it to:
AND ((propValue.String like @Condition) or (propValue.LongString like @Condition))
Enjoy!
Loading...
