Using Filterrific in Rails to search through Active Record Associations

Question

I have a teacher model and a student model (teacher has_many students). Hoping to use Filterrific to search through teachers by their students' names.

The default functionality of filterrific is to search teachers based on SQL queries to the teachers table.

I'm wondering how I would be able to search teachers based on their associations' tables. Here is the SQL code in teacher.rb:

 scope :search_query, lambda { |query|
    return nil  if query.blank?
    # condition query, parse into individual keywords
    terms = query.downcase.split(/\s+/)
    # replace "*" with "%" for wildcard searches,
    # append '%', remove duplicate '%'s
    terms = terms.map { |e|
      (e.gsub('*', '%') + '%').gsub(/%+/, '%')
    }
    # configure number of OR conditions for provision
    # of interpolation arguments. Adjust this if you
    # change the number of OR conditions.
    num_or_conditions = 3
    where(
      terms.map {
        or_clauses = [
          "LOWER(teachers.first_name) LIKE ?",
          "LOWER(teachers.last_name) LIKE ?",
          "LOWER(teachers.email) LIKE ?"
        ].join(' OR ')
        "(#{ or_clauses })"
      }.join(' AND '),
      *terms.map { |e| [e] * num_or_conditions }.flatten
    )
  }

Answer 1

I took the sample code from a scenario where I use includes instead of joins because I need a right outer join. In that case I have to let Rails know that I'm referencing the students table.

I'm not sure if you actually need the references portion with joins. Give it a try without, and I'd be curious to know if it works.

scope :search_query_by_students, lambda { |query|
  return nil  if query.blank?
  # condition query, parse into individual keywords
  terms = query.downcase.split(/\s+/)
  # replace "*" with "%" for wildcard searches,
  # append '%', remove duplicate '%'s
  terms = terms.map { |e|
    (e.gsub('*', '%') + '%').gsub(/%+/, '%')
  }
  # configure number of OR conditions for provision
  # of interpolation arguments. Adjust this if you
  # change the number of OR conditions.
  num_or_conditions = 3
  where(
    terms.map {
      or_clauses = [
        "LOWER(students.first_name) LIKE ?",
        "LOWER(students.last_name) LIKE ?",
        "LOWER(students.email) LIKE ?"
      ].join(' OR ')
      "(#{ or_clauses })"
    }.join(' AND '),
    *terms.map { |e| [e] * num_or_conditions }.flatten
  ).joins(:students).references(:students)
}