Manually preloading associations in Rails using custom scopes/SQL

In rare cases, you may need to manually preload associations in Rails for performance reasons. For example, you might have a situation where you can fetch multiple associations from the database in one query, or you need to do something that can’t be accomplished with Rails’ built-in support for eager loading.

First, let’s look at some situations where you might not know that it can be done with Rails’ built-in functionality.

Preloading / eager loading only one record from an association

For example you might have a Person that has_many photos, but when listing these people you only want to display the first photo. Firstly, you might think that this will solve it:

It will appear as if it’s working, but the problem is that Rails will still preload all photos for each Person, not only the first one. You might think that a limit condition on the association would help, but it cannot, since Rails uses a query like the following for preloading:

SELECT “photos”.* FROM “photos” WHERE “photos”.”person_id” IN (1,2,3,4,5)

So, if you would stick a LIMIT at the end of that, it would not be limiting on a per-person basis. Here is what we can do to preload only the first photo:

We use PostgreSQL’s DISTINCT ON to only select one photo for each person_id. Because of this we also need to add person_id as the first column to order by (this is required by postgres, and in Rails 4 orders are prepended to existing ones so this will be the first one). You can order descending if you need it to be the last photo. If you need to use more specific conditions, you can use additional scopes, however note that Rails will ignore everything except for: where, select, references, and includes.

We need to set select_values directly because DISTINCT ON must appear before specifying which columns to select.

Also, if you want to use a scope that you have defined on Image, you might have to do a little hack to be able to use that scope:

Note that the same limitations apply on the types of scopes that you can use (so no group scope for example).

Manually preloading

You can look into code in active_record/associations/preloader/ to see more on how Rails does preloading, but basically, you need to do this:

For my reference, here is another more complex way to accomplish the same thing by creating a custom preloader and calling it:

About these ads

6 thoughts on “Manually preloading associations in Rails using custom scopes/SQL

  1. Mmmmmm maybe this is not a real problem you are discussing, but imho there is no way ever to determine a display_photo uniquely. I would write this differently using a belongs_to relation, adding the display_photo_id to the Person model.

    • You can determine it uniquely, you just need to add orders to get what you need (i.e. you might want to order by photos created_at). DISTINCT ON will take the first row according to how you ordered them.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s