Mysql2::Error: Column 'created_at' in order clause is ambiguous

I have the following query that is working correctly

ObjectItem.find(:all, :include => :object_groups, :conditions =>  "object_items.description LIKE '%#{search}%' OR object_groups.description LIKE '%#{search}%'", :order => 'object_items.created_at DESC')

But doing query this way is now deprecated so i'm trying to change to this form

ObjectItem.order('object_items.created_at DESC').includes(:object_groups).where("object_item开发者_高级运维s.description LIKE '%#{search}%' OR object_groups.description LIKE '%#{search}%'")

But i'm getting the following error:

Mysql2::Error: Column created_at in order clause is ambiguous: SELECT  DISTINCT `object_items`.id FROM `object_items` LEFT OUTER JOIN `object_groups_object_items` ON `object_groups_object_items`.`object_item_id` = `object_items`.`id` LEFT OUTER JOIN `object_groups` ON `object_groups`.`id` = `object_groups_object_items`.`object_group_id` WHERE (object_items.description LIKE '%%' OR object_groups.description LIKE '%%') ORDER BY object_items.created_at DESC, created_at DESC  LIMIT 20 OFFSET 0

order('users.created_at DESC')

Basically the problem is that there is join query probably so both the table has the created_at field so its throw the error. if you want to order the result according to which tables created_at so using the table_name.field_name on which you order the result.

ORDER BY object_items.created_at DESC, created_at DESC LIMIT 20 OFFSET 0

As the previous answer says, it's got a hanging "created_at" that could refer to the column in either table.

It's clearly not the one that you are directly passing in with .order - so perhaps you have a default scope with a default order somewhere in your model. If you explicitly make that object_items.created_at it may possibly fix the problem.

try this : order('users.created_at DESC') or order(created_at: :asc)

should be worked for the same thing

This is usually the result of the same field name appearing in multiple tables.

In your case it looks like object_items and object_groups both have the same field 'created_at'.

The best thing (in my opinion) would be to rename the field in one of the tables by (for example) using a prefix to the field name.

How did you define the object_groups relation in ObjectItem model?

ORDER BY created_ar DESC may be added to the query if you have relation similar to following in ObjectItem:

has_many :object_groups, :order => "created_at DESC"

I ran into this today on a Spree project. It is almost always caused by leaving the table name off the order clause. See this ticket for how I solved the problem: https://github.com/spree/spree/issues/2654

In my case, I had added a decorator to one of my spree models, and it was adding an order clause without the appropriate table name.