by Matt Polito on June 9, 2022

I'd like to take things a bit back to basics on some commonly used utilities in Rails but dive a bit deeper than surface level. Today's journey will start with the seemingly elusive 'less than' query in ActiveRecord.

Building an ActiveRecord query can be very intuitive... when you're looking for equality.

A basic query will look like this:

User.where(login_count: 5).to_sql

=> SELECT "users".* from "users" WHERE "users"."login_count" = 5

However, what happens when we're looking to build something like a 'less than' query?

Building an ActiveRecord query can be very intuitive... when you're looking for equality.

A basic query will look like this:

User.where(login_count: 5).to_sql

=> SELECT "users".* from "users" WHERE "users"."login_count" = 5

However, what happens when we're looking to build something like a 'less than' query?

Let's look at the many ways this could be achieved:

User.where("login_count < 5").to_sql

=> SELECT "users".* from "users" WHERE (login_count < 5)

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.

User.where(User.arel_table[:login_count].lt(5)).to_sql

=> SELECT "users".* from "users" WHERE ("users"."login_count" < 5)

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 lt (less 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.

User.where(login_count: Float::INFINITY...5).to_sql

=> SELECT "users".* from "users" WHERE ("users"."login_count" < 5)

Up until a few years ago, I didn't even think ActiveRecord had a mechanism to create a less than query with the key/value syntax until I stumbled upon this accidentally.

Honestly it's cool but kind of confusing until you really think about what is trying to be conveyed... then is kinda makes sense. We're utilizing a Range as our value which, in a lot of cases, will create a BETWEEN SQL query. In this case, we're utilizing Infinity. So by having a range with Infinity being our begin and an end of a value, we're saying that the range represents 'less than [VALUE]'.

Another key thing to note is that we're using a triple dot range syntax. Think about the third dot as taking the place of the value after it. This is how we end up with a 'less than' vs a 'less than or equal' query.

Remember about nine words ago when I told you there was a subtle difference between the queries when passed an inclusive vs exclusive range?

Let's take a look at that

User.where(login_count: Float::INFINITY..5).to_sql

=> SELECT "users".* from "users" WHERE ("users"."login_count" <= 5)

Well there you have it... a less than or equal query using ActiveRecord's key/value syntax.

Photo by @k8_iv