In Rails, how do you write a finder with "where" that compares two dates?

Question

I’m using Rails 4.2. I have a model with these date time attributes

submitted_at
created_at

How do I write a finder method that returns all the models where the submitted_at field occurs chronologically before the created_at field? I tried this

MyModel.where(:submitted_at < :created_at)

But that is returning everything in my database, even items that don’t match.

Answer 1

where(:submitted_at < :created_at) is actually where(false). When you compare two symbols with the lt/gt operators you're actually just comparing them alphabetically:

:a < :b # true
:c > :b # true

where(false) or any other argument thats blank? just returns an "unscoped" relation for chaining.

The ActiveRecord query interface doesn't really have a straight forward way to compare columns like this.

You either use a SQL string:

Resource.where('resources.submitted_at < resources.created_at')

Or use Arel to create the query:

r = Resource.arel_table
Resource.where(r[:submitted_at].lt(r[:created_at]))

The results are exactly the same but the Arel solution is arguably more portable and avoids hardcoding the table name (or creating an ambigeous query).