In our previous
post,
we’ve shown how to use the disable_joins
option for has_many: :through
association.
In this post, we would like to show, how that works for the has_one: :through
association as well.
Let’s consider the use case where:
- Users can create multiple posts.
- Anyone looking at the post can add a comment - crowd-sourced comments.
In this case, we can store the crowd-sourced comments in a separate database, as comments can grow quickly and may need a different kind of data management approach.
The database.yml
can be like below:
default: &default
adapter: postgresql
encoding: unicode
pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
username: root
password: password
development:
primary:
<<: *default
database: rails_demo_development
crowd_sourced:
<<: *default
migrations_paths: db/migrate_crowd_sourced
database: rails_demo_crowd_sourced_development
The models would look like below:
# app/models/application_record.rb
class ApplicationRecord < ActiveRecord::Base
self.abstract_class = true
connects_to database: { writing: :primary, reading: :primary }
end
# app/models/crowd_sourced_record.rb
class CrowdSourcedRecord < ActiveRecord::Base
self.abstract_class = true
connects_to database: { writing: :crowd_sourced, reading: :crowd_sourced }
end
# app/models/user.rb
class User < ApplicationRecord
has_many :posts
has_one :latest_post, -> { order('posts.created_at desc').limit(1) }, class_name: 'Post'
end
# app/models/user.rb
class Post < ApplicationRecord
belongs_to :user
has_many :comments
has_one :latest_comment, -> { order('comments.created_at desc').limit(1) }
end
# app/models/comment.rb
class Comment < CrowdSourcedRecord
end
Before
In Rails 6, to fetch the latest comment for a user, we need to add a custom method, as associations can’t join across databases.
# app/models/user.rb
class User < ApplicationRecord
has_many :posts
has_one :latest_post, -> { order('posts.created_at desc').limit(1) }, class_name: 'Post'
def latest_comment
Comment.order('comments.created_at desc').where(post_id: posts.pluck(:id)).limit(1)
end
end
Here, we are fetching the most recent comment created on the posts created by the user.
As comments are in different a database, need to pluck
the post ids.
> User.first.latest_comment
User Load (0.2ms) SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT $1 [["LIMIT", 1]]
(0.3ms) SELECT "posts"."id" FROM "posts" WHERE "posts"."user_id" = $1 [["user_id", 1]]
Comment Load (0.6ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN ($1, $2) /* loading for inspect */ ORDER BY comments.created_at desc LIMIT $3 [[nil, 1], [nil, 11], ["LIMIT", 1]]
=> #<ActiveRecord::Relation [#<Comment id: 51, post_id: 11, name: "Lesa Boyer", email: "nichol_oga@hahnpaucek.ca", comment: "Bad song!", created_at: "2021-05-25 10:15:12.527229000 +0000", updated_at: "2021-05-25 10:15:12.527229000 +0000">]>
For all such associations across different databases, we will have to add custom methods as above.
After
Rails 7 has
added
an option in has_one :through
association to make it easier to have multiple databases joining.
We can use the has_one :through
association with the newly added disable_joins: true
option like below.
We do need to add one association in the User
model for the latest_post
in this case.
class User < ApplicationRecord
has_many :posts
has_many :comments, through: :posts, disable_joins: true
has_one :latest_post, -> { order('posts.created_at desc').limit(1) }, class_name: 'Post'
has_one :latest_comment, through: :latest_post, disable_joins: true
end
class Post < ApplicationRecord
belongs_to :user
has_many :comments
has_one :latest_comment, -> { order('comments.created_at desc').limit(1) }, class_name: 'Comment'
end
If we try to load the latest_comment
association now, it fires 2 separate queries to load the post ids,
and the latest comment with those post ids.
> User.first.latest_comment
User Load (0.3ms) SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT $1 [["LIMIT", 1]]
Post Pluck (0.6ms) SELECT "posts"."id" FROM "posts" WHERE "posts"."user_id" = $1 ORDER BY posts.created_at desc [["user_id", 1]]
Comment Load (0.6ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN ($1, $2) ORDER BY comments.created_at desc LIMIT $3 [["post_id", 11], ["post_id", 1], ["LIMIT", 1]]
=> #<Comment id: 51, post_id: 11, name: "Lesa Boyer", email: "nichol_oga@hahnpaucek.ca", comment: "Bad song!", created_at: "2021-05-25 10:15:12.527229000 +0000", updated_at: "2021-05-25 10:15:12.527229000 +0000">
This allows us to eager load the latest comment.
> User.includes(:latest_comment)
User Load (0.3ms) SELECT "users".* FROM "users" /* loading for inspect */ LIMIT $1 [["LIMIT", 11]]
Post Load (0.3ms) SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) ORDER BY posts.created_at desc LIMIT $11 [["user_id", 1], ["user_id", 2], ["user_id", 3], ["user_id", 4], ["user_id", 5], ["user_id", 6], ["user_id", 7], ["user_id", 8], ["user_id", 9], ["user_id", 10], ["LIMIT", 1]]
Comment Load (0.3ms) SELECT "comments".* FROM "comments" WHERE "comments"."post_id" = $1 ORDER BY comments.created_at desc LIMIT $2 [["post_id", 11], ["LIMIT", 1]]
=> #<ActiveRecord::Relation [#<User id: 1, name: "Milton Dicki", email: "lyman@heller.us", created_at: "2021-04-20 17:13:20.492541000 +0000", updated_at: "2021-04-20 17:13:20.492541000 +0000">, #<User id: 2, name: "Crystle Davis", email: "lanie@quigley.biz", created_at: "2021-04-20 17:13:20.529307000 +0000", updated_at: "2021-04-20 17:13:20.529307000 +0000">, #<User id: 3, name: "Junita Sauer", email: "clayton@padberg.name", created_at: "2021-04-20 17:13:20.554967000 +0000", updated_at: "2021-04-20 17:13:20.554967000 +0000">, ...>]>