image by Icons8 Team
Active Record‘s query interface has many different ways to turn your logic into SQL to get rows from your database.
I previously extolled the virtues of the core extensions to
Time and in our web applications we‘re often extracting records from databases where time periods are one of our primary filters. So let’s use those extensions to query our database.
…using a text-based SQL fragments to query time fields:
User.where("created_at > ? AND created_at < ?", 2.weeks.ago, 1.week.ago).to_sql #=> "SELECT ... WHERE (created_at > '2022-08-31 11:29:53.945995' AND created_at < '2022-09-07 11:29:53.946280')" User.where("created_at > ? AND created_at < ?", 2.weeks.ago.beginning_of_day, 2.weeks.ago.end_of_day).to_sql #=> "SELECT ... WHERE (created_at > '2022-08-31 00:00:00' AND created_at < '2022-08-31 23:59:59.999999')" User.where("created_at >= ?", 2.weeks.ago.beginning_of_day).to_sql #=> "SELECT ... WHERE \"users\".\"created_at\" >= '2022-08-31 00:00:00'"
Ranges of time passed as an argument.
User.where(created_at: (2.weeks.ago..1.week.ago)).to_sql #=> "SELECT ... WHERE \"users\".\"created_at\" BETWEEN '2022-08-31 11:29:33.248193' AND '2022-09-07 11:29:33.248938'" User.where(created_at: 2.weeks.ago.all_day).to_sql #=> "SELECT ... WHERE \"users\".\"created_at\" BETWEEN '2022-08-31 00:00:00' AND '2022-08-31 23:59:59.999999'" User.where(created_at: (2.weeks.ago.beginning_of_day..)).to_sql #=> "SELECT ... WHERE \"users\".\"created_at\" >= '2022-08-31 00:00:00'"
The ruby syntax in this case is shorter and clearer.
You’re also likely to make fewer mistakes as the SQL statement generated by the scope won‘t have a big splurge of your text inserted into it.
In addition the SQL generated by the framework when passed a range takes advantage of the correct
BETWEEN SQL syntax (at least in Postgres). This more accurately describes the behaviour and allows the underlying database to use it’s statisics and indexes to return results as quickly as possible from the resulting query.
Ranges to Active Record scopes seems less common than it should be. I presume this is because
String-based conditions are listed first in the guides documentation, and were—for early versions of Rails—the primary syntax.
There’s very little reason not to use this more modern syntax. It’s clearer to read and reason about when you’re using the features of Active Record than throwing
Strings around. That way it stands out more when you do have to drop down to
Strings for more complicated SQL queries.
My final example uses an endless
Range which was introduced in Ruby 2.6 (in 2018) and it took a little while to include into Rails so you might see examples that interplate
Strings to get the same effect.
If you’re regularly querying on a field it is probably worth investigating whether your database table could benefit from adding a database index.
Last updated on October 17th, 2022 by @andycroll
An email newsletter, with one Ruby/Rails technique delivered with a ‘why?’ and a ‘how?’ every two weeks. It’s deliberately brief, focussed & opinionated.