15. Fast and easy way to optimise your Rails DB queries and Active Storage

This post is an extension to previous Active Storage post covered in section 7.

Here we will look at an advanced query and fine tuning it to remove N+1 queries from your Active Record calls.

As an example, you may have your base ActiveRecord class contain a basic has_one_attached relation like this:

class JobPost < ApplicationRecord
  ...
  belongs_to :organization_entity, optional: true
  has_one_attached :logo
  ...
end

And another class which can be related to your main class, such as in this case organization_entity.

class OrganizationEntity < ApplicationRecord
  ...
  has_many :job_posts, dependent: :nullify, foreign_key: :organization_entity_id
  has_one_attached :org_logo
  ...
end

Now if you want to load your main class with attached logos pre-loaded, you can use:

with_attached_logos

as defined in the documentation. So in the above example you can use:

JobPost.with_attached_logo.where(....)

Now, how about if you want to include another class alongside your main one?

You should use the basic includes() functionality:

JobPost.with_attached_logo.where(....).includes(:organization_entity).preload!

This will load the organisation entity, but it will not pre-load the attached org_logo which are attached to the organization_entity class.

You can easily verify this in logs, e.g. have a search mechanism like this:

  @pagy, @postings = pagy_countless(
    JobPost.with_attached_logo.search(search_params)
      .includes(:metadata)
      .includes(:keyword_tag)
      .includes(:organization_entity)
      .preload!,
    items: 20, 
    page: search_params[:page]
  )
  render json: JobPosting::BaseSerializer.new(@postings, meta: pagination_metadata)

then inside the base serialiser, stick a debugger to see what happens when it gets to each entry of the logo.

if object&.organization_entity&.org_logo.attached? && object&.organization_entity&.org_logo.variable?            
  debugger
  logo = Rails.application.routes.url_helpers.rails_representation_url(
    object.organization_entity.org_logo.variant(resize_to_limit: [LOGO_MAX_WIDTH, LOGO_MAX_HEIGHT]), only_path: true
  )
end

Notice that for each entry of the entity, it will make another query to the database, it will look something like this:

This is bad and is a clear case of an N+1 query.

How to resolve this includes() with attached objects to remove N+1 queries?

You can do this with the help of Reflection in rails. It’s much easier to understand with an example.

For the above example of OrganizationEntity here’s what the response of reflections would give:

OrganizationEntity.reflections.keys
["job_posts", .., "org_logo_attachment", "org_logo_blob"]

This essentially gives you the reflection name that you require to wire up your ActiveRecord query. In this case, we’re interested in org_logo_attachment entity.

Knowing this information, you can finally create the query which will preload your data, you will need to add:

.includes(organization_entity: [org_logo_attachment: :blob])

Where you will essentially create a nested includes(). The organisation entity will join on org_logo_attachment which will join on blob which is the data that you’re after when loading the attachments.

The final query will look something like this:

  @pagy, @postings = pagy_countless(
    JobPost.with_attached_logo.search(search_params)
      .includes(:metadata)
      .includes(:keyword_tag)
      .includes(organization_entity: [org_logo_attachment: :blob])
      .preload!,
    items: 20, 
    page: search_params[:page]
  )
  render json: JobPosting::BaseSerializer.new(@postings, meta: pagination_metadata)

Using the same debugger as before, we can verify that this is no longer an N+1 query.

So, just aim to include the relation that you require in your main query with this:

.includes(<your_entity>: [<attached_item_name>_attachment: :blob])

And this should include the relevant data in your query.