Active Record Query Interface

Some context to begin with

This article is inspired by a real problem I’ve once been tasked with. Dealing with it taught me a lot, and I still have it as a reference in my mind. I think examples are a good learning resource, they can clarify a lot. In this article, I’d like to share with you some examples of using the Active Record query methods.

In order not to introduce domain-specific details, I’ll use a sample application for a library to illustrate examples. It’s all fairly simple, as shown in the diagram below. We have four tables: authors, books, users and rentals. One user can borrow many books and one book can be borrowed by many users, so we need a joining table to model many-to-many relationships. It’s the rentals table in our case. We also store some additional info there, which are the dates of borrowing and return. An author can have many books assigned to their name. A book also has an attribute defining its genre.

User reading statistics

The task was to prepare statistics for a single user, so we could tell how many books from each genre were borrowed. My first thought was to fetch all books which have been borrowed by the user, group them by their genre and then do the mapping, so each genre has a number of books assigned instead of a list. Here is what I came up with:

Hash[Book.joins(:rentals).where(rentals: { user: user }).group_by(&:genre).map { |genre, books| [genre, books.size] }]

While this approach works and looks clean, it doesn’t use all the possibilities that Active Record query methods offer. Thanks to them, we’re able to filter and aggregate data on a database level without using raw SQL directly in our code. Operating on a db level also increases our efficiency.

In the example above, we can use the group method instead of the Ruby’s groupby method. It will apply tthe GROUPBY clause to the tSQL query. Furthermore, the mapping and size method can be replaced with a count aggregation function. In the end, we are left with a query that looks like this:

Book.joins(:rentals).where(rentals: { user: user }).group(:genre).count(:books)

It looks even simpler!

Other useful examples

Below you’ll find some other ways of using the query methods which I find worth knowing.

Invitation for inactive users

TASK: Filter users who have never borrowed a book or did it more than a year ago.

We could fetch all users by including the associated rentals and then filter them using the select method.

User.includes(:rentals).select do |user|
 user.rentals.empty? || user.rentals.none? { |rental| rental.start_date >= Date.today - 1.year }
end 

But, of course, there is no need to fetch everything. By using the query methods, we can filter it out on the db level. First, let’s select users who have borrowed some books in the last year and then exclude them from the final selection.

This is how the subquery will look like:

User.joins(:rentals).where(rentals: { start_date:  (Date.today - 1.year).Date.today })

And now, let’s put it all together:

User.where.not(id: [User.joins(:rentals).where(rentals: { start_date:  (Date.today - 1.year)..Date.today })])

Underrepresented authors

TASK: Get authors with one or zero borrowed books

Doing it with the select method is super simple, but again – there is no need to operate on such a big set of data as the db can filter it for us:

Author.includes(:books).select { |author| author.books.size <= 1 }

Then, filtering authors with zero books assigned is quick and easy:

Author.left_joins(:books).where(books: { id: nil })

It’s important to remember one thing while using the left_joins (and outer joins in general). If there are records in the left table (here: authors) that don’t have corresponding records in the right table (here: books), then in result the right table columns will be populated with nil values.

As we also need authors with one book assigned in the system, there are a few more operations to do. We’ll have to do the grouping, counting and adding a condition. Here is how to put it all together:

Author.left_joins(:books).group(:id).having("count(*) <= ?", 1)

The condition comes after the aggregation function, so we have to use the HAVING clause, instead of the WHERE clause to specify it.

The Active Record query methods are worth checking when thinking about application performance. They can simplify your code and make it work faster. I hope that the shared examples will help you in exploring the possibilities the query methods have to offer.

Read more:

– Time for a new reality. An era of remote work has started a month ago

– Need to use the common JS frameworks in your Rails app? Stimulus.js may be an alternative

– Web App Development: Why is Ruby on Rails a technology worth choosing?