To include or to join

Take this rails/squeel code:

UserProperty.includes(:user).where{user.id != nil}

Sometimes it works and sometimes it doesn't. Why? Under the hood, rails makes a decision. Should we do this with an eager_load or a preload? What does this mean?

If we change it to:

UserProperty.eager_load(:user).where{user.id != nil}

We get:

SELECT "user_properties"."id" AS t0_r0,
       "user_properties"."user_id" AS t0_r1,
       "user_properties"."property_id" AS t0_r2,
       "user_properties"."created_at" AS t0_r3,
       "user_properties"."updated_at" AS t0_r4,
       "user_properties"."position" AS t0_r5,
       "users"."id" AS t1_r0,
       "users"."email" AS t1_r1,
       "users"."encrypted_password" AS t1_r2,
       "users"."reset_password_token" AS t1_r3,
       "users"."reset_password_sent_at" AS t1_r4,
       "users"."remember_created_at" AS t1_r5,
       "users"."sign_in_count" AS t1_r6,
       "users"."current_sign_in_at" AS t1_r7,
       "users"."last_sign_in_at" AS t1_r8,
       "users"."current_sign_in_ip" AS t1_r9,
       "users"."last_sign_in_ip" AS t1_r10,
       "users"."created_at" AS t1_r11,
       "users"."updated_at" AS t1_r12,
FROM "user_properties"
LEFT OUTER JOIN "users" ON "users"."id" = "user_properties"."user_id"
WHERE ("users"."id" IS NOT NULL)

Rails grabs the user_properties and the users in one query. Because there is a join with users, this sql executes.

If we change to:

UserProperty.preload(:user).where{user.id != nil}

We get:

SELECT "user_properties".* FROM "user_properties" WHERE ("user"."id" IS NOT NULL)

That's not going to work as the sql does not join with users.

Here's the code that's guaranteed to work and eagerly load users:

UserProperty.includes(:user).joins(:user).where{user.id != nil}

Most often you shouldn't think about whether to do an eager_load or a preload. Just leave it up to rails.

If you are querying based on criteria of an association, use joins. If you want to eagerly load an association, use includes. If you want to do both, do both.

kbaum/Include_or_join.md