Rails: Optimize query N+1 with same table one_to_many and scope conditions

Question

Here's a challenge I'm facing, using Rails 5 (I use Skylight service that reports N+1 queries, and their recommended solution is here, but it's not enough in my case).

I have a table nodes and a single Node can have several nodes related to it (there's a column called parent_node_id) that gives me the ability to relate one to many.

class Node < ApplicationRecord
  ...
  belongs_to :parent_node, foreign_key: :parent_node_id, class_name: 'Node', optional: true, inverse_of: :nodes
  has_many :nodes, foreign_key: :parent_node_id, class_name: 'Node'
  ...
end

Important The level of hierarchy is maximum 1. That means that a node.nodes.first.node does not happen. A node that has a parent_node does not have any more nodes.

Issue is that I'm facing performance issues with N+1 because it's not enough to include the nodes in the original query, because inside the loop I query each record with a different scope. Here's a sample code that exposes the issue:

# In a controller, including nodes so it does not query inside
nds = Node.some_scope.include(:nodes)
nds.each do |nd|
  ...
  # In a model
  # If I loop inside, there's no extra SQL queries, all good
  nd.nodes.each do |nd2|
    ...
  end
  ...
  # In a model
  # Here's the N+1 issue
  nd.nodes.another_scope.each do |nd3|
    ...
  end
  # Returns a value to the controller
  ...
end

That will anyway triggers the SQL queries for each nd3 variable since there's another_scope that modifies the original nds values, and I can not include the condition in the nds values because the nodes that don't meet the criteria in another_scope are needed for nd2.

Is there a way to optimize this?

Answer 1

Replace another_scope with select { |n| n.satisfies_another_scope_criteria? }

Since you've already fetched all children, there's no need to filter them in the database once again unless there's a limit clause in another_scope