Search code examples
sqlruby-on-railsrubysearchthinking-sphinx

Thinking sphinx - narrow search with sql

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:

  1. 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.

  2. 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.

  3. 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.

Solution

  • 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}