In order to make a single DB query, I am eager loading Posts along with their translation data (using Rails 6 and Mobility (*)), but instead it yields 2 SQL queries:
# app/models/post.rb
class Post < ApplicationRecord
extend Mobility
translates :title, :description, backend: :table
end
# app/controllers/posts_controller.rb
class PostsController < ApplicationRecord
def index
@posts = Post.eager_load(:translations).limit(3)
end
end
<%# app/views/posts/index.html.erb %>
<% @posts.each do |post| %>
<h1><%= post.title %></h1>
<div><%= post.description %></div>
<% end %>
Result:
- first, all Post IDs are selected
- then all attributes of posts with those IDs are returned, using WHERE IN
SELECT DISTINCT "posts"."id" FROM "posts"
LEFT OUTER JOIN "post_translations"
ON "post_translations"."post_id" = "posts"."id" LIMIT $1 [["LIMIT", 3]]
SELECT "posts"."id" AS t0_r0, "posts"."created_at" AS t0_r1, "posts"."updated_at" AS t0_r2, "post_translations"."id" AS t1_r0, "post_translations"."locale" AS t1_r1, "post_translations"."created_at" AS t1_r2, "post_translations"."updated_at" AS t1_r3, "post_translations"."title" AS t1_r4, "post_translations"."description" AS t1_r5, "post_translations"."post_id" AS t1_r6 FROM "posts"
LEFT OUTER JOIN "post_translations"
ON "post_translations"."post_id" = "posts"."id"
WHERE "posts"."id" IN ($1, $2, $3) [["id", "00060a7d-b846-5fc5-a372-1fc3462c695c"], ["id", "008db504-6fb4-5e90-bdca-4293ebe6d920"], ["id", "034944c1-4067-5ae5-89aa-4777ef14d66b"]]
How can this double SQL statement with in-memory of IDs be avoided?
(*) A note mon Mobility
The Mobility documentation has examples yielding a single SQL statement, but as pointed out by Chris Salzberg, its query API is not used at all in this example so should not be the culprit. To try to demonstrate that the issue might not be related to Mobility but Active Record itself, below is a somewhat equivalent code stripped off of Mobility, which shows the same double-querying issue (NB: this is just for demonstration purposes, as I do want to keep using Mobility):
class Post < ApplicationRecord
has_many :translations, ->{ where(locale: I18n.locale) }
%i(title description).each do |attr|
define_method(attr) do
translations.first.send(attr)
end
end
class Translation < ApplicationRecord; end
end
<%# app/views/posts/index.html.erb %>
<% Post.eager_load(:translations).limit(3).each do |post| %>
<h1><%= post.title %></h1>
<div><%= post.description %></div>
<% end %>
