Rails3 and ActiveRecord with legacy database: JOIN not returning other table columns

Question

I have a legacy database that contains two different tables (tbl_players and tbl_player_ratings) that link to one another on a common column (player_key).

My problem: With Rails3, when I attempt to retrieve PlayersRatings joined to Players, only columns from PlayerRatings are returned. However, if I execute the same SQL query at the mysql command line, columns from both tables are returned.

For simplicity, I here provide just a few of the columns from each table.

tbl_players

player_key, last_name

tbl_player_ratings

player_key, rating

My Rails classes representing these tables look as follows:

class PlayerRating < ActiveRecord::Base
  establish_connection :legacy
  set_table_name 'tbl_player_ratings'
  set_primary_key "player_key"
  belongs_to :player,
             :foreign_key => 'player_key'
end

class Player < ActiveRecord::Base
  establish_connection :legacy
  set_table_name 'tbl_players'
  set_primary_key "player_key"
  has_many :player_ratings,
           :foreign_key => 'player_key'
end

The query that I'm running in the rails console:

PlayerRating.joins(:player).select("*").limit(1)

This returns a sole PlayerRating without any Player fields represented.

The SQL produced by the above rails command:

SELECT * FROM `tbl_player_ratings` INNER JOIN `tbl_players` ON `tbl_players`.`player_key` = `tbl_player_ratings`.`player_key` LIMIT 1

When I execute that exact command at the mysql command-line, all columns in both tables are returned.

Why does Rails and ActiveRecord not do the same (return all columns in both tables)?

Answer 1

A PlayerRating is only a PlayerRating object; you cannot coerce it into being a combination PlayerRating-Player via a joins(:player) on the query.

Following that query, you will have access to the player via player_rating.player.

array_of_player_ratings = PlayerRating.joins(:player).limit(any_number) 
single_player_rating = PlayerRating.joins(:player).first 
player = single_player_rating.player

Do look at the duplicate question linked for a fuller description of the difference between SQL queries and ActiveRecord queries.