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

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.

CodePudding user response：

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), any other argument thats blank? just returns an "unscoped" relation for chaining. In the case the code does a whole lot of nothing.

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

CodePudding user response：

You can use .to_sql to see what query is generated.

For yours it looks like this:

Resource.where(:submitted_at < :created_at).to_sql 
# => SELECT `resources`.* FROM `resources`

If you update like below, you will get some results:

Resource.where('submitted_at < created_at')
# => SELECT * FROM `resources` WHERE (submitted_at < created_at)