Counting associated records with PostgreSQL and Rails

In this scenario we have two resources, Garage and Car. A Garage can have many cars and a Car belongs to a single Garage at any one time. That is to say the cars table holds the foreign key of the Garage it belongs to.

Counting with Rails

Previously, if I was tasked with fetching a count of all of the cars that were present in each of the garages I would have run some code that looked a little like this:

Garage.includes(:cars).map { |garage| { garage: garage.name, count: garage.cars.size }
#=> [{ garage: 'Upper Street', count: 1 }, { garage: 'Lower Street', count: 4 }, { garage: 'East Street', count: 3 }]

This is a very "railsy" way of performing a count, it will hit the database twice, once to fetch all garages and then again to fetch all cars associated with those garages (thanks to the includes method). However for each of these queries it pulls the entire record from the database each time. This is unnecessary as all we want is the count of the cars for each garage.

Counting with PostgreSQL

In order to perform the same count with PostgreSQL we can dot he following:

SELECT garages.name garage, COUNT(cars.id) FROM garages
INNER JOIN cars ON cars.garage_id = garages.id

This performs a single query that only pulls the data we need from the database (the name of the garage and the number of cars each one currently has in it).

Counting with PostgreSQL and Rails

So how do we leverage this more performant PostgreSQL count whilst inside the rails ecosystem? There are a couple of ways to do this. The most straightforward way is to execute the SQL statement directly against the database using ActiveRecord::Base.connection:

sql = <<- SQL
  SELECT garages.name garage, COUNT(cars.id) FROM garages
  INNER JOIN cars ON cars.garage_id = garages.id
SQL

ActiveRecord::Base.connection.execute(sql).to_a
#=> [{ garage: 'Upper Street', count: 1 }, { garage: 'Lower Street', count: 4 }, { garage: 'East Street', count: 3 }]

And there you have it. A reasonably straightforward way to count associated records in Rails whilst leveraging the true power and speed of PostgreSQL!