Read more

How to tell ActiveRecord how to preload associations (either JOINs or separate queries)

Henning Koch
July 01, 2013Software engineer at makandra GmbH

Remember why preloading associations "randomly" uses joined tables or multiple queries?

Illustration UI/UX Design

UI/UX Design by makandra brand

We make sure that your target audience has the best possible experience with your digital product. You get:

  • Design tailored to your audience
  • Proven processes customized to your needs
  • An expert team of experienced designers
Read more Show archive.org snapshot

If you don't like the cleverness of this behavior, you can explicitely tell ActiveRecord how to preload associations Show archive.org snapshot with either JOINs or separate queries:

  • joins will join the given association so you can order or add conditions.

    User.joins(:posts)
    

    Query:

    SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id"
    

    Note that records will not be preloaded that way. Accessing each user's posts will produce extra queries!

  • preload will simply preload associations in bulk, using extra queries:

    User.preload(:posts).to_a
    

    Query:

    SELECT "users".* FROM "users"
    SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" IN (1)
    

    Accessing each user's posts will not produce extra queries.

    If all you want is to preload associations, this is the fastest way, even though it produces additional queries.

  • eager_load will preload associations in bulk, explicitly using a LEFT OUTER JOIN:

    User.eager_load(:posts).to_a
    

    Query:

    SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "posts"."id" AS t1_r0,
           "posts"."title" AS t1_r1, "posts"."user_id" AS t1_r2, "posts"."desc" AS t1_r3
    FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"
    

    Note that this will not work when preloading polymorphic associations because of joining.

  • includes will preload associations like preload ...

    User.includes(:posts).to_a
    

    Query:

    SELECT "users".* FROM "users"
    SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" IN (1)
    

    ... and switch to a JOIN if necessary:

    User.includes(:posts).where('posts.id > 10').to_a
    

    Query:

    SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "posts"."id" AS t1_r0,
           "posts"."title" AS t1_r1,
           "posts"."user_id" AS t1_r2, "posts"."desc" AS t1_r3
    FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"
    WHERE (posts.id > 10)
    

    Using includes usually works nicely, but Rails will apply some magic, as mentioned at the beginning of this card.

    This is subject to issues with polymorphic associations, too.

Henning Koch
July 01, 2013Software engineer at makandra GmbH
Posted by Henning Koch to makandra dev (2013-07-01 10:42)