Thinking sphinx - narrow search with sql

Question

I faced trouble trying to narrow sphinx search result with custom sql condition. After 2 days of tries and frustration i even dont care how to do it: before search (through custom indexes conditions), inside search or after search.

I have two classes: Theme and Grade, and join table between them.

class Theme < ActiveRecord::Base
  has_many :grades, through: :theme_grades
end

class Grade < ActiveRecord::Base
  has_many :themes, through: :theme_grades
end

I want to filter sphinx search of themes with having special grade_id or not having grade_ids at all.

In ActiveRecord it should be like:

Theme.joins(:theme_grades).
      where('theme_grades.grade_id = ? OR theme_grades.grade_id IS NULL', %ID%)

In sql it is going to be like:

SELECT "themes".* FROM "themes" 
INNER JOIN "theme_grades" ON "theme_grades"."theme_id" = "themes"."id" 
WHERE (theme_grades.grade_id = %ID% OR theme_grades.grade_id IS NULL)

But i dont understand how to use it with search results.

I tried:

sql: { join: '...' } in search options hash, rewriting my 'WHERE' as join condition. It doesnt work, resulting in error like "Sphinx found IDs, but ActiveRecord cannot find them", because AR look for them in our narrowed search and expects to find them.
After search simply rip out ids of found objects (because after search we receive array, not relation), and find them through AR, what gives us relation. And we can work with relation - just add another scopes. The problem lies in excerpts - i need them, but i dont understand how to handle them to my relation.
The last idea that i have is to make 6 different indices for every one of my grade (i have 6 different grades). And somehow divide themes between them, but remembering that themes without grades should be in every index.

Sphinx and Sql are two different worlds. In Rails, both create results that behave like arrays of hashes, but they are not really joinable. If you have Sphinx index the grade_id also, you'll be able to use it as a filter in your Sphinx query. — Tom Harrison
@Tom, you are right. I was deceived by 'sql' options described at the bottom at this page pat.github.io/thinking-sphinx/searching.html - it looks like just for custom sql. Indexing of grade_id is a good idea. But theme has many grade_ids, so i dont see any solutions to compare array of grade_ids in theme indexed like: has theme.grade_ids, as: :grade_ids with one grade_id, through 'with' => {} in search. — Anton Kovalev

pat pat · Accepted Answer · 2014-11-09T12:37:02

It's best to have this filtering happen in Sphinx, because then pagination is reliable (and it's just a bit neater doing it all at once).

So, you'll need two attributes in your Theme index - one for all the grade ids (an array of integers), and one to indicate whether there are any grade ids (a boolean):

# if your index is SQL-backed (with: :active_record)
has theme_grades.grade.id, as: :grade_ids
has "COUNT(DISTINCT theme_grades.id) > 0", as: :any_grade_ids, type: :boolean

# or if you're using real-time indices:
has grade_ids, type: :integer, multi: true
has grade_ids.any?, as: :any_grade_ids, type: :boolean

And then searching would look like this - construct a dynamic attribute based on the grade id and filter by that attribute:

@themes = Theme.search select: "*, (IN(grade_ids, #{grade_id}) OR NOT any_grade_ids) AS grade_match",
  with: {grade_match: true}

Thinking sphinx - narrow search with sql

2 Answers