Thinking Sphinx filtering on string field in a has_many association

I need to use Thinking Sphinx to filter on a string field from another table connected by a has_many association.

Say for example a Student has many degrees. The "graduation_year", which is the filter field in question, is in the degrees table and has to be a string (for some proprietary reason).

Following the Thinking Sphinx recommendation for filtering on string attributes, I've tried defining the index as follows:

define_index do
  has "CRC32(degrees.graduation_year)", :as => :graduation_years, :type => :integer
end

I get this error on running the index:

ERROR: index 'student_delta': sql_range_query: ERROR:  column "degrees.graduation_year" must appear in the开发者_Python百科 GROUP BY clause or be used in an aggregate function

The database is Postgres.

If we're dealing with a has_many, then you're going to want to have the results for that column aggregated:

has "array_to_string(array_agg(CRC32(degrees.graduation_year)), ',')",
  :as => :graduation_years, :type => :multi

If you're using a version of PostgreSQL older than 8.4, then switch array_agg to array_accum. The former is part of 8.4 and later versions, and is faster.

However, if we're dealing with years, then they're integers at heart anyway, so why not the following instead:

has "array_to_string(array_agg(degrees.graduation_year), ',')",
  :as => :graduation_years, :type => :multi

No need to CRC the values, and Sphinx treats them as a collection of integers, so your filters remain simpler as well.