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.

Solution 1

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

Solution 2

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.

Solution 3

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.

Comments

[user984621]

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?

[Denis de Bernardy]

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.

[Matt]

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.

[Denis de Bernardy]

@Matt: agreed, but we're likely missing the bits and pieces of the code that makes it appear.

[Nate Bird]

I was getting a similar error (different column) but it turned out to be a sort column. Thanks for the inspiration!

[Yetti]

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!

[Jigar Bhatt]

This is called perfect answer

[TiggerToo]

And if the scope is using a multi-column where?

[Spark.Bao]

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) }