searches not working

[stevenwoudstra]

Hi, i was trying to make a search filed in my rails application but it seems not to work with the mobility tables.

This is my model function: self.visible.where("LOWER(#{answer}) LIKE :term OR LOWER(#{question}) LIKE :term", term: "%#{term.downcase}%")

Rails console:

Faq Load (0.6ms)  SELECT "faqs".* FROM "faqs" WHERE "faqs"."visible" = $1 AND (LOWER(answer_nl) LIKE '%kaas%' OR LOWER(question_nl) LIKE '%kaas%')  [["visible", true]]
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "answer_nl" does not exist
LINE 1: ...ROM "faqs" WHERE "faqs"."visible" = $1 AND (LOWER(answer_nl)...

It looks like he is not searching in the mobility tables. Am i doing something wrong or do i need an other way to approach this?

[shioyama]

Yes, this is the expected behaviour. I probably should put a note somewhere in the readme about this.

The problem is that Mobility overrides where, find_by etc so you can treat translated attributes like untranslated attributes, for any backend, but there are limits on how far you can go with this, because translated attributes are not actually columns on the model table.

So in your case above, you are using a raw SQL string, which Mobility cannot parse. I tried to see if it would be possible to do this, but it's very tricky to do and I don't think it would work well.

So basically, (currently at least) you can only use query methods on translated attributes with hash arguments, like: self.visible.where(answer: "foo"), etc. If you do this, Mobility will do some magic to translate those queries into Arel which ActiveRecord can then parse and convert to nice SQL.

If you use SQL strings then AR will actually think you want the column on the model table, but there is no column on the model table (it is in another table, or somewhere else depending on your backend).

Also, I would like to at least raise an error with a message when you try to do this (use SQL string with translated attributes), but even just parsing the SQL string to do that is tricky.

Which backend are you using? If it's the default KeyValue backend, then complex queries are somewhat difficult (this is one of the downsides of that backend).

[shioyama]

Also, in case it wasn't clear from my answer above, things like LOWER are difficult since a hash argument to where will not allow you to use raw SQL operators like that. You will probably need to write SQL specific to the backend you are using for this.

[stevenwoudstra]

Okee thanks a lot for your quick responds. I'm using the table backend. I did just try this in my console but still it is saying that the table does not exist Faq.where(answer: 'always')

irb(main):010:0> Faq.where(answer: 'always')
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column faqs.answer does not exist
LINE 1: SELECT "faqs".* FROM "faqs" WHERE "faqs"."answer" = $1
                                          ^
: SELECT "faqs".* FROM "faqs" WHERE "faqs"."answer" = $1

[shioyama]

Ah, you need to either use the i18n scope or set a default scope in your model:

Faq.i18n.where(answer: 'always')

or

class Faq < ApplicationRecord
  default_scope { i18n }
end

Faq.where(answer: 'always')

Can you try that?

[stevenwoudstra]

yes thank you that worked, but do you know of a way how i can do something like this: Faq.where(answer: LIKE "%always%") so it searches in the columns?

[shioyama]

Actually if you're using the table backend, it's not so hard. I think this should work:

Faq.join_mobility_model_translations.where("answer LIKE '%always%'")

If you want to simplify this, you can pass association_name: :translations to translate in your model, then it's just:

Faq.join_translations.where("answer LIKE '%always%'")

You can also use LOWER, etc. here I believe.

[stevenwoudstra]

okee that did not work..

irb(main):009:0> Faq.join_mobility_model_translations.where("answer LIKE '%always%'")
NoMethodError: undefined method `join_mobility_model_translations' for #<Class:0x0055fb7c78f6e0>

and in the model i did this

translates :answer, type: :string, locale_accessors: true, association_name: :translations

[shioyama]

If you put association_name: :translations in the model, then you can just use: Faq.join_translations.where("answer LIKE '%answer%'").

The class method is join_<association_name>, so if association_name is translations then the method id join_translations.

[shioyama]

Closing this since it's not a bug and not a feature request either.

[stevenwoudstra]

I'm sorry to bother you this much, but i just can't get it to work. when i put association_name: :translations in my model i get an error nexpected ':', expecting keyword_end association_name: :translations I got the feeling that i'm doing it horribly wrong but i just can't get it right Do you have an example of a model that is configured the right way?

[shioyama]

I meant you need to include it as an option to translates:

class Faq < ApplicationRecord
  include Mobility
  translates :answer, :question, association_name: :translations
  default_scope { i18n }
end

Does that work?

[shioyama]

If not, please post your code.

[stevenwoudstra]

it diddn't work

include Mobility
  translates :question, type: :string, locale_accessors: true
  translates :answer, type: :string, locale_accessors: true, association_name: :translations
  translates :slug, type: :string, locale_accessors: true

  default_scope { i18n }

  belongs_to :faq_category
  validates :faq_category_id, :question_nl, :answer_nl, presence: true

  scope :with_site_type, -> (type) { where(site_type: type) }
  scope :visible, -> { where(visible: true) }

  after_update :update_sitemap
  before_save :set_site_type

  extend FriendlyId
  friendly_id :question, use: [:slugged, :history, :mobility]

This is my model And then i get this error in my console: irb(main):023:0> Faq.join_translations NoMethodError: undefined method 'join_translations'

[shioyama]

You're using the table backend, right? Then you don't need those type: :string in there, that's for the key-value backend.

Otherwise I don't see what's wrong there. What if you just remove association_name: :translations and call Faq.i18n.join_mobility_model_translations ?

[stevenwoudstra]

with the association_name: :translations in the model i get an error on the create

PG::UniqueViolation: ERROR: duplicate key value violates unique constraint "index_faq_translations_on_faq_id_and_locale" DETAIL: Key (faq_id, locale)=(43, nl) already exists. : INSERT INTO "faq_translations" ("question", "slug", "locale", "faq_id", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5, $6) RETURNING "id"

without the association_name: :translations it does work. and the Faq.i18n.join_mobility_model_translations does work to! :)

but it looks like that it does not show the translated column in there

irb(main):005:0> Faq.i18n.join_mobility_model_translations
  Faq Load (0.3ms)  SELECT "faqs".* FROM "faqs"
=> #<ActiveRecord::Relation [#<Faq id: 41, faq_category_id: 13, visible: true, created_at: "2017-04-18 09:58:24", updated_at: "2017-04-18 09:58:24", site_type: "jobseeker">, #<Faq id: 42, faq_category_id: 14, visible: true, created_at: "2017-04-18 09:59:55", updated_at: "2017-04-18 09:59:55", site_type: "employer">]>

[stevenwoudstra]

okee i was trying some things here and found out that if i do my model like this :

class Faq < ApplicationRecord
  include Mobility
  translates :question, locale_accessors: true, association_name: :translations
  translates :answer, locale_accessors: true, association_name: :translations
  translates :slug, locale_accessors: true, association_name: :translations

  default_scope { i18n }

  belongs_to :faq_category
  validates :faq_category_id, :question_nl, :answer_nl, presence: true

  scope :with_site_type, -> (type) { where(site_type: type) }
  scope :visible, -> { where(visible: true) }

  after_update :update_sitemap
  before_save :set_site_type

this comand does not work Faq.join_translations but this does Faq.i18n.join_translations

next I attempted to try a where statmenty irb(main):009:0> Faq.i18n.join_translations.where("answer LIKE 'functie'") but i got an error again

Faq Load (0.7ms)  SELECT "faqs".* FROM "faqs" WHERE (answer LIKE 'functie')
ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column "answer" does not exist
LINE 1: SELECT "faqs".* FROM "faqs" WHERE (answer LIKE 'functie')

[shioyama]

Yes sorry I realized you need to add association_name to every translates in order for that to work. I'll add some global configuration options so you can set it in one place in a future release.

About the query, you need to explicitly pass the table name:

Faq.i18n.join_translations.where("faq_translations.answer LIKE 'functie'")

(haven't tested that)

I'm not sure why the default_scope is not working though.

[stevenwoudstra]

yes that is working thank a lot :) :+1:

[stevenwoudstra]

okee now it is getting realy stage the first time i execut the comand it woks but the second time it fails....

Loading development environment (Rails 5.0.1)
irb(main):019:0> Faq.i18n.join_translations.where("faq_translations.answer LIKE '%functie%'")
  Faq Load (0.5ms)  SELECT "faqs".* FROM "faqs" INNER JOIN "faq_translations" ON "faq_translations"."faq_id" = "faqs"."id" AND "faq_translations"."locale" = 'nl' WHERE (faq_translations.answer LIKE '%functie%')
=> #<ActiveRecord::Relation [#<Faq id: 41, faq_category_id: 13, visible: true, created_at: "2017-04-18 09:58:24", updated_at: "2017-04-18 09:58:24", site_type: "jobseeker">]>

irb(main):020:0> Faq.i18n.join_translations.where("faq_translations.answer LIKE '%functie%'")
  Faq Load (0.6ms)  SELECT "faqs".* FROM "faqs" WHERE (faq_translations.answer LIKE '%functie%')
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "faq_translations"
LINE 1: SELECT "faqs".* FROM "faqs" WHERE (faq_translations.answer L...
                                           ^
: SELECT "faqs".* FROM "faqs" WHERE (faq_translations.answer LIKE '%functie%')

[shioyama]

That's probably the same issue as #23.

Can you try using mobility like below?

gem 'mobility', github: 'shioyama/mobility', branch: 'do_not_memoize_scope'

In any case I'm working on this issue, it's a bug.

[stevenwoudstra]

yes that was the problem thnks again ^^ your awsome

[shioyama]

Can you clarify, did updating the Gemfile fix it?

[stevenwoudstra]

yes that was the problem updating the Gemfile fixed it

[shioyama]

Great thanks for confirming :smile:

[stevenwoudstra]

no problem thank you for thanking your time helping me :smiley: