Thread: sort by price on search

I have a request to allow search results by price ASC and DESC as well as the default result. I have created a @sortorder param in the getproducts sproc. I am wondering where I can put in the order by.

v 9013.

In aspdnsf_getproducts stored proc, try replacing the ORDER BY pf.rownum (refer to screenshot).

Hi

This really should be a standard feature of ASPDNSF but I think Compunix sell an add-on that provides this functionality.

Alternatively, if you don't mind doing a small amount of development, you can make the change yourself and we've written a basic walkthrough here: http://www.webopius.com/content/439/...ducts-by-price

@Alfred: If I were to change the order by in that line, it would only sort on that page's results, not for the whole returned set.

@webopius: I can see how this would work at a category level, but when doing a search, inserting all variants into a temp table seems like a lot of overhead on the sql server.

Thank you webopius!

I believe the search page is a rather important page to all sites, and must have several key aspects like sorting/paging/filtering and other advanced features. Searching in advanced form does get server intensive but our cSearch has all of these features plus more while optimizing the work with your SQL; see http://www.ecommercecartmods.com/p-1...torefront.aspx

@jsimacek. No problem!

@chrismartz.

Indeed, you may be correct that creating a temp table is an overhead but if you look at the code for the aspdnsf_GetProducts stored procedure (which is also used by the search), this is exactly how it works.

Temp tables are created for #displayorder and #inventoryfilter along with the productfilter table.

...all we do is add additional sort criteria to what's already there.

Adam

Here is what I have done. It works perfectly with Low to High. The only problem I'm having now is that when I choose High to low, the pages are correct but each page shows the product in low to high order. Each page has the correct items on it, but the order they're in on each page needs to be reversed.

I used @sortorder as my option.

Code:

INSERT @productfilter (productid, displayorder, VariantID, VariantDisplayOrder, ProductName, VariantName, Price)  
			SELECT * FROM (
			SELECT DISTINCT p.productid, do.displayorder, pv.VariantID, pv.DisplayOrder as VariantDisplayOrder, p.Name, pv.Name as VariantName, Price
			FROM   
				product p with (nolock)  
				join #displayorder do on p.ProductID = do.ProductID   
				left join ProductVariant pv        with (NOLOCK) ON p.ProductID = pv.ProductID and pv.IsDefault >= @ViewType  
				left join productcategory pc       with (nolock) on p.ProductID = pc.ProductID   
				left join productsection ps        with (nolock) on p.ProductID = ps.ProductID   
				left join ProductManufacturer pm   with (nolock) on p.ProductID = pm.ProductID   
				left join ProductDistributor pd    with (nolock) on p.ProductID = pd.ProductID   
				left join ProductGenre px          with (nolock) on p.ProductID = px.ProductID   
				left join ProductVector px2        with (nolock) on p.ProductID = px2.ProductID   
				left join ProductLocaleSetting pl  with (nolock) on p.ProductID = pl.ProductID   
				left join ProductCustomerLevel pcl with (nolock) on p.ProductID = pcl.ProductID   
				left join ProductAffiliate pa      with (nolock) on p.ProductID = pa.ProductID
				   
				join #inventoryfilter i on pv.VariantID = i.VariantID  
			WHERE   
				  (pc.categoryid = @categoryID or @categoryID is null or @categorycount = 0)  
			  and (ps.sectionid = @sectionID or @sectionID is null or @sectioncount = 0)  
			  and (pl.LocaleSettingID = @localeID or @localeID is null or @localecount = 0)  
			  and (pa.AffiliateID = @affiliateID or pa.AffiliateID is null or @affiliatecount = 0 or @FilterProductsByAffiliate = 0)  
			  and (pm.manufacturerid = @manufacturerID or @manufacturerID is null or @manufacturercount = 0)  
			  and (pd.DistributorID = @distributorID or @distributorID is null or @distributorcount = 0)  
			  and (px.GenreID = @genreID or @genreID is null or @genrecount = 0)  
			  and (px2.VectorID = @vectorID or @vectorID is null or @vectorcount = 0)  
			  and p.ProductTypeID = coalesce(@ProductTypeID, p.ProductTypeID)  
			  and (case   
					when @FilterProductsByCustomerLevel = 0 or @custlevelcount = 0 or pcl.CustomerLevelID is null or @CustomerLevelID is null then 1  
					when @CustomerLevelFilteringIsAscending = 1 and pcl.CustomerLevelID <= @CustomerLevelID then 1   
					when pcl.CustomerLevelID = @CustomerLevelID or pcl.CustomerLevelID is null then 1   
					else 0  
				   end  = 1  
				  )  
			  and (@ftsenabled = 1 or
					(@ftsenabled = 0 and
						(@searchstr is null  
						 or patindex(@searchstr, isnull(p.name, '')) > 0  
						 --or patindex(@searchstr, isnull(convert(nvarchar(20),p.productid), '')) > 0   
						 or patindex(@searchstr, isnull(pv.name, '')) > 0  
						 or patindex(@searchstr, isnull(p.sku , '')+isnull(pv.skusuffix , '')) > 0
						 or patindex(@searchstr, isnull(p.manufacturerpartnumber, '')) > 0  
						 or patindex(@searchstr, isnull(pv.manufacturerpartnumber, '')) > 0  
						 or (patindex(@searchstr, isnull(p.Description, '')) > 0 and @extSearch = 1)  
						 or (patindex(@searchstr, isnull(p.Summary, '')) > 0 and @extSearch = 1)  
						)
					)
				  )  
			  and case when isnull(pv.saleprice,0) = 0 then 0 else 1 end >= @OnSaleOnly  
			  and p.published >= @publishedonly  
			  and pv.published >= @publishedonly  
			  and isnull(p.IsAPack, 0) <= 1-@ExcludePacks  
			  and isnull(p.IsAKit, 0) <= 1-@ExcludeKits  
			  and p.IsSystem <= 1-@ExcludeSysProds  
			  and p.Deleted = 0  
			  and pv.Deleted = 0  
			  and ((@IncludeAll = 1) or (getdate() between isnull(p.AvailableStartDate, '1/1/1900') and isnull(p.AvailableStopDate, '1/1/2999')))
			  ) a order by 
				case when @sortorder = 3 then Price end desc,
			  	case when @sortorder = 2 then Price end,
				case when @sortorder < 2 OR @sortorder > 3 then displayorder end,
				case when @sortorder < 2 OR @sortorder > 3 then Name end,
				case when @sortorder < 2 OR @sortorder > 3 then VariantDisplayOrder end,
				case when @sortorder < 2 OR @sortorder > 3 then VariantName end