Refining Thinking Sphinx search float field with Active Relation

[Neil Panjwani]

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 with the right 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?

[Pat Allan]

Hi Neil

As you may have realised already, Sphinx has its own query protocol - the SQL line in the logs is just to translate the search results into model instances. While Thinking Sphinx allows a few options to be passed through to the SQL query (:includes, :join, :select, :sql_order translated to :order), it doesn't respond to ARel methods and especially SQL WHERE clauses, because that would break pagination of the search results.

I'd recommend instead looking at using Sphinx attribute filters to get the behaviour you're after. If you've got price set up as an attribute, then your search query can just be the following:

Product.search 'eggs', :with => {:price => 0.0..3.0}

If the queries are going to get more complex than that, then feel free to ask here for help on how to construct them.

Cheers

--
Pat

> --
> You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group.
> To view this discussion on the web visit https://groups.google.com/d/msg/thinking-sphinx/-/ACZmJKVOy6AJ.
> To post to this group, send email to thinkin...@googlegroups.com.
> To unsubscribe from this group, send email to thinking-sphi...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.