Rails 3 Including multiple tables in one query
Im working on a rails application for tournaments. I have three models im working with on this query:
class Player < ActiveRecord::Base
validates :name, :uniqueness => true
has_and_belongs_to_many :tournaments
class Tournament < ActiveRecord::Base
belongs_to :tournament_type
has_and_belongs_to_many :players
has_many :player_matches, :dependent => :destroy
class PlayerMatch < ActiveRecord::Base
belongs_to :player_one, :class_name => "Player", :foreign_key => "player_one"
belongs_to :player_two, :class_name => "Player", :foreign_key => "player_two"
In the show action of the tournaments_controller, im calling the following query:
Tournament.where(:id => params[:id]).includes(:player_matches, :players).first()
While the tournaments and player_matches are done in a single join, the players are queried individually, since my code depends on them:
Player Load (0.4ms) SELECT `players`.*, t0.tournament_id as the_parent_record_id FROM `players` INNER JOIN `players_tournaments` t0 ON `players`.id = t0.player_id WHERE (t0.tournament_id = 14)
Player Load (0.2ms) SELECT `players`.* FROM `players` WHERE `players`.`id` = 5 LIMIT 1
Player Load (0.2ms) SELECT `players`.* FROM `players` WHERE `players`.`id` = 9 LIMIT 1
Player Load (0.2ms) SELECT `players`.* FROM `players` WHERE `players`.`id` = 1 LIMIT 1
Player Load (0.1ms) SELECT `players`.* FROM `players` WHERE `players`.`id` = 8 LIMIT 1
Player Load (0.1ms) SELECT `players`.* FROM `players` WHERE `players`.`id` = 3 LIMIT 1
Player Load (0.1ms) SELECT `players`.* FROM `players` WHERE `players`.`id` = 2 LIMIT 1
Player Load (0.1ms) SELECT `players`.* FROM `players` WHERE `players`.`id` = 7 LIMIT 1
Player Load (0.1ms) SELECT `player开发者_如何学JAVAs`.* FROM `players` WHERE `players`.`id` = 6 LIMIT 1
Player Load (0.1ms) SELECT `players`.* FROM `players` WHERE `players`.`id` = 4 LIMIT 1
How can i change this up, so that it is all pulled in one query?
Tournament.where(:id => params[:id]).includes([{:player_matches => :player_one}, :players]).first()
That should do the trick. And really, the combination of array notation and hash notation is a bit messy to get sorted at first, but work with the console and you'll find out which syntax doesn't crash :)
精彩评论