CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlruby-on-rails-4activerecord
abhilashak
abhilashak

Reputation: 3621

Rails postgres error: ActiveRecord::StatementInvalid Exception: PG::AmbiguousColumn: ERROR: column reference "sample_id" is ambiguous

I have a scope in the Rails User model (this scope is using every model):

scope :only_sample,                       -> { only_sample_items }

and 'only_sample_items' method is:

return where("sample_id IS NULL OR sample_id NOT IN (?) OR sample_id IN (?)", inactive_sample_ids, active_sample_ids)

When I am joining User with Projects table and getting this error. I know that sample_id is there in both users and projects table. This causes the ambiguity.

Can anyone tell me for avoiding this error how can I modify the 'only_sample_items' method ?

Upvotes: 0

Views: 1403

Answers (2)

abhilashak
abhilashak

Reputation: 3621

Finally I managed to get rid of the ambiguity by using Arel.

I changed my query as follows: 

item_table = klass.arel_table # klass be User, Project etc
where(item_table[:sample_id].eq(nil).or(item_table[:sample_id].not_in inactive_sample_ids).or(item_table[:sample_id].in active_sample_ids))

Now actually what happens is Arel take care of the query by adding table_name.coulmn name itself according to which model I am calling the scope.

Upvotes: 1

uma
uma

Reputation: 2972

Since you have sample id in users and projects both table that's why there is problem of ambiguity.

I think that the following code will help you:

return where("users.sample_id IS NULL OR users.sample_id NOT IN (?)", inactive_sample_ids)

or

 return where("projects.sample_id IS NULL OR projects.sample_id NOT IN (?)", inactive_sample_ids)

Upvotes: 3

Related Questions