{"id":412524,"date":"2024-06-29T22:35:41","date_gmt":"2024-06-29T22:35:41","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=412524"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=412524","title":{"rendered":"<span>Enhanced ActiveRecord preloading<\/span>"},"content":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<ul>\n<li>\n<p>Do you like <code>ActiveRecord<\/code> preloading?<\/p>\n<\/li>\n<li>\n<p>How many times have you resolved your N+1 issues with <code>includes<\/code> or <code>preload<\/code>?<\/p>\n<\/li>\n<li>\n<p>Do you know that preloading has limitations?<\/p>\n<\/li>\n<\/ul>\n<p>In this guide, I&#8217;d like to share with you tips and tricks about ActiveRecord preloading and how you can enhance it to the next level.<\/p>\n<p>Let&#8217;s start by describing the models.<\/p>\n<pre><code class=\"ruby\"># The model represents users in our application. class User &lt; ActiveRecord::Base   # Every user may have from 0 to many payments.   has_many :payments end  # The model represents payments in our application. class Payment &lt; ActiveRecord::Base   # Every payment belongs to a user.   belongs_to :user end<\/code><\/pre>\n<p>Assuming we want to iterate over a group of users and check how many payments they have, we may do:<\/p>\n<pre><code class=\"ruby\"># The query we want to use to fetch users from the database. users = User.all # Iteration over selected users. users.each do |user|   # Print amount of user's payments.    # This query will be called for every user, bringing an N+1 issue.   p user.payments.count end<\/code><\/pre>\n<p>We can fix the N+1 issue above in a second. We need to add ActiveRecord&#8217;s <code>includes<\/code> to the query that fetches users.<\/p>\n<pre><code class=\"ruby\"># The query to fetch users with preload payments for every selected user. users = User.includes(:payments).all<\/code><\/pre>\n<p>Then, we can iterate over the group again without the N+1 issue.<\/p>\n<pre><code class=\"ruby\">users.each do |user|   p user.payments.count end<\/code><\/pre>\n<p>Experienced with ActiveRecord person may notice that the iteration above still will have an N+1 issue. The reason is the <code>.count<\/code> method and its behavior. This issue brings us to the first tip.<\/p>\n<h4>Tip 1. count vs size vs length<\/h4>\n<ul>\n<li>\n<p><code>count<\/code> &#8212; always queries the database with <code>COUNT<\/code> query;<\/p>\n<\/li>\n<li>\n<p><code>size<\/code> &#8212; queries the database with <code>COUNT<\/code> only when there is no preloaded data, returns array length otherwise;<\/p>\n<\/li>\n<li>\n<p><code>length<\/code> &#8212; always returns array length, in case there is no data, load it first.<\/p>\n<\/li>\n<\/ul>\n<p><em>Note:<\/em> be careful with <code>size<\/code> as ordering is critical.<\/p>\n<p>Meaning, for <code>user = User.first<\/code><\/p>\n<pre><code class=\"ruby\"># Does `COUNT` query user.payments.size # Does `SELECT` query user.payments.each { |payment| }<\/code><\/pre>\n<p>is different from<\/p>\n<pre><code class=\"ruby\"># Does `SELECT` query user.payments.each { |payment| } # No query user.payments.size<\/code><\/pre>\n<p>You may notice that the above solution loads all payment information when the amount is only needed. There is a well-known solution for this case called <a href=\"https:\/\/guides.rubyonrails.org\/association_basics.html#options-for-belongs-to-counter-cache\" rel=\"noopener noreferrer nofollow\">counter_cache<\/a>.<\/p>\n<p>To use that, you need to add <code>payments_count<\/code> field to <code>users<\/code> table and adjust <code>Payment<\/code> model.<\/p>\n<pre><code class=\"ruby\"># Migration to add `payments_count` to `users` table. class AddPaymentsCountToUsers &lt; ActiveRecord::Migration   def change     add_column :users, :payments_count, :integer, default: 0, null: false   end end  # Change belongs_to to have counter_cache option. class Payment &lt; ActiveRecord::Base   belongs_to :user, counter_cache: true end<\/code><\/pre>\n<p><em>Note:<\/em> avoid adding or removing payments from the database directly or through <code>insert_all<\/code>\/<code>delete<\/code>\/<code>delete_all<\/code> as <code>counter_cache<\/code> is using ActiveRecord callbacks to update the field&#8217;s value.<\/p>\n<p>It&#8217;s worth mentioning <a href=\"https:\/\/github.com\/magnusvk\/counter_culture\" rel=\"noopener noreferrer nofollow\">counter_culture<\/a> alternative that has many features compared with the built-in <code>counter_cache<\/code><\/p>\n<h3>Associations with arguments<\/h3>\n<p>Now, let&#8217;s assume we want to fetch the number of payments in a time frame for every user in a group.<\/p>\n<pre><code class=\"ruby\">from = 1.months.ago to = Time.current # Query to fetch users. users = User.all users.each do |user|   # Print the number of payments in a time frame for every user.   # Database query will be triggered for every user, meaning it has an N+1 issue.   p user.payments.where(created_at: from...to).count end<\/code><\/pre>\n<p>ActiveRecord supports defining associations with arguments.<\/p>\n<pre><code class=\"ruby\">class User &lt; ActiveRecord::Base   has_many :payments, -> (from, to) { where(created_at: from...to) } end<\/code><\/pre>\n<p>Unfortunately, such associations are not possible to preload with <code>includes<\/code>. Gladly, there is a solution with <a href=\"https:\/\/github.com\/djezzzl\/n1_loader\/\" rel=\"noopener noreferrer nofollow\">N1Loader<\/a>.<\/p>\n<pre><code class=\"ruby\"># Install gem dependencies. require 'n1_loader\/active_record'  class User &lt; ActiveRecord::Base   n1_optimized :payments_count do     argument :from     argument :to     def perform(users)       # Fetch the payment number once for all users.      Payment.where(user: users).where(created_at: from...to).group(:user_id).count         users.each do |user|        # Assign preloaded data to every user.         # Note: it doesn't use any promises.        fulfill(user, payments[user.id])      end     end   end end  from = 1.month.ago to = Time.current # Preload payments N1Loader \"association\". Doesn't query the database yet. users = User.includes(:payments_count).all users.each do |user|   # Queries the database once, meaning has no N+1 issues.   p user.payments_count(from, to) end<\/code><\/pre>\n<p>Let&#8217;s look at another example. Assuming we want to fetch the last payment for every user. We can try to define scoped <code>has_one<\/code> association and use that.<\/p>\n<pre><code class=\"ruby\">class User &lt; ActiveRecord::Base   has_one :last_payment, -> { order(id: :desc) }, class_name: 'Payment' end<\/code><\/pre>\n<p>We can see that preloading is working.<\/p>\n<pre><code class=\"ruby\">users = User.includes(:last_payment) users.each do |user|   # No N+1. Last payment was returned.   p user.last_payment end<\/code><\/pre>\n<p>At first glance, we may think everything is alright. Unfortunately, it is not.<\/p>\n<h4>Tip 2. Enforce has_one associations on the database level<\/h4>\n<p>ActiveRecord, fetches all available payments for every  user with provided order and then assigns only first payment to the  association. First, such querying is inefficient as we load many redundant  information. But most importantly, this association may lead to big issues. Other  engineers may use it, for example, for <code>joins(:last_payment)<\/code>. Assuming that association has strict agreement on the database level that a user may have none or a single payment in the database. Apparently, it may not be the case, and some queries will return unexpected data.<\/p>\n<p>Described issues may be found with <a href=\"https:\/\/github.com\/djezzzl\/database_consistency\" rel=\"noopener noreferrer nofollow\">DatabaseConsistency<\/a>.<\/p>\n<p>Back to the task, we can solve it with <a href=\"https:\/\/github.com\/djezzzl\/n1_loader\" rel=\"noopener noreferrer nofollow\">N1Loader<\/a> in the following way<\/p>\n<pre><code class=\"ruby\">require 'n1_loader\/active_record'  class User &lt; ActiveRecord::Base   n1_optimized :last_payment do |users|     subquery = Payment.select('MAX(id)').where(user: users)     payments = Payment.where(id: subquery).index_by(&amp;:user_id)          users.each do |user|       fulfill(user, payments[user.id])     end   end end  users = User.includes(:last_payment).all  users.each do |user|   # Queries the database once, meaning no N+1.   p user.last_payment end<\/code><\/pre>\n<p>Attentive reader could notice that in every described  case, it was a requirement to explicitly list data that we want to  preload for a group of users. Gladly, there is a simple solution! <a href=\"https:\/\/github.com\/DmitryTsepelev\/ar_lazy_preload\" rel=\"noopener noreferrer nofollow\">ArLazyPreload<\/a> will make N+1 disappear just by enabling it. As soon as you need to load association for any record, it will load it once for all records that were fetched along this one. And it works with ActiveRecord and N1Loader perfectly!<\/p>\n<p>Let&#8217;s look at the example.<\/p>\n<pre><code class=\"ruby\"># Require N1Loader with ArLazyPreload integration require 'n1_loader\/ar_lazy_preload'  # Enable ArLazyPreload globally, so you don't need to care about `includes` anymore ArLazyPreload.config.auto_preload = true  class User &lt; ActiveRecord::Base   has_many :payments    n1_optimized :last_payment do |users|     subquery = Payment.select('MAX(id)').where(user: users)     payments = Payment.where(id: subquery).index_by(&amp;:user_id)      users.each do |user|       fulfill(user, payments[user.id])     end   end end  # no need to specify `includes` users = User.all  users.each do |user|   p user.payments # no N+1   p user.last_payment # no N+1 end<\/code><\/pre>\n<p>As you can see, there is no need to even remember about resolving N+1 when you have both <a href=\"https:\/\/github.com\/DmitryTsepelev\/ar_lazy_preload\" rel=\"noopener noreferrer nofollow\">ArLazyPreload<\/a> and <a href=\"https:\/\/github.com\/djezzzl\/n1_loader\" rel=\"noopener noreferrer nofollow\">N1Loader<\/a> in your pocket. It works great with GraphQL API too. Give it and try and share your feedback!<\/p>\n<\/div>\n<\/div>\n<\/div>\n<p><!----><!----><\/div>\n<p><!----><!----><br \/> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/articles\/666736\/\"> https:\/\/habr.com\/ru\/articles\/666736\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<div><!--[--><!--]--><\/div>\n<div id=\"post-content-body\">\n<div>\n<div class=\"article-formatted-body article-formatted-body article-formatted-body_version-2\">\n<div xmlns=\"http:\/\/www.w3.org\/1999\/xhtml\">\n<ul>\n<li>\n<p>Do you like <code>ActiveRecord<\/code> preloading?<\/p>\n<\/li>\n<li>\n<p>How many times have you resolved your N+1 issues with <code>includes<\/code> or <code>preload<\/code>?<\/p>\n<\/li>\n<li>\n<p>Do you know that preloading has limitations?<\/p>\n<\/li>\n<\/ul>\n<p>In this guide, I&#8217;d like to share with you tips and tricks about ActiveRecord preloading and how you can enhance it to the next level.<\/p>\n<p>Let&#8217;s start by describing the models.<\/p>\n<pre><code class=\"ruby\"># The model represents users in our application. class User &lt; ActiveRecord::Base   # Every user may have from 0 to many payments.   has_many :payments end  # The model represents payments in our application. class Payment &lt; ActiveRecord::Base   # Every payment belongs to a user.   belongs_to :user end<\/code><\/pre>\n<p>Assuming we want to iterate over a group of users and check how many payments they have, we may do:<\/p>\n<pre><code class=\"ruby\"># The query we want to use to fetch users from the database. users = User.all # Iteration over selected users. users.each do |user|   # Print amount of user's payments.    # This query will be called for every user, bringing an N+1 issue.   p user.payments.count end<\/code><\/pre>\n<p>We can fix the N+1 issue above in a second. We need to add ActiveRecord&#8217;s <code>includes<\/code> to the query that fetches users.<\/p>\n<pre><code class=\"ruby\"># The query to fetch users with preload payments for every selected user. users = User.includes(:payments).all<\/code><\/pre>\n<p>Then, we can iterate over the group again without the N+1 issue.<\/p>\n<pre><code class=\"ruby\">users.each do |user|   p user.payments.count end<\/code><\/pre>\n<p>Experienced with ActiveRecord person may notice that the iteration above still will have an N+1 issue. The reason is the <code>.count<\/code> method and its behavior. This issue brings us to the first tip.<\/p>\n<h4>Tip 1. count vs size vs length<\/h4>\n<ul>\n<li>\n<p><code>count<\/code> &#8212; always queries the database with <code>COUNT<\/code> query;<\/p>\n<\/li>\n<li>\n<p><code>size<\/code> &#8212; queries the database with <code>COUNT<\/code> only when there is no preloaded data, returns array length otherwise;<\/p>\n<\/li>\n<li>\n<p><code>length<\/code> &#8212; always returns array length, in case there is no data, load it first.<\/p>\n<\/li>\n<\/ul>\n<p><em>Note:<\/em> be careful with <code>size<\/code> as ordering is critical.<\/p>\n<p>Meaning, for <code>user = User.first<\/code><\/p>\n<pre><code class=\"ruby\"># Does `COUNT` query user.payments.size # Does `SELECT` query user.payments.each { |payment| }<\/code><\/pre>\n<p>is different from<\/p>\n<pre><code class=\"ruby\"># Does `SELECT` query user.payments.each { |payment| } # No query user.payments.size<\/code><\/pre>\n<p>You may notice that the above solution loads all payment information when the amount is only needed. There is a well-known solution for this case called <a href=\"https:\/\/guides.rubyonrails.org\/association_basics.html#options-for-belongs-to-counter-cache\" rel=\"noopener noreferrer nofollow\">counter_cache<\/a>.<\/p>\n<p>To use that, you need to add <code>payments_count<\/code> field to <code>users<\/code> table and adjust <code>Payment<\/code> model.<\/p>\n<pre><code class=\"ruby\"># Migration to add `payments_count` to `users` table. class AddPaymentsCountToUsers &lt; ActiveRecord::Migration   def change     add_column :users, :payments_count, :integer, default: 0, null: false   end end  # Change belongs_to to have counter_cache option. class Payment &lt; ActiveRecord::Base   belongs_to :user, counter_cache: true end<\/code><\/pre>\n<p><em>Note:<\/em> avoid adding or removing payments from the database directly or through <code>insert_all<\/code>\/<code>delete<\/code>\/<code>delete_all<\/code> as <code>counter_cache<\/code> is using ActiveRecord callbacks to update the field&#8217;s value.<\/p>\n<p>It&#8217;s worth mentioning <a href=\"https:\/\/github.com\/magnusvk\/counter_culture\" rel=\"noopener noreferrer nofollow\">counter_culture<\/a> alternative that has many features compared with the built-in <code>counter_cache<\/code><\/p>\n<h3>Associations with arguments<\/h3>\n<p>Now, let&#8217;s assume we want to fetch the number of payments in a time frame for every user in a group.<\/p>\n<pre><code class=\"ruby\">from = 1.months.ago to = Time.current # Query to fetch users. users = User.all users.each do |user|   # Print the number of payments in a time frame for every user.   # Database query will be triggered for every user, meaning it has an N+1 issue.   p user.payments.where(created_at: from...to).count end<\/code><\/pre>\n<p>ActiveRecord supports defining associations with arguments.<\/p>\n<pre><code class=\"ruby\">class User &lt; ActiveRecord::Base   has_many :payments, -> (from, to) { where(created_at: from...to) } end<\/code><\/pre>\n<p>Unfortunately, such associations are not possible to preload with <code>includes<\/code>. Gladly, there is a solution with <a href=\"https:\/\/github.com\/djezzzl\/n1_loader\/\" rel=\"noopener noreferrer nofollow\">N1Loader<\/a>.<\/p>\n<pre><code class=\"ruby\"># Install gem dependencies. require 'n1_loader\/active_record'  class User &lt; ActiveRecord::Base   n1_optimized :payments_count do     argument :from     argument :to     def perform(users)       # Fetch the payment number once for all users.      Payment.where(user: users).where(created_at: from...to).group(:user_id).count         users.each do |user|        # Assign preloaded data to every user.         # Note: it doesn't use any promises.        fulfill(user, payments[user.id])      end     end   end end  from = 1.month.ago to = Time.current # Preload payments N1Loader \"association\". Doesn't query the database yet. users = User.includes(:payments_count).all users.each do |user|   # Queries the database once, meaning has no N+1 issues.   p user.payments_count(from, to) end<\/code><\/pre>\n<p>Let&#8217;s look at another example. Assuming we want to fetch the last payment for every user. We can try to define scoped <code>has_one<\/code> association and use that.<\/p>\n<pre><code class=\"ruby\">class User &lt; ActiveRecord::Base   has_one :last_payment, -> { order(id: :desc) }, class_name: 'Payment' end<\/code><\/pre>\n<p>We can see that preloading is working.<\/p>\n<pre><code class=\"ruby\">users = User.includes(:last_payment) users.each do |user|   # No N+1. Last payment was returned.   p user.last_payment end<\/code><\/pre>\n<p>At first glance, we may think everything is alright. Unfortunately, it is not.<\/p>\n<h4>Tip 2. Enforce has_one associations on the database level<\/h4>\n<p>ActiveRecord, fetches all available payments for every  user with provided order and then assigns only first payment to the  association. First, such querying is inefficient as we load many redundant  information. But most importantly, this association may lead to big issues. Other  engineers may use it, for example, for <code>joins(:last_payment)<\/code>. Assuming that association has strict agreement on the database level that a user may have none or a single payment in the database. Apparently, it may not be the case, and some queries will return unexpected data.<\/p>\n<p>Described issues may be found with <a href=\"https:\/\/github.com\/djezzzl\/database_consistency\" rel=\"noopener noreferrer nofollow\">DatabaseConsistency<\/a>.<\/p>\n<p>Back to the task, we can solve it with <a href=\"https:\/\/github.com\/djezzzl\/n1_loader\" rel=\"noopener noreferrer nofollow\">N1Loader<\/a> in the following way<\/p>\n<pre><code class=\"ruby\">require 'n1_loader\/active_record'  class User &lt; ActiveRecord::Base   n1_optimized :last_payment do |users|     subquery = Payment.select('MAX(id)').where(user: users)     payments = Payment.where(id: subquery).index_by(&amp;:user_id)          users.each do |user|       fulfill(user, payments[user.id])     end   end end  users = User.includes(:last_payment).all  users.each do |user|   # Queries the database once, meaning no N+1.   p user.last_payment end<\/code><\/pre>\n<p>Attentive reader could notice that in every described  case, it was a requirement to explicitly list data that we want to  preload for a group of users. Gladly, there is a simple solution! <a href=\"https:\/\/github.com\/DmitryTsepelev\/ar_lazy_preload\" rel=\"noopener noreferrer nofollow\">ArLazyPreload<\/a> will make N+1 disappear just by enabling it. As soon as you need to load association for any record, it will load it once for all records that were fetched along this one. And it works with ActiveRecord and N1Loader perfectly!<\/p>\n<p>Let&#8217;s look at the example.<\/p>\n<pre><code class=\"ruby\"># Require N1Loader with ArLazyPreload integration require 'n1_loader\/ar_lazy_preload'  # Enable ArLazyPreload globally, so you don't need to care about `includes` anymore ArLazyPreload.config.auto_preload = true  class User &lt; ActiveRecord::Base   has_many :payments    n1_optimized :last_payment do |users|     subquery = Payment.select('MAX(id)').where(user: users)     payments = Payment.where(id: subquery).index_by(&amp;:user_id)      users.each do |user|       fulfill(user, payments[user.id])     end   end end  # no need to specify `includes` users = User.all  users.each do |user|   p user.payments # no N+1   p user.last_payment # no N+1 end<\/code><\/pre>\n<p>As you can see, there is no need to even remember about resolving N+1 when you have both <a href=\"https:\/\/github.com\/DmitryTsepelev\/ar_lazy_preload\" rel=\"noopener noreferrer nofollow\">ArLazyPreload<\/a> and <a href=\"https:\/\/github.com\/djezzzl\/n1_loader\" rel=\"noopener noreferrer nofollow\">N1Loader<\/a> in your pocket. It works great with GraphQL API too. Give it and try and share your feedback!<\/p>\n<\/div>\n<\/div>\n<\/div>\n<p><!----><!----><\/div>\n<p><!----><!----><br \/> \u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 <a href=\"https:\/\/habr.com\/ru\/articles\/666736\/\"> https:\/\/habr.com\/ru\/articles\/666736\/<\/a><br \/><\/br><\/br><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-412524","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/412524","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=412524"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/412524\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=412524"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=412524"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=412524"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}