How to get team name based on team id in an entity from another table?

Questions / Help
, ,
1

Hey I have a user entity in my postgres database and I am displaying it with the generated index method:

  def index(conn, _params) do
    users = Web.list_users()
    render(conn, "index.html", users: users)
  end

I have a field in it called teamid that is based on a selected team when the user is created.
how could i get the team name from another table based on the teamid? when i log out the user it looks like this:

[
  %Userteam1.Web.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 6,
inserted_at: ~N[2018-08-11 07:29:21.552256],
name: "ssd",
password: "dssd",
teamid: "1",
updated_at: ~N[2018-08-11 07:29:21.557296]
  },
  %Userteam1.Web.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 7,
inserted_at: ~N[2018-08-11 07:30:25.431934],
name: "kkjkj",
password: "kjkjjk",
teamid: "2",
updated_at: ~N[2018-08-11 07:30:25.436358]
  },
  %Userteam1.Web.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 8,
inserted_at: ~N[2018-08-11 07:42:05.148300],
name: "tr",
password: "tr",
teamid: "2",
updated_at: ~N[2018-08-12 06:07:00.772189]
  }
]
2

It looks like a good use case for ecto has_many and belongs_to.

But…

  1. teamid is not the usual way to represent a reference, it would be team_id. So You would need to specify foreign key

  2. It is a string, but it should be an integer, unless You are using uuid

If You have 2 schemas… User and Team, and You set associations, it will be easy to preload team data for users.

If You have a Team schema, You might as well try this

iex> import Ecto.Query
iex> team_id = String.to_integer(user.teamid)
iex> Team |> where(id: ^team_id)

# or simply if You have corresponding aliases

iex> Repo.get(Team, String.to_integer(user.teamid))
3

Hey thanks for the reply, yeah i tried to use has_many, belongs_to but couldn’t really see how would it work.
I even set it up, but I don’t know how to use it really.
you can see if you run my app, that a user can choose a team, that is selected from a dropdown that takes the items from the team table (the same for the role).
but I could grasp how to use has_many and belongs_to in my case
my code currently looks like this: https://github.com/benonymus/userteamrolebackend

4

Meaning what exactly? Does not the excellent Ecto documentation help?

1 Like
5

no, I mean how does that solve this issue exactly

6

If Ecto knows about how the two schemas are associated, then it will be able to automatically join the tables in queries when you include a preload instruction.

So, if you:

  1. Add has_many(:users, Userteam1.User) to your team schema;
  2. Add belongs_to(:team, Userteam1.Team) to your user schema;
  3. Remove the teamid field from the user migration;
  4. Add add(:team_id, references(:teams)) to your user migration;
  5. Replace :teamid in your user changeset cast and validate_required params to :team_id

then you should be able to get all users with team names as follows:

from(User, preload: [:team])
|> Repo.all()

I haven’t tested this code, so it’s not guaranteed to work, but it should point you in the right direction.

3 Likes
7

Sorry for the late reply but this way how will my team id saved to the team_id field?
because when my user is created it doesnt have that field

"user" => %{"id" => "1", "name" => "ds", "password" => "[FILTERED]", "role" => "1"}

also there is a schema in userteam1 folder and then inside the web folder too, should i update both? because even after i did it shows the same outcome.

so my user schema looks like this:

defmodule Userteam1.User do
  use Ecto.Schema
  import Ecto.Changeset

  schema "users" do
field(:name, :string)
field(:password, :string)
field(:role, :string)
belongs_to(:team, Userteam1.Team)

timestamps()
  end

  @doc false
  def changeset(user, attrs) do
IO.puts("xd")
IO.inspect(attrs)

user
|> cast(attrs, [:name, :password, :role, :team_id])
|> validate_required([:name, :password, :role, :team_id])
  end
end

and the schema loks the same in the web folder too.
but it never saves the user

8

Note that you should only have one schema module for User and one for Team, so somehow these have been duplicated in your project (maybe by running the generators twice?). Your migrations are duplicated as well. It will be simpler to have the migration for Team run before the migration for User as one of the fields in User relies on the Team table existing (otherwise you will need a third migration that amends the User table after the Team table has been created). You can try to delete the duplicate files, or maybe it’s easier to start again?

If your user is setup like this:

defmodule Userteam1.User do
  use Ecto.Schema
  import Ecto.Changeset

  schema "users" do
    field(:name, :string)
    field(:password, :string)
    field(:role, :string)
    belongs_to(:team, Userteam1.Team)
    timestamps()
  end

  def changeset(user, attrs) do
    user
    |> cast(attrs, [:name, :password, :role, :team_id])
    |> validate_required([:name, :password, :role, :team_id])
  end
end

then it will have a team and team_id field:

iex(1)> %Userteam1.User{}
%Userteam1.User{
  __meta__: #Ecto.Schema.Metadata<:built, "users">,
  id: nil,
  inserted_at: nil,
  name: nil,
  password: nil,
  role: nil,
  team: #Ecto.Association.NotLoaded<association :team is not loaded>,
  team_id: nil,
  updated_at: nil
}

and if your team is setup like this:

defmodule Userteam1.Team do
  use Ecto.Schema
  import Ecto.Changeset

  schema "teams" do
    field(:name, :string)
    has_many(:users, Userteam1.User)
    timestamps()
  end

  def changeset(team, attrs) do
    team
    |> cast(attrs, [:name])
    |> validate_required([:name])
  end
end

then it will have a users field:

iex(2)> %Userteam1.Team{}
%Userteam1.Team{
  __meta__: #Ecto.Schema.Metadata<:built, "teams">,
  id: nil,
  inserted_at: nil,
  name: nil,
  updated_at: nil,
  users: #Ecto.Association.NotLoaded<association :users is not loaded>
}

For this to work with the db, your migration for users should look like this (provided the migration for teams is before the migration for users, otherwise you will need to have two migrations for users with an alter table instruction in the second one):

defmodule Userteam1.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users) do
      add(:name, :string)
      add(:password, :string)
      add(:role, :string)
      add(:team_id, references(:teams))

      timestamps()
    end
  end
end

I forked your project on Github, made the changes above, and it worked okay.

3 Likes
9

I am checking it out now dude, thanks
Also I am thinking that those schemas in the web folder could be for the web view, since I am generating those too, these: https://github.com/benonymus/userteamrolebackend/tree/master/lib/userteam1/web
you can see them on localhost:4000 if you run the project with mix phx.server

10

running your version the web part of the app doesn’t really work, I am looking into it now

11

I have fixed the web part of the app in my forked version.

  1. I updated the web schemas with the changes I made earlier to the other schemas (I didn’t realize at first that your use of duplicate schemas was intentional);
  2. I updated list_users/0 to preload the teams field;
  3. Updated any display of teamid in the templates to team.name
  4. Updated the <select> element for team to reference team_id

It seems to be working well.

EDIT: Note that even if you want to use 2 schemas for User and 2 schemas for Team, each should still only have one migration as the duplicate schemas will be referencing the same db table. Currently you seem to have 2 migrations for each.

1 Like