Current time: 01-23-2021, 12:04 AM Hello There, Guest! (LoginRegister)

Post Reply 
 
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Database Query Search Time
08-16-2010, 04:53 PM
Post: #22
RE: Database Query Search Time
Greetings,

At this time, I am going to elaborate a bit on how I setup the price facet. My solution was inspired by an article I found on the net:

http://www.lucidimagination.com/Communit...earch-Solr

I am taking a quote out of the article:

If we request field faceting on the "price" field, we get back counts for individual prices. However, we want price ranges, not individual prices. One workaround is to index another field that contains the ranges that the prices fall into (for example 100_200, 200_300, 300_400) and use field faceting on that field. A more flexible solution is to utilize query facets that provide the ability to retrieve facet counts for arbitrary queries.

Let's assume that we have an indexed "price" field and we want to get the facet counts for the following ranges of prices: $100 or less, $100-$200, $200-$300, $300-$400, $400-$500, and $500 or over. We simply add a facet.query command to our query request for each desired range


I want two drop down lists of prices on our product search page containing the following values for minimum and maximum price:
0,10,20,30,40,50,75,100,150,200,250,300,350,400,450,500,600,700,800,900,1000,125​0,1500,1750,2000,3000,4000,5000,1000000000

Let's say we have a product whose price is $549.99. With the custom2 field, I can setup price ranges that this product falls into:

02p 102p 202p 302p 402p 502p 752p 1002p 1502p 2002p 2502p 3002p 3502p 4002p 4502p 5002p p2600 p2700 p2800 p2900 p21000 p21250 p21500 p21750 p22000 p23000 p24000 p25000

The value "2" separates the price and p. If I want to search for products with prices between 300 and 750 dollars, I can set the criteria like this: criteria="custom2:p2750 and custom2:3002p"

I tried different delimiters in place of the "2" above only to find that all results were returned to me instead of filtered results. I even tried to leave a delimiter out to save some space in the cfcollection. I am not sure if something was being cached or what but I did not retrieve the results I expected. However, when I leave the "2" in there, I get exactly what I want. I have tested this many times on a cfcollection of 100 products. I have the mindset on this case if it is not broke do not fix it or improve it for that matter.

I believe that this method is more difficult than it should be. I do not see why there can not be a way we can just store prices in a custom field and have some sort of range search within the coldfusion cfsearch wrapper. I am a bit disappointed with that.

My initials thoughts on SOLR so far is that it seems very touchy when using cfsearch. I find documentation hard to find when it comes to coldfusion and SOLR. Adobe has some basic tutorials but nothing advanced like price facets. I found that the skillset for coldfusion and SOLR development really hard to find.

I am not sure if anyone else has this problem. For cfsearch, if I try to set the "type" parameter to any value thats in the documentation, coldfusion throws an error. I am not sure if this is a problem with coldfusion in general or if an error occurred when updating my server. I intend to look into this more.

I am going to play around with categories / subcategories next. After that, I should be able to import all products into the cfcollection. Then I can see how fast SOLR is compared to just using CFQUERY.

I will release a bit of code as well to help people that come across this. Will do that after the script is completed.

Sincerely,
Travis Walters
Find all posts by this user
Quote this message in a reply
Post Reply 


Messages In This Thread
RE: Database Query Search Time - pmeenan - 07-04-2010, 12:03 PM
RE: Database Query Search Time - pmeenan - 07-05-2010, 04:35 AM
RE: Database Query Search Time - sajal - 07-05-2010, 05:03 AM
RE: Database Query Search Time - pmeenan - 07-08-2010, 08:47 PM
RE: Database Query Search Time - sajal - 08-13-2010, 10:06 PM
RE: Database Query Search Time - sajal - 08-14-2010, 03:38 AM
RE: Database Query Search Time - sajal - 08-14-2010, 06:50 AM
RE: Database Query Search Time - pmeenan - 08-16-2010, 02:55 AM
RE: Database Query Search Time - green-watch.org - 08-16-2010 04:53 PM
RE: Database Query Search Time - sajal - 08-17-2010, 01:14 AM
RE: Database Query Search Time - pmeenan - 08-23-2010, 01:22 AM
RE: Database Query Search Time - pmeenan - 08-23-2010, 08:45 PM
RE: Database Query Search Time - ringman57 - 08-24-2010, 11:52 PM
RE: Database Query Search Time - ringman57 - 08-25-2010, 02:54 AM

Forum Jump:


User(s) browsing this thread: 2 Guest(s)