Rails find record with join table and pluck single column-CodePudding

I have a many to many relationship:

Player 
  has_many :team_players
  has_many :teams, through: :team_players

Team
  has_many :team_players
  has_many :players, through: :team_players

TeamPlayer
  belongs_to :team
  belongs_to :player

One action a player can perform is to join a team. This just creates a new TeamPlayer record with a team_id player_id (and a status of 'active'). When a player joins a team, I want the endpoint to respond with the player all teams that the player is on.

At the time that the player joins the team, I have not retrieved the player record, I create the join record with the team_id player_id.

I'm aware I can get the player first, then do something like player.teams, but I'd like to learn how I can fetch the appropriate player (entire player record) with the teams records, AND pluck only the 'name' column from the Team table.

I tried: Player.find(player_id).joins(:teams).pluck(:name) but this doesn't work. I think the joins is in the wrong spot. I'm thinking something like this:

Player.joins(:teams).pluck(:name) but not sure where to put the player id. would it be a where clause?

I also saw syntax like this when searching: Player.teams.where... but not sure how to use that either.

so basically I want the end result to be something like this:

player {
  // player fields...
  teams: ['team-1', 'team-2', etc...],
}

EDIT

this query worked:

Player.where(id: player_id).includes(:teams).pluck(:name).first

but this only returns an array of team names (that the player is a part of). I want the teams list to be nested inside the player object

CodePudding user response：

Pluck is not what you want

pluck is used to immedidately fire a db query and get the selected column(s) only as an array. Except when its called on a relation thats already loaded and actually just works like #map.

Its a table based world

If you want to select muliple values from a joined table into a single column in the result you need an aggregate function. For example on Postgres you can select an array of all the team names with:

player = Player.left_joins(:teams)
           .group(:id)
           .select(
             'players.*',
             'json_agg(teams.name) AS team_names'
           ) 
           .find(1)

player.team_names # ["foo", "bar", "baz"]

On other databases you might need to use other aggregate functions. However you might want to leave this approach until you actually need it for performance reasons and use .includes / .eager_load.

Player.eager_load(:teams) actually selects the columns off both tables - by actually selecting one row for every team together with the columns from player:

SELECT "players"."id" AS t0_r0, 
       "players"."created_at" AS t0_r1, 
       "players"."updated_at" AS t0_r2, 
       "teams"."id" AS t1_r0, "teams"."name" AS t1_r1, 
       "teams"."created_at" AS t1_r2, "teams"."updated_at" AS t1_r3 
FROM "players" 
LEFT OUTER JOIN "team_players" ON "team_players"."player_id" = "players"."id" 
LEFT OUTER JOIN "teams" ON "teams"."id" = "team_players"."team_id"

Each row in the results will look something like:

t0_r0 (players.id) | t1_r0 (teams.id) | t1_r1 (teams.name) 
-------------------------------------------------------------
1                  | 1                | Arsenal
1                  | 2                | Tottenham
1                  | 3                | AC Milan
2                  | 4                | AFC Ajax
2                  | 1                | Arsenal

Rails will will actually loop through the results and stuff it all into Player and Team records for you so you can focus on more fun stuff:

class Player < ApplicationRecord
  # ...

  def team_names
    teams.map(&:name)
  end
end

player = Player.eager_load(:teams)
                .id(1)
puts player.team_names

Scoping assocations

All the above will still just get all the teams. If you just want active teams you can add additional assocations:

class Player < ApplicationRecord
  has_many :team_players
  has_many :teams, through: :team_players
  has_many :active_team_players,
    ->{ where(active: true ) },
    class_name: 'TeamPlayer' 
  has_many :active_teams, 
    through: :active_team_players,
    source: :team
end

When you join active_teams it will add a condition to the join clause:

irb(main):003:0> Player.joins(:active_teams)
  Player Load (0.8ms)  SELECT "players".* FROM "players" INNER JOIN "team_players" ON "team_players"."active" = $1 AND "team_players"."player_id" = "players"."id" INNER JOIN "teams" ON "teams"."id" = "team_players"."team_id"  [["active", true]]
=> []