Get max and min value of a column with thinking sp

Question

I'm working with Rails and thinking sphinx. I have a model Product indexed as following (only showing relevant information)

define_index do
   indexes :name, :as => :name, :sortable => true
   indexes color, :facet => true
   ...
   indexes price, :as => :range, :facet => true
   has created_at, price, root_category_id
   ...
end

What I need is to get the Product with max price of the current search. I've tried something like

 Product.search('', :select => 'MAX(price)')

but it's returning me a mess.

>> Product.search_for_ids( :select => 'MAX(price)')
Sphinx Query (3.0ms)  
Sphinx  Found 732 results
Product Load (0.4ms)  SELECT MAX(price) FROM `products` WHERE `products`.`id` IN (388, 389, 390, 391, 392, 393, 394, 395, 396, 397, 398, 399, 401, 402, 403, 404, 405, 406, 407, 408)
=> [nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil]

I really don't understand why it's doing that strange query, why it's adding that where and why it's returning an array.

Regards, Franco.

Answer 1

The short answer: Sphinx can't return aggregate data - it always returns document records (in this case, products). You'll have to use ActiveRecord/SQL for this type of query.

As for why your attempt is returning odd values: with Thinking Sphinx v2 and earlier, the :select option is passed through to the underlying ActiveRecord call when Sphinx results are translated into ActiveRecord options (the SQL call you can see). You've removed the primary key from the SELECT clause, and so Thinking Sphinx isn't able to match Sphinx results to Product instances, and thus nil is returned for each failed match.