by Matt Polito on June 14, 2022

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:

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.

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.

ActiveRecord's 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.

Crocodile.where(years_grown: 7...Float::INFINITY).to_sql

=> SELECT "crocodiles".* from "crocodiles" WHERE ("crocodiles"."years_grown" >= 7)

...wait

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.