Rails query for two columns of the same table are equal to their values

Question

I am trying to find if there exists a more Rails-y way to generate the following query

# The purpose of this query is to select Users who have not updated
# their user record.

SELECT `users`.* FROM `users` WHERE `users`.`created_at` = `users`.`updated_at`

I can accomplish it using Arel with the following:

arel_where = User.arel_table[:created_at].eq(User.arel_table[:updated_at])
User.where(arel_where)
  #=> … WHERE `users`.`created_at` = `users`.`updated_at`

However I cannot use any combination of the hash syntax to do this without accessing the table and using it's equality method. The following two fail due to turning the key into a string.

User.where(created_at: :updated_at)
  #=> … WHERE `users`.`created_at` = 'updated_at'

User.where(created_at: User.arel_table[:updated_at])
  #=> … WHERE `users`.`created_at` = '--- !ruby/struct:Arel::Attributes::Attribu…

Edit

While not a hard requirement I'm trying to avoid strings like the following:

User.where('created_at = updated_at')
  #=> WHERE (created_at = updated_at)

I sometimes analyze my logs and the difference in the query between this and the first which uses Arel would need to be accounted for.

Ariannaarianne

Answer 1

User.where("created_at = updated_at")

Yangyangtze

Thanks Ted! I completely forgot to explain why I wasn't going for this much simpler solution. This will work for tons of people but I'm raising the question because I'm looking for something a little off the beaten path. – Ariannaarianne

This will happily fail when rails generates a join query, e.g. ActiveRecord::StatementInvalid: Mysql2::Error: Column 'id' in where clause is ambiguous – Brooking

Answer 2

You can do it like this with arel:

User.where(User.arel_table[:created_at].eq(User.arel_table[:updated_at]))

Volding