Filterrific and Globalize

Question

It appears as if filterrific does not take content in translation tables into account (Globalize).

Is there anyway to search translation tables as well? My setup works perfectly well if the content is in the actual model. However, once the fields are empty and only entered in the translation table no results are being displayed (obviously).

My Model:

class Manual < ApplicationRecord
  translates :title, :content, :teaser, :slug

  extend FriendlyId
  friendly_id :title, :use => :globalize

  belongs_to :user
  belongs_to :support_category
  has_many :manual_faqs
  has_many :faqs, :through => :manual_faqs

  validates :title, presence: true
  validates :content, presence: true
  validates :user_id, presence: true

  update_index('manuals#manual') { self }

  filterrific(
      default_filter_params: { sorted_by: 'created_at_desc' },
      available_filters: [
          :sorted_by,
          :search_query,
          :with_user_id,
          :with_created_at_gte
      ]
  )

  scope :with_user_id, lambda { |user_ids|
    where(user_id: [*user_ids])
  }

  scope :search_query, lambda { |query|
    # Searches the students table on the 'first_name' and 'last_name' columns.
    # Matches using LIKE, automatically appends '%' to each term.
    # LIKE is case INsensitive with MySQL, however it is case
    # sensitive with PostGreSQL. To make it work in both worlds,
    # we downcase everything.
    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_conds = 2
    where(
        terms.map { |term|
          "(LOWER(manuals.title) LIKE ? OR LOWER(manuals.content) LIKE ?)"
        }.join(' AND '),
        *terms.map { |e| [e] * num_or_conds }.flatten
    )
  }

  scope :sorted_by, lambda { |sort_option|
    # extract the sort direction from the param value.
    direction = (sort_option =~ /desc$/) ? 'desc' : 'asc'
    case sort_option.to_s
      when /^created_at_/
        # Simple sort on the created_at column.
        # Make sure to include the table name to avoid ambiguous column names.
        # Joining on other tables is quite common in Filterrific, and almost
        # every ActiveRecord table has a 'created_at' column.
        order("manuals.created_at #{ direction }")
      else
        raise(ArgumentError, "Invalid sort option: #{ sort_option.inspect }")
    end
  }

  scope :created_at_gte, lambda { |reference_time|
    where('manuals.created_at >= ?', reference_time)
  }

  def self.options_for_sorted_by
    [
        ['Date received (newest first)', 'created_at_desc'],
        ['Date received (oldest first)', 'created_at_asc']
    ]
  end
end

My Controller:

  def index
    @filterrific = initialize_filterrific(
        Manual,
        params[:filterrific],
        select_options: {
            sorted_by: Manual.options_for_sorted_by,
            with_user_id: User.options_for_select
        }
    ) or return

    @manuals = @filterrific.find.page(params[:page])

    respond_to do |format|
      format.html
      format.js
    end

  rescue ActiveRecord::RecordNotFound => e
    # There is an issue with the persisted param_set. Reset it.
    puts "Had to reset filterrific params: #{ e.message }"
    redirect_to(reset_filterrific_url(format: :html)) and return
    #respond_with(@references)
  end

Answer 1

I don't know filterrific at all but I do know Globalize, and since filterrific is based on AR scopes it should be simply a matter of joining the translation table to get results to show up.

Here's your search_query scope modified to join and search the joined translations table (without the comments for clarity):

scope :search_query, lambda { |query|
  return nil if query.blank?

  terms = query.downcase.split(/\s+/)

  terms = terms.map { |e|
    ('%' + e.gsub('*', '%') + '%').gsub(/%+/, '%')
  }

  num_or_conds = 2
  where(
    ('(LOWER(manual_translations.title) LIKE ? OR'\
     ' LOWER(manual_translations.content) LIKE ?)' * (terms.count)).join(' AND '),
    *terms.map { |e| [e] * num_or_conds }.flatten
  ).with_translations
}

Notice I've only changed two things: (1) I've appended with_translations, a method described in this SO answer which joins the translations for the current locale, and (2) I've swapped the manuals table for the manual_translations table in the query.

So if you call this query in the English locale:

Manual.search_query("foo")

you get this SQL:

SELECT "manuals".* FROM "manuals"
INNER JOIN "manual_translations" ON "manual_translations"."manual_id" = "manuals"."id"
WHERE (LOWER(manual_translations.title) LIKE '%foo%' OR
       LOWER(manual_translations.content) LIKE '%foo%')
      AND "manual_translations"."locale" = 'en'"

Notice that with_translations is automatically tagging on that manual_translations.locale = 'en' so you filter out only results in your locale, which I assume is what you want.

Let me know if that works for you.