How can I prevent n+1 issue from array that is generating additional conditions?

Question

I'm facing n+1 issue when using conditions on array that comes from includes query.

Here are the following tables:

rems
|id| |name|
 1  aaa
 2  bbb

rem_correlatives
|id| |rem_id| |name|  |deleted|  |pending_tr|
 1     1       qqqq1      0          0
 2     1       qqqq2      0          1 
 3     1       qqqq1      1          0
 4     1       qqqq2      1          1
 5     1       qqqq1      0          0
 6     1       qqqq2      0          1 
 7     1       qqqq1      1          0
 8     1       qqqq2      1          1
 9     2       qqqq1      0          0
 10    2       qqqq2      0          1 
 11    2       qqqq1      1          0
 12    2       qqqq2      1          1
 13    2       qqqq1      0          0
 14    2       qqqq2      0          1 
 15    2       qqqq1      1          0
 16    2       qqqq2      1          1

Here are the models:

 class Rem < ApplicationRecord
    has_many :rem_correlatives
 end

 class RemCorrelative < ApplicationRecord
    belongs_to :rem
 end

Here is the controller called rems_controller.rb from the location /app/controllers/rems_controller.rb

 def index
    @list_array = Rem.includes(:rem_correlatives).all
 end

Here is the index view from the location /app/views/rems/index.html.erb

 <% @list_array.each do |array| %>
      <% array.name %>
      <% @check_result = array.rem_correlatives.where("deleted=0 AND pending_tr= 1)%>
        <% if @check_result.present? %>
           No
        <% else %>
           Yes
        <% end %>
      <% end %>
   
 <% end%>

I tried this workaround code that display data array using columns pending_tr=1 and deleted=0 but seems not to be a good practice.

 <% @list_array.each do |array| %>
      <% array.name %>
      <% array.rem_correlatives.each do |rem_correlative|%>
        <!--  Create condition pending_tr=1 AND deleted=0  -->
        <% if rem_correlative.pending_tr == 1 && rem_correlative.deleted == 0%>
           <% @check_condition = "No" %>
        <% else %>
           <% @check_condition = "Yes"%>
        <% end %>
      <% end %>
      <!--  Check results from array if the word yes exists --> 
      <% if @check_condition.include?("Yes") %>
         si 
      <% else %>
        no
      <% end %>          
 <% end%>

Here is the backend result when using the workaround code, its working and not displaying n+1 issue.

How can I prevent n+1 issue from array that is generating additional conditions as good code?

See Specifying Conditions on Eager Loaded Associations (or use joins) — Stefan
– Stefan, Commented Nov 17, 2023 at 7:06

spickermann · Accepted Answer · 2023-11-17 07:11:31Z

1

I would create a dedicated association with a scope in the model.

# in app/models/rem.rb
has_many :pending_rem_correlatives,
  class_name: 'RemCorrelative', -> { where(deleted: false, pending_tr: true) }

And would then use this association in the controller and view.

# in the controller
@rems = Rem.includes(:pending_rem_correlatives).all

# in the view
<% @rems.each do |rem| %>
  <%= rem.name %>

  <% if rem.pending_rem_correlatives.any? %>
    No such rem correlatives
  <% else %>
    There are matching rem correlatives
  <% end %>
<% end %>

answered Nov 17, 2023 at 7:11

spickermann

108k9 gold badges115 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Carlos Morales Over a year ago

HI spickermann i tried your code and it is still generating n+1

spickermann Over a year ago

When you still have subqueries then it is likely because of the other subconditions in the view that you mentioned in a comment above. Would you mind sharing your actual code or at least an example that is closer to reality?

user3402754 · Accepted Answer · 2023-11-17 07:15:52Z

1

Currently, in your controller, you are using Rem.includes(:rem_correlatives).all to load the Rem records with their associated rem_correlatives. However, when you later iterate over @list_array and access array.rem_correlatives, it still generates additional queries for each Rem record, causing the N+1 query problem.

To address this, you can preload the necessary associations using the includes method and also leverage a query condition to filter the associated records. Here's how you can modify your controller code:

def index
  @list_array = Rem.includes(:rem_correlatives)
                  .where("rem_correlatives.deleted = 0 AND rem_correlatives.pending_tr = 1")
                  .references(:rem_correlatives)
                  .all
end

With this change, in your view, you can iterate over @list_array without causing additional queries for each Rem record:

<% @list_array.each do |array| %>
  <%= array.name %>
  <% if array.rem_correlatives.present? %>
    No
  <% else %>
    Yes
  <% end %>
<% end %>

answered Nov 17, 2023 at 7:15

user3402754

3,1052 gold badges21 silver badges33 bronze badges

1 Comment

Carlos Morales Over a year ago

I'm trying to use subconditions and don't want to touch the controller because i have other subconditions on the view.

Collectives™ on Stack Overflow

How can I prevent n+1 issue from array that is generating additional conditions?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related