How do I get each user's role names?

I use following command to get users.

users = Repo.all(from u in User, order_by: [desc: u.id], limit: 2, preload: [:roles])

Following is the result

iex(5)> users = Repo.all(from u in User, order_by: [desc: u.id], limit: 2, preload: [:roles])
[debug] QUERY OK source="users" db=32.8ms decode=2.2ms queue=45.1ms idle=1775.2ms
SELECT u0."id", u0."email", u0."hashed_password", u0."confirmed_at", u0."inserted_at", u0."updated_at" FROM "users" AS u0 ORDER BY u0."id" DESC LIMIT 2 []
↳ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:312
[debug] QUERY OK source="roles" db=22.2ms queue=0.8ms idle=1898.4ms
SELECT r0."id", r0."name", r0."inserted_at", r0."updated_at", u1."user_id"::bigint FROM "roles" AS r0 INNER JOIN "users_roles" AS u1 ON r0."id" = u1."role_id" WHERE (u1."user_id" = ANY($1)) ORDER BY u1."user_id"::bigint [[4, 5]]
↳ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:312
[
  #Hello.Accounts.User<
    __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
    id: 5,
    email: "abc@email.com",
    confirmed_at: ~N[2023-07-17 05:37:19],
    roles: [],
    inserted_at: ~N[2023-07-17 05:36:55],
    updated_at: ~N[2023-07-17 05:37:19],
    ...
  >,
  #Hello.Accounts.User<
    __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
    id: 4,
    email: "test@email.com",
    confirmed_at: nil,
    roles: [
      %Hello.Accounts.Role{
        __meta__: #Ecto.Schema.Metadata<:loaded, "roles">,
        id: 2,
        name: "admin",
        users: #Ecto.Association.NotLoaded<association :users is not loaded>,
        inserted_at: ~N[2023-07-17 08:11:36],
        updated_at: ~N[2023-07-17 08:11:36]
      }
    ],
    inserted_at: ~N[2023-07-16 08:37:59],
    updated_at: ~N[2023-07-16 08:37:59],
    ...
  >
]
iex(6)>

How do I get each user’s role names?

Hi, please copy and paste text instead of using screenshots.

1 Like

Many thanks for your reply, I updated my post for the command result.

1 Like

You can select users role in the query select section and map to the roles’ name there. Mapping in the select section is supported according to the documentation: Ecto.Query.API — Ecto v3.11.1

From the documentation:

from p in Post,
select: map(p, [:title, :body])

I have never tried it but I guess, maybe this is what you want:

users = Repo.all(
from u in User,
order_by: [desc: u.id],
limit: 2,
preload: [:roles],
select: map(u.roles, [:name])
)

EDIT

Maybe I am understanding the ‘map’ instruction wrong and it is as easy as:

users = Repo.all(
from u in User,
order_by: [desc: u.id],
limit: 2,
preload: [:roles],
select: u.roles |> Enum.map(fn r → r.name end)
)

1 Like

When you say “get” what do you want to do with the role names? As @JeyHey notes you can query just the name out. Alternatively if you want both the user and the name, then you can manipulate the data with Enum functions. But it depends a lot on what you want the end result to be.

Can you show an example of what end result you want to have?

1 Like

Many thanks for your reply, I want to show user’s role name like following.

Can you not just do?

<%= @user.role.name %>

You are already preloading, so this should get you the name of the role for the user.

Edit. I’m assuming based on your image you have a loop like the below. You don’t need the @user if that’s the case.

<%= for user <- users do %>
<%= user.role.name %>
<% end %>
1 Like

Many thanks for your reply. finally I solve the question as following.

for user <- users do
  user.roles|> Enum.map(fn role -> role.name end)
end