为什么这个Rails关联在急切加载后分别加载？ [英] why is this rails association loading individually after an eager load?

问题描述

我正在尝试避免由于急于加载而导致的N + 1查询问题，但是它无法正常工作。关联的模型仍在单独加载。

I'm trying to avoid the N+1 queries problem with eager loading, but it's not working. The associated models are still being loaded individually.

以下是相关的ActiveRecord及其关系：

Here are the relevant ActiveRecords and their relationships:

class Player < ActiveRecord::Base
  has_one :tableau
end

Class Tableau < ActiveRecord::Base
  belongs_to :player
  has_many :tableau_cards
  has_many :deck_cards, :through => :tableau_cards
end

Class TableauCard < ActiveRecord::Base
  belongs_to :tableau
  belongs_to :deck_card, :include => :card
end

class DeckCard < ActiveRecord::Base
  belongs_to :card
  has_many :tableaus, :through => :tableau_cards
end

class Card < ActiveRecord::Base
  has_many :deck_cards
end

class Turn < ActiveRecord::Base
  belongs_to :game
end

和查询I' m使用的是播放器的这种方法：

and the query I'm using is inside this method of Player:

def tableau_contains(card_id)
  self.tableau.tableau_cards = TableauCard.find :all, :include => [ {:deck_card => (:card)}], :conditions => ['tableau_cards.tableau_id = ?', self.tableau.id]
  contains = false
  for tableau_card in self.tableau.tableau_cards
    # my logic here, looking at attributes of the Card model, with        
    # tableau_card.deck_card.card;
    # individual loads of related Card models related to tableau_card are done here
  end
  return contains
end

与范围有关吗？这个tableau_contains方法在一个较大的循环中减少了一些方法调用，我最初尝试进行急切加载，因为在多个地方循环并检查了这些相同的对象。然后，我终于尝试了上面的代码，并在循环之前进行了加载，并且仍然在日志的tableau_cards循环内看到针对Card的各个SELECT查询。我也可以在tableau_cards循环之前看到带有IN子句的热切查询。

Does it have to do with scope? This tableau_contains method is down a few method calls in a larger loop, where I originally tried doing the eager loading because there are several places where these same objects are looped through and examined. Then I eventually tried the code as it is above, with the load just before the loop, and I'm still seeing the individual SELECT queries for Card inside the tableau_cards loop in the log. I can see the eager-loading query with the IN clause just before the tableau_cards loop as well.

编辑：下面带有较大的外部循环的附加信息

additional info below with the larger, outer loop

EDIT2：更正了以下带有答案提示的循环

EDIT2 : corrected loop below with tips from answers

EDIT3：添加了更多目标详细信息

EDIT3 : added more details in loop with goals

这是更大的循环。它位于after_save

Here's the larger loop. It is inside an observer on after_save

def after_save(pa)
  turn = Turn.find(pa.turn_id, :include => :player_actions)
  game = Game.find(turn.game_id, :include => :goals)
  game.players.all(:include => [ :player_goals, {:tableau => [:tableau_cards => [:deck_card => [:card]]]} ])
  if turn.phase_complete(pa, players)  # calls player.tableau_contains(card)
    for goal in game.goals
      if goal.checks_on_this_phase(pa)
        if goal.is_available(players, pa, turn)
          for player in game.players
            goal.check_if_player_takes(player, turn, pa)
              ... # loop through player.tableau_cards
            end
          end
        end
      end
    end
  end

以下是回合类中的相关代码：

Here's the relevant code in the turn class:

def phase_complete(phase, players)
  all_players_complete = true
  for player in players
    if(!player_completed_phase(player, phase))
      all_players_complete = false
    end
  end
  return all_players_complete
end

游戏中玩家的玩家正在执行另一个查询来加载玩家们。它被缓存了，我的意思是它在日志中有CACHE标签，但是我认为根本不会有任何查询，因为game.players应该已经加载到内存中。

the for player in game.players is doing another query to load the players. It is cached, I mean it has the CACHE label in the log, but I would've thought there would be no query at all because the game.players should already be loaded in memory.

目标模型的另一个摘要：

Another snippet from the Goal model:

class Goal < ActiveRecord::Base
  has_many :game_goals
  has_many :games, :through => :game_goals
  has_many :player_goals
  has_many :players, :through => :player_goals

  def check_if_player_takes(player, turn, phase)
    ...
    for tab_card in player.tableau_cards
    ...
  end
end

推荐答案

尝试一下：

class Game
  has_many :players
end

更改 tableau_contains 的逻辑，如下所示：

Change the logic of tableau_contains as follows:

class Player < ActiveRecord::Base
  has_one :tableau
  belongs_to :game

  def tableau_contains(card_id)
    tableau.tableau_cards.any?{|tc| tc.deck_card.card.id == card_id}
  end

end

按以下方式更改 after_save 的逻辑：

Change the logic of after_save as follows:

def after_save(turn)
  game = Game.find(turn.game_id, :include => :goals))
  Rails.logger.info("Begin  eager loading..")                
  players = game.players.all(:include => [:player_goals,
            {:tableau => [:tableau_cards=> [:deck_card => [:card]]]} ])
  Rails.logger.info("End  eager loading..")                
  Rails.logger.info("Begin  tableau_contains check..")                
  if players.any?{|player| player.tableau_contains(turn.card_id)}
    # do something..                
  end
  Rails.logger.info("End  tableau_contains check..")                
end

after_save 方法中的第二行渴望将所需的数据加载到执行 tableau_contains 检查。 tableau.tableau_cards 和 tc.deck_card.card 之类的调用应该/不会打入数据库。

Second line in the after_save method eager loads the data needed to perform the tableau_contains check. The calls such as tableau.tableau_cards and tc.deck_card.card should/will not hit the DB.

代码中出现的问题：

1）将数组分配给 has_many 关联

1) Assigning array to an has_many association

@game.players = Player.find :all, :include => ...

以上声明不是简单的赋值语句。它将使用给定游戏的 game_id 更改 Payers 表行。
我假设那不是您想要的。如果您检查数据库表，您会注意到玩家表
中的 updated_time 行在分配后已更改。

Statement above is not a simple assignment statement. It changes the palyers table rows with the game_id of the given game. I am assuming that is not what you want. If you check the DB table you will notice that the updated_time of the players table rows have changed after assignment.

您必须将值分配给一个单独的变量，如 after_save 方法中的代码示例所示。

You have to assign the value to a separate variable as shown in the code sample in after_save method.

2）手工编码的关联SQL

2) Hand coded association SQL

您在代码中的许多地方都为关联数据手工编码了SQL。 Rails为此提供了关联。

Many places in the code you are hand coding the SQL for association data. Rails provides associations for this.

例如：

tcards= TableauCard.find :all, :include => [ {:deck_card => (:card)}], 
         :conditions => ['tableau_cards.tableau_id = ?', self.tableau.id]

可以重写为：

tcards = tableau.tableau_cards.all(:include => [ {:deck_card => (:card)}])

<$ c上的 tableau_cards 卡关联$ c> Tableau 模型构造与您手工编码相同的SQL。

The tableau_cards cards association on Tableau model constructs the same SQL you have hand coded.

您可以通过在 Player中添加 has_many：through 关联来进一步改进上述语句类。

You can further improve the statement above by adding a has_many :through association to Player class.

class Player
  has_one :tableau
  has_many :tableau_cards, :through => :tableau
end

tcards = tableau_cards.all(:include => [ {:deck_card => (:card)}])

编辑1

我创建了一个应用程序来对此进行测试码。它按预期工作。 Rails运行一些SQL来渴望加载数据，即：

I created an application to test this code. It works as expected. Rails runs several SQL to eager load the data, i.e.:

Begin  eager loading..
SELECT * FROM `players` WHERE (`players`.game_id = 1) 
SELECT `tableau`.* FROM `tableau` WHERE (`tableau`.player_id IN (1,2))
SELECT `tableau_cards`.* FROM `tableau_cards` 
          WHERE (`tableau_cards`.tableau_id IN (1,2))
SELECT * FROM `deck_cards` WHERE (`deck_cards`.`id` IN (6,7,8,1,2,3,4,5))
SELECT * FROM `cards` WHERE (`cards`.`id` IN (6,7,8,1,2,3,4,5))
End  eager loading..
Begin  tableau_contains check..
End  tableau_contains check..

在急于加载数据之后，我看不到任何SQL执行。

I don't see any SQL executed after eager loading the data.

编辑2

对代码进行以下更改。

def after_save(pa)
  turn = Turn.find(pa.turn_id, :include => :player_actions)
  game = Game.find(turn.game_id, :include => :goals)
  players = game.players.all(:include => [ :player_goals, {:tableau => [:tableau_cards => [:deck_card => [:card]]]} ])
  if turn.phase_complete(pa, game, players)
    for player in game.players
      if(player.tableau_contains(card))
      ...
      end
    end
  end
end
def phase_complete(phase, game, players)
  all_players_complete = true
  for player in players
    if(!player_completed_phase(player, phase))
      all_players_complete = false
    end
  end
  return all_players_complete
end

缓存的工作方式如下：

game.players # cached in the game object
game.players.all # not cached in the game object

players = game.players.all(:include => [:player_goals])
players.first.player_goals # cached

上面的第二条结果在自定义关联查询中。因此，AR不会缓存结果。在使用标准关联SQL提取 player_goals 时，在第3条语句中为每个玩家对象缓存了它们。

The second statement above results in a custom association query. Hence AR doesn't cache the results. Where as player_goals are cached for every player object in the 3rd statement as they are fetched using standard association SQL.

这篇关于为什么这个Rails关联在急切加载后分别加载？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！