PDA

View Full Version : Custom SQL on Category Page



bty_marketing
10-09-2012, 06:00 PM
I see that we have the ability to create custom SQL on category pages. For example our What's New page is not really a category in that we don't add items to it - we have code that shows all products based on a date range:
DisplayBeginDate >= (GETDATE() - 15) which works great!

I also see in the help, we can create a query like: P.ProductPrice < 25.00

Anyone have enough SQL to show me how we could combine two categories? An example would be: Category=Outerwear AND Category=BestSeller

Thanks!!

gifty
10-10-2012, 10:48 AM
ProductID IN (SELECT
Products.ProductID FROM Products,Categories_Products_Link,Products_Extende d
WHERE Products.ProductID=Categories_Products_Link.Produc tID
AND Products.ProductID=Products_Extended.ProductID
AND Categories_Products_Link.CategoryID=508)
OR
ProductID IN (SELECT
Products.ProductID FROM Products,Categories_Products_Link,Products_Extende d
WHERE Products.ProductID=Categories_Products_Link.Produc tID
AND Products.ProductID=Products_Extended.ProductID
AND Categories_Products_Link.CategoryID=520)

Just a word of warning. We have a lot of categories with Custom Where Clauses and we tried to use the filtered navigation and had to disable it because of a bug of some sort. You might check with support but I think the deal is you have to choose between the use of Custom Where Clauses or Filter Navigation but not both.

Now that said I think that only applies if you are custom where clauses more than just a few times.

bty_marketing
10-10-2012, 11:00 AM
Wow, thanks for that, it's much more complex than I realized! Where do you learn your SQL?

bty_marketing
10-11-2012, 10:49 AM
Wow, that worked like a charm! I changed OR to AND since I want to refine to only items that exist in both categories. http://www.boutiquetoyou.com/Recently_Seen_Fresh_Fashion_Seen_on_Today_s_Celebr ities_s/2872.htm if anyone else wants to create "filters" this way, it's a great bit of code, thanks Gifty!!