1

I had this query working before implementing Mobility on my app :

Product.joins(:category).where('categories.name ILIKE ANY ( array[?] )', categories)

where categories is = params[:categories].map {|category| "%#{category}%" }

After having implemented Mobility gem, the query output was obviously: []

Thus I tried adding .i18n as stated in gem doc: Product.i18n.joins(:category).where('categories.name ILIKE ANY ( array[?] )', categories)

--> Outputs [], as it doesn't join on the translated table : SELECT "products".* FROM "products" INNER JOIN "categories" ON "categories"."id" = "products"."category_id" WHERE (categories.name ILIKE ANY ( array['%Categorie test%'] ))

Then I tried to join category translated table without success. I tried to inspire my query with following question. But all these queries fail :

Product.i18n.joins(:category).joins(:translations).where('categories.name ILIKE ANY ( array[?] )', categories)
Product.i18n.joins(:category).join_translations.where('categories.name ILIKE ANY ( array[?] )', categories)
Product.i18n.joins(:mobility_string_translations).where('categories.name ILIKE ANY ( array[?] )', categories)
Product.i18n.joins(:category_name_fr_string_translations).where('categories.name ILIKE ANY ( array[?] )', categories)

Returning either : undefined method join_translations or Can't join 'Product' to association association_name '

How can I join category and query with ILIKE to get translated outputs?

dbugger
  • 15,868
  • 9
  • 31
  • 33
guillaume
  • 651
  • 6
  • 14
  • SQL strings will not work, Mobility has no easy way to parse them. You can use the new block format with Arel predicates: https://github.com/shioyama/mobility#querying – Chris Salzberg Jul 02 '18 at 22:06

1 Answers1

2

Thanks to Chris' help, here is the working Arel query for whoever might be interested :

Product.joins(:category).merge(Category.i18n {name.matches_any(categories)})
guillaume
  • 651
  • 6
  • 14
  • Wow that's really great! I wasn't familiar with "matches any" so didn't know what the Arel method would be offhand, but you seem to have found it. The very nice thing about this is that it will work with *any backend*. I'm planning to create a wiki page on querying since stuff like this (added in 0.7) opens the door to much mroe interesting possibilities. I'll add this as an example there :) – Chris Salzberg Jul 06 '18 at 00:03