How to do multi facets search in thinking sphinx rails

Question

Using thinking-sphinx for facets in my app and define two facets price and year.

I want to fetch facets nested results.

Now I am getting facets by year and it give count against year.

years = records[:year]

Along with the count of records along with year I also want to get min price in that year.

Now I getting it like

years.map do |year,count|
     price = Data.where(:year=>year).minimum(:price)
     {count: count,cheapest_price: price}
end

But its like LazyLoading as N+1 against year. I want to fetch prices along within year facets.

Is there any way to do it?

Answer 1

I think this code does what you want - though you may need to tweak things to get exactly what you're after:

Data.search(
  :select => "groupby() AS year, MIN(price) as min_price, count(DISTINCT sphinx_internal_id) as count",
  :group_by => :year,
  :max_matches => 1000,
  :middleware => ThinkingSphinx::Middlewares::RAW_ONLY
)

Each option explained:

• The select specifies the appropriate aggregations you're after, and groupby() is a function that refers to the attribute this query is grouping by.
• group_by specifies the attribute you want the query to group by. Sphinx's equivalent of GROUP BY in SQL, though in the past it has been more limited.
• max_matches should be set to 1000, unless you've configured it to be a higher value in your Sphinx configuration (via config/thinking_sphinx.yml). Using the highest possible value ensures the facet results scan as many documents as possible. You could also set :limit to the same value for the specific number of rows being returned here.
• middleware uses a limited set of Thinking Sphinx middleware classes, to avoid translating results into ActiveRecord objects - instead, you'll get an array of hashes with the Sphinx query data.