Building greater than query in ActiveRecord
Previously I mentioned that I'd like to take a journey digging below the surface of some commonly used and very useful Ruby on Rails api.
Today's journey will be the inverse of building a 'less than' query in ActiveRecord... dun, dun, duuuuun... the 'greater than' query.
So that we're on the same page, let's review. Building an ActiveRecord query can be very intuitive... when you're looking for equality.
A basic query will look like this:
Crocodile.where(years_grown: 7).to_sql => SELECT "crocodiles".* from "crocodiles" WHERE "crocodiles"."years_grown" = 7
However, what happens when we're looking to build something like a 'greater than' query?
Let's look at the many ways this could be achieved:
The one with the SQL fragment
Crocodile.where("years_grown > 7").to_sql => SELECT "crocodiles".* from "crocodiles" WHERE (years_grown > 7)
Although this may be the most straight forward, I believe the next options are better overall. Let's just glance over escaped input in a SQL fragment for now.
The one with the Arel
Crocodile.where(Crocodile.arel_table[:years_grown].gt(7)).to_sql => SELECT "crocodiles".* from "crocodiles" WHERE ("crocodiles"."years_grown" > 7)
Arel is a SQL AST manager library that ActiveRecord uses to build queries.
where accepts many kinds of arguments... in this case it consumes an Arel Node. We use the
gt (greater than) predicate on the arel_table's column.
Notice how the generated statement has an explicitly declared column name vs the SQL fragment in the first example. This can really help when you're dealing with joins on a table that may have columns of the same name.
I find that my personal preference tends to lean toward utilizing Arel when I am unable to do something cleanly in ActiveRecord.
The one with the Range
Crocodile.where(years_grown: 7...Float::INFINITY).to_sql => SELECT "crocodiles".* from "crocodiles" WHERE ("crocodiles"."years_grown" >= 7)
That's not what I wanted. That gave me a 'greater than or equal' query result when I wanted a 'greater than'.
I'm using the range's 'triple dot' notation which would mean we're excluding infinity... hmm... can you exclude infinity?
Let's try including infinity:
Crocodile.where(years_grown: 7..Float::INFINITY).to_sql => SELECT "crocodiles".* from "crocodiles" WHERE ("crocodiles"."years_grown" >= 7)
THAT'S THE SAME QUERY!!!
If we were to break down what the range is trying to say, no matter how you write that, it is essentially saying give me a range from 7 to infinity. So, in reality, it does seem like 'give me something that is greater than or equal to 7'.
Well dangit, this doesn't look good. It appears that we cannot actually get a 'greater than' query from a range.
Passing a range can be a cool way to make a query work with ActiveRecord but I've found that more often than not it can lead to a confusing result especially when we start passing infinite ranges. YOU'VE BEEN WARNED.
It has been an interesting journey on this one but wanted to show that when you dig a bit deeper there can be several more ways to get the result you want. However, just below the surface there be
dragons crocodiles as well.