Trying to get count of products in each category in Thinking Sphinx

Question

I am using Thinking Sphinx and want to get count of products for each category. My index file:

ThinkingSphinx::Index.define :product, :with => :active_record do
  # fields
  indexes title, :sortable => true
  indexes product_category_id

  has created_at, updated_at
end

I tried to use query, using group_by

Product.search('phone').group_by(&:product_category_id)

But it returns me grouped hash with all search results, but I want to see only count, and want to make this query much lighter.

Also I tried query

Product.search('phone', :select => 'COUNT(product.id) as cr').group_by(&:market_id)

But it returns error

<ThinkingSphinx::SyntaxError: sphinxql: syntax error, unexpected IDENT, expecting DISTINCT or '*' near 'product.id) as cr FROM `product_core` WHERE MATCH('phone') AND `sphinx_deleted` = 0 LIMIT 0, 20; SHOW META' - SELECT COUNT(product.id) as cr FROM `product_core` WHERE MATCH('phone') AND `sphinx_deleted` = 0 LIMIT 0, 20; SHOW META>

Answer 1

As noted on GitHub:

Facet queries are what you'll want to use for this: http://pat.github.io/thinking-sphinx/facets.html

Two aspects to it - firstly, you'll want to convert product_category_id from a field to an attribute and set it to be a facet:

has product_category_id, :facet => true

And then you can make a facet call to get the summary information you're after:

Product.facets('phone')[:product_category_id]