I have database of products and I use Thinking Sphinx to search through them. I have a pretty complex set of conditions to find the right product in the correct category and details. However, I'd like to refine the search so that I can make restrictions on the product's price and quantity, which are both float attributes of the Product model.
For ex:
1.9.2p290 :014 > Product.search('eggs').map { |p| "#{p.name}, $#{p.price}" }
Sphinx Query (4.6ms) eggs
Sphinx Found 984 results
Product Load (0.9ms) SELECT "products".* FROM "products" WHERE "products"."id" IN (7635, 7636, 7637, 7638, 7639, 7640, 7641, 7642, 7643, 7644, 7645, 7646, 7647, 7648, 7650, 7651, 7652, 7653, 7654, 7655)
[
[ 0] "Egg Beaters Pour Spout, $2.89",
[ 1] "Fleischman Regular Egg Beaters Egg Substitute, $3.09",
[ 2] "Fleischman Southwest Egg Beaters Egg Substitute, $2.89",
[ 3] "Lucerne Best Of The Egg, $2.79",
[ 4] "Lucerne Best Of The Egg, $2.69",
[ 5] "Lucerne Best Of The Egg, $5.29",
[ 6] "Lucerne Best Of The Egg Whites, $2.79",
[ 7] "Lucerne Best Of The Egg Whites, $5.29",
[ 8] "Papetti Foods All Whites Liquid Egg Whites, $5.89",
[ 9] "Papetti Foods Healthier Real Egg Product Better N Eggs, $5.89",
[10] "Papettis 100% All Egg Whites, $2.5",
[11] "Eating Right Eggs With Omega A, $3.99",
[12] "Egglands Best Cage Free Large Eggs, $3.29",
[13] "Egglands Best Cage Free Large Grade AA Brown Eggs, $4.39",
[14] "Egglands Best Classic Large Eggs, $5.49",
[15] "Egglands Best Grade AA Large Eggs, $4.09",
[16] "Hard Boiled Eggs Peeled And Ready To Eat, $1.49",
[17] "Land O Lakes Cage Free All Natural Large Grade A Brown Eggs, $4.39",
[18] "Land O Lakes Farm Fresh Extra Large Brown Eggs, $2.49",
[19] "Land O Lakes Organic All Natural Large Grade A Brown Eggs, $5.49"
]
1.9.2p290 :015 > Product.search('eggs').class
ThinkingSphinx::Search < Array
1.9.2p290 :016 > Product.search('eggs').where("price < ?", 3)
NoMethodError: Sphinx Query (5.5ms) eggs
Sphinx Found 984 results
Product Load (0.8ms) SELECT "products".* FROM "products" WHERE "products"."id" IN (7635, 7636, 7637, 7638, 7639, 7640, 7641, 7642, 7643, 7644, 7645, 7646, 7647, 7648, 7650, 7651, 7652, 7653, 7654, 7655)
undefined method `where' for #<ThinkingSphinx::Search:0x007feaf30e1880>
from /Users/neil/.rvm/gems/ruby-1.9.2-p290/gems/thinking-sphinx-2.0.10/lib/thinking_sphinx/search.rb:174:in `method_missing'
from (irb):16
from /Users/neil/.rvm/gems/ruby-1.9.2-p290/gems/railties-3.2.5/lib/rails/commands/console.rb:47:in `start'
from /Users/neil/.rvm/gems/ruby-1.9.2-p290/gems/railties-3.2.5/lib/rails/commands/console.rb:8:in `start'
from /Users/neil/.rvm/gems/ruby-1.9.2-p290/gems/railties-3.2.5/lib/rails/commands.rb:41:in `<top (required)>'
from script/rails:6:in `require'
from script/rails:6:in `<main>'
How do I use ActiveRelation to refine my search after TS find's matching text?
This should work:
Product.search 'eggs', :with => {:price => price, :quantity => quantity}
Assuming you have three different input boxes to define the search string ('eggs'), the price and the quantity, I would use something like this when trying to search exact matches:
scope = params[:query].present? Product.search params[:query] : scope = Product.search nil
scope = scope.search with: { :price => params[:price] } if params[:price].present?
scope = scope.search with: { :price => params[:quantity] } if params[:quantity].present?
@products = scope
But that wont filter 'prices lower than x' or 'quantity between x and y'. For that you'll need to use ranges, since thinking-sphinx. For example, if you want to search products with a price lower than a given value:
Product.search 'eggs', :with => {:price => 0..value}
That returns the products matching the query 'eggs' with a price between 0 and the given value.
If you want to search products with a price higher than a given value:
Product.search 'eggs', :with => {:price => value..Float::MAX}
That returns the products matching the query 'eggs' with a price between the given value and the maximum representable finite floating point number.