为什么这个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屋!