How do I return the value of a foreign key in the JSON response rather than the ID?

Questions / Help
,
1

I’m experimenting with using Phoenix as a JSON API but I am struggling with returning a value in a field that is using a primary key rather than the id integer in the column.

I currently have 2 tables:

players (id, name, first_team_id, created_at, inserted_at)
teams (id, name, created_at, inserted_at)

A key was created via my migration file:

 def change do
    alter table(:players) do
      add :first_team_id, references(:teams)
    end

added to my schema:

  schema "players" do
    field :name, :string
    field :first_team_id, :integer
  
    timestamps()
  end

and my view:

  def render("player.json", %{player: player}) do
    %{id: player.id,
      name: player.name,
      first_team_id: player.first_team_id}
  end

I’m trying to recreate a response like you would get from:

SELECT players.name, teams.name as first_name  FROM players
LEFT JOIN teams ON players.first_team_id = teams.id;

I think there are a few different options:

  1. Use Ecto.query instead of Repo.get and recreate the above query
  2. Pipe the result of Repo.get into another function to add the correct value from the teams DB
  3. Handle the view in a different way that I’m not aware of

This obviously isn’t a unique problem but I can’t find any answers. What is the most Phoenix/functional way of approaching this?

2

You are not loading the team along with the player. You need a join first.

3

Hi dimitar, so there isn’t anything in Ecto that will do this for me, I should go ahead and run my own ecto.query?

4

There are such mechanisms in Ecto (joins). There are much more complex ones, too.

I’m on my phone now and can’t help. Can get back to you in a few hours with a solution.

5

Your player schema should be:

  schema "players" do
    field :name, :string
    belongs_to :first_team, YourApp.Team
    timestamps()
  end

Then, somewhere in your controller:

id = 123 # this should come in the parameters of your controller functions
player_query = from(p in YourApp.Player,
  where: p.id == ^id,
  left_join: team in assoc(p, :first_team),
  preload: [first_team: first_team]
)
player = Repo.one(player_query)

Then this player variable will have a first_team object filled in. If you only want the player name and team name, you can append select: %{player_name: p.name, team_name: team.name} after the preload clause above.

6

Thank you for the code samples, I appreciate you taking the time to do that for me.

I didn’t realise I needed the belongs_to in the player schema because I thought that had to be matched with a reciprocal has_many which I don’t have because one player can only have one first team.

I implemented the following in my controller after making the change to the schema:

alias Fcareers.Players
alias Fcareers.Players.Player

 def list_players do
  query = from(p in Player,
  left_join: teams in assoc(p, :first_team),
  preload: [first_team: first_team])

    Repo.all(query)
  end

However on compile I receive an unfamiliar error:

unbound variablefirst_teamin query. If you are attempting to interpolate a value, use ^var

I can tell I’m misunderstanding something fundamental about the nature of this kind of join behavior (either how to implement it in the schema or in a query) but I find the documentation quite difficult to follow unless you already understand it well!

7

It should be

preload: [first_team: ^first_team])

That means You want to ensure first_team is not rebound…

By the way, where is first_team defined?

8

Thanks kokolegorille, adding the carat cleared that error and led me to:

lib/fcareers/players/players.ex:24: undefined function first_team/0

which I suspect is what you expected given your question about where first_team is defined. The answer is I am not really sure? I have a first_team_id field in my players table as per my OP and a teams table with an id and name column.

I’ve managed to obtain the correct result with an explicit query:

query = from p in "players",
  join: t in  "teams",
  on: t.id == p.first_team_id,
  select: %{id: p.id, name: p.name, first_team: t.name}

  Repo.all(query)

But this doesn’t feel very ‘Elixery’!

9

As long as it looks like Linq You are good to go :slight_smile: