Query by Duration in Active Record

In this tutorial, we’ll learn how to query and group records by duration using Active Record. If at any point you wish to explore on your own, simply clone or fork the example repository on which this post references. Let’s get cooking! Below are the models and their associations we’ll be using for this exercise. class Recipe < ApplicationRecord has_many :steps end class Step < ApplicationRecord belongs_to :recipe end It might be tempting to store the duration as an integer in a durationinseconds column. However, PostgreSQL provides a better solution to this problem with its interval datatype. What’s more, Rails provides an abstraction around this datatype that can be used in migrations. class CreateSteps < ActiveRecord::Migration[7.0] def change create_table :steps do |t| t.interval :duration ... end end end This is not only semantically correct, but also makes for a cleaner interface. It means we can add records like this: step = Step.new(duration: 10.minutes) step.duration # => 10.minutes Let’s start simple and query for steps by their duration. Step.where(duration: 10.minutes) # => [#<Step>] This is easy because we can query against the table that has the duration without needing to run any calculations. However, that will just query for all records whose duration is exactly 10 minutes. Passing an endless range will use a comparison operator. Step.where(duration: ..10.minutes) # => [#<Step>, #<Step>] Note that the use of two dots will result in a less than or equal comparison, while the use of three dots will in a less than comparison. Step.where(duration: ..10.minutes).to_sql # => SELECT "steps".* FROM "steps" WHERE "steps"."duration" <= 'PT10M' Step.where(duration: ...10.minutes).to_sql # => SELECT "steps".* FROM "steps" WHERE "steps"."duration" < 'PT10M' If you cannot use a hash condition, you’ll need to cast 10.minutes to iso8601 so that it will be compatible with the PostgreSQL interval output. Step.where("duration >= ?", 10.minutes.iso8601) # => [#<Step>, #<Step>] If you call to_sql you can see that the comparison is made against PT10M since this is what PostgreSQL expects. Step.where("duration >= ?", 10.minutes.iso8601).to_sql # => SELECT "steps".* FROM "steps" WHERE (duration >= 'PT10M') 10.minutes.iso8601 # => "PT10M" Let’s turn up the heat by querying for recipes by their duration. This is more challenging because the recipe does not have a duration column. Not only that, but a recipe has many steps, not just one, and each step could have a duration. class Recipe < ApplicationRecord has_many :steps def self.with_duration_less_than, -> (duration){ joins(:steps) .group(:id) .having("SUM(steps.duration) <= ?", duration.iso8601) } end Recipe.with_duration_less_than(60.minutes) # => [#<Recipe>, #<Recipe>] The use of joins allows access to the associated steps table, which in turn allows access to the duration column. From there, we can call having to filter out rows that do not meet the specified criteria. Now for the pièce de résistance: Let’s group recipes by their duration. By using a combination of group and sum while leveraging order we can group recipes by their duration sorted from quickest to longest. class Recipe < ApplicationRecord has_many :steps def self.by_duration joins(:steps) .group(:name) .order("SUM(steps.duration) ASC") .sum(:duration) end end Recipe.by_duration # => {"Recipe Two"=>5 minutes, "Recipe One"=>25 minutes} Check out our cookbook for more active record recipes! If you enjoyed this post, you might also like: