rails 3; activerecord; where; NOT EQUAL condition comparison between two columns in database

Question

Is there some way of comparing two columns in a database using where?

Say, I have two columns for user that tells:

1. city_of_birth
2. favourite_city

And I would like a list of users that have favourite_city that is different from city_of_birth.

I was hoping something like this would work.

@users = User.where(
  :city_of_birth != :favourite_city
).all

Running this results in

undefined method `where' for nil:NilClass

There are similar questions asked about using conditional statements for where, but none of them were about conditional statements about the columns in database itself.

Thank you in advance for the help.

Answer 1

The error relates to the constant User not being defined, however to answer your question about the where method...

:city_of_birth != :favourite_city

This will always be true, so your actually calling where like this...

User.where(true)

This won't do much I'm afraid. I think you maybe getting this confused with the hash condition syntax which can be used. That also won't be of much use to you. You would need to use a string condition like this...

User.where('users.city_of_birth != users. favourite_city')

This is effectively just a snippet of SQL that will eventually be included in the final statement sent to the database.