In this post i'll go through how to write efficient queries for belongs_to associations. I primarly work with Rails so it will focus on that a bit but i'll share the SQL versions too so you can implement similarly everywhere.

Example domain model

Let's take 2 simple models.

Problem

Now we have to find all people who belong to a billable role(when billable is true basically).

Solution

The simplest way is to use Ruby Enumerable method like

Person.all.select { |person| person.role.billable? }

This will work but it's not optimal as it will hit the database for each person(could be millions). Also it gets a lot of useless data from the role table which we don't need.

Here what we want is for the database to do the heavy lifiting instead. We can use join here and filter out the role using where clause.

With Activerecord

Person.all.joins(:role).where(roles: { billable: true })

which will generate a SQL like:

Here we have succesfuly used out belongs_to association by writing a good SQL query. These are very small and simple things one must keep in mind to keep their Rails or any other app fast and robust.