I'm building a scope for a model to render some sort of randomness in a collection :

scope :biased_random, -> (value) {
      self.select("*, RANDOM() * (to_char(created_at, 'YYYYMMDD')::float / to_char(now(), 'YYYYMMDD')::float) AS randomable_int").order(randomable_int: :desc)
}

I'm satisfied with the results BUT I'm getting a PG::AmbiguousColumn: ERROR :

"PG::AmbiguousColumn: ERROR:  column reference \"created_at\" is ambiguous\nLINE 1: SELECT *, RANDOM() * (to_char(created_at, 'YYYYMMDD')::float...\n                                      ^\n"

SO I tried to specify the model name as it shouldn't be ambiguous anymore:

self.select("*, RANDOM() * (to_char(#{self.model.to_s.downcase}.created_at, 'YYYYMMDD')::float / to_char(now(), 'YYYYMMDD')::float) AS randomable_int").order(randomable_int: :desc)

And now I'm getting another PG error:

ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near "model name") 

("model name" being my model obviously)

I don't understand why it is ambiguous in the first place and then why when I'm specifying it, I'm getting another error.

CodePudding user response：

The PG::AmbiguousColumn error is most likely because your query is joining more than one table. You'll want to prefix the created_at with the name of the table, not of the model - https://api.rubyonrails.org/classes/ActiveRecord/ModelSchema/ClassMethods.html#method-i-table_name might help here.