Create a search in the attributes of multiple tables

Question

I am using the globalize gem to add raductions to my website

and I have a complex problem, I have a search engine to filter activities

these are my two models:

class Activity < ActiveRecord::Base
  belongs_to :category

  translates :name, :description
end

class Category < ActiveRecord::Base
  has_many :activities

  translates :name
end

I generated their tables with the gem, I have activities and activity_translations, categories and category_translations

The fields that I want to translate (you can see in their models) are in their translation tables, if I want to search for activities or categories by name I have to search them in their translation tables

for activities this is easy:

activities = Activity.with_translations.where(event_id: event_id)
activities = activities.where('activity_translations.name LIKE :query', query: "%#{query}%")

but my problem is that I want to filter in activities by name and categories by name with the same query, for that I have to enter their translation tables, just as the categories in activities are optional

i am trying this:

activities = Activity.with_translations.where(event_id: event_id)
activities = activities
                       .joins("LEFT OUTER JOIN categories ON activities.category_id = categories.id INNER JOIN category_translations ON category_translations.category_id = categories.id")
                       .where('activity_translations.name LIKE :query OR activity_translations.description LIKE :query OR category_translations.name LIKE :query', query: "%#{query}%")

but this does not work just returns me the activities that have categories

any suggestions?

Answer 1

Diclaimer: I have never used the globalize gem so your milage may vary.

You could do this with two subselects instead of joins.

SELECT activities.* FROM activities 
WHERE activities.id IN 
  (SELECT activity_translations.activity_id FROM activity_translations WHERE activity_translations.name LIKE :query)
OR activities.category_id IN 
  (SELECT category_translations.category_id FROM category_translations WHERE category_translations.name LIKE :query)

I'm just guessing here that there is a x_id column on the translations table that points back.

You can reproduce that query in vanilla ActiveRecord with something like:

Activity.where(
  id: ActivityTranslation.select(:activity_id)
        .where('name LIKE ?', "%#{query}%"))
.or(
  Activity.where(
    category_id: CategoryTranslation.select(:activity_id))
       .where('name LIKE ?', "%#{query}%"))
)