Rails ActiveRecord: PG::Error: ERROR: column reference "created_at" is ambiguous

Question

I am struggling with the error in object and not sure at all where is the problem.

This is how the models looks like:

class Car < ActiveRecord::Base  
  has_many :car_colors
  has_many :colors, :through => :car_colors
end

class CarColor < ActiveRecord::Base
  belongs_to :color
  belongs_to :car
end

class Color < ActiveRecord::Base  
  has_many :car_colors
  has_many :cars, :through => :car_colors
end

Here is the query:

@cars = Car.all(:joins => :car_colors, :conditions => { :car_colors => {:color_id => params[:id_number]}}, :order => "cars.created_at DESC")

And the error output:

PG::Error: ERROR:  column reference "created_at" is ambiguous
LINE 1: ...d" WHERE "car_colors"."color_id" = 2 AND (created_at...
                                                             ^
: SELECT "cars".* FROM "cars" INNER JOIN "car_colors" ON "car_colors"."car_id" = "cars"."id" WHERE "car_colors"."color_id" = 2 AND (created_at > '2013-05-03 12:28:36.551058') ORDER BY cars.created_at DESC

The generated SQL query (below the error message) seems to be fine, but what causes the error message?

Thank you in advance.

Are you sure there's nothing missing from the query you posted? No conditions on the associations or anything? Something is applying a date condition but nothing in the query should do that. — Matt, Jun 03 '13 at 12:45
Yes, I am completely sure. That's why is the error so weird. — user984621, Jun 03 '13 at 14:03
Similar question, check it out.: https://stackoverflow.com/questions/15885335/rails-has-many-through-pgerror-error-column-reference-id-is-ambiguous-err/15885379 — ahembal, Sep 30 '19 at 11:21

score 56 · Accepted Answer · answered Jun 03 '13 at 12:46

56

There likely is a created_at field in your car_colors table. created_at should probably be cars.created_at to remove the ambiguity.

answered Jun 03 '13 at 12:46

Denis de Bernardy

75,850
13
131
154

I am just checking the `car_colors` migration and yes, there is `t.timestamps` - but why is this problem? Anyway, I will try to remove it. What do you mean by the second sentence? – user984621 Jun 03 '13 at 12:54
2

It's a problem because PG can't know whether you're meaning `created_at` for `cars` or `car_colors`... SQL is not a language that gives any room to ambiguity: either the statement is clear with no possibility of interpretation, or it is not and should get rejected with the error message that you're seeing. – Denis de Bernardy Jun 03 '13 at 12:55
This doesn't explain why `AND (created_at > '2013-05-03 12:28:36.551058')` appears in the SQL, removing the timestamp is not a solution, it just glosses over the problem. – Matt Jun 03 '13 at 14:14
@Matt: agreed, but we're likely missing the bits and pieces of the code that makes it appear. – Denis de Bernardy Jun 03 '13 at 14:16
This is called perfect answer – Jigar Bhatt Sep 12 '16 at 13:42

Adam Waite · Answer 2 · 2014-10-07T21:21:44.687

28

Define a scope like this:

scope :scope_age, -> { order(created_at: :desc) }

rather than:

scope :scope_age, -> { order("created_at DESC") }

It removes the ambiguity by using the property of the model in which the scope is defined in.

edited Oct 07 '14 at 21:21

answered Jun 17 '14 at 21:52

Adam Waite

19,175
22
126
148

1

This didn't quite work for me. I was getting "undefined method 'gsub' for {:name=>:asc}:Hash" when I tried `order(name: :asc)`. However, I was able to get `order("users.name ASC")` to work. Thanks for the inspiration that got me to my answer! – Yetti May 24 '15 at 22:39
1

And if the scope is using a multi-column where? – TiggerToo Mar 27 '17 at 14:51
the definition `default_scope { order('created_at DESC') }` cause me the same issue, fixed it by removing it or changing to `default_scope { order(created: :desc) }` – Spark.Bao Mar 12 '19 at 07:32

score 1 · Answer 3 · answered Jun 03 '13 at 14:18

Don't remove your timestamps from the join model, they aren't the problem - the problem is that something is adding a condition to your query:

AND (created_at > '2013-05-03 12:28:36.551058')

Since the date is one month ago, search your code for one.month.ago and see if it appears in any scopes, probably in your cars or car_colors models. Check the scopes manually if nothing turns up through the search.

Removing the timestamps will make your query work, but it's not the right thing to do.

I was getting a similar error (different column) but it turned out to be a sort column. Thanks for the inspiration! — Nate Bird, Nov 22 '13 at 13:48

Rails ActiveRecord: PG::Error: ERROR: column reference "created_at" is ambiguous

3 Answers3