Got nil in ecto query

Questions / Help
,
1

Hello there, i have some problems with my query. I have two schemas: User (:user) and UserDetails (:users_details, belongs_to :user, User). And I have a function in which i want to fetch result from query, but got nil.

def get_details_by_user(user_id) do
query =
from user_id in Project.Accounts.User,
join: user in assoc(user_id, :users_details),
where: user.id == ^user_id,
select: user

Repo.one(query)

end
Why am I getting nil while expecting a UserDetails structure?

Or is there another, better way to link these two tables and change the fields for UserDetails in my phoenix template without using queries?

2

Not sure if it affects anything, but you are using user_id identifier to both bind the user schema in the query from user_id in User and do the filtering user.id == ^user_id. And also you are only selecting the user schema and not user details which I guess is not what you want according to the function name.

Can you try this instead, what response do you get?

def get_details_by_user(user_id) do
  Project.Accounts.User
  |> where(id: ^user_id)
  # I prefer explicit joins, note I'm using a left join instead of inner here
  |> join(:left, [u], ud in Project.Accounts.UserDetails, on: ud.user_id == u.id)
  |> select([u, ud], %{user: u, details: ud})
  |> Repo.one()
end

The approach above is different in that I’m using a left join instead of an inner one which I think is the default when writing queries in the keyword syntax. I’m also specifying what to join on explicitly instead of relying on assoc helper, but that’s just a preference.

3

It seems like rather than getting the details of a user you are wanting to get a user with details.

def get_user_with_details(user_id) do
  from(user in User,
    where: user.id == ^user_id,
    left_join: details in assoc(user, :users_details),
    preload: :users_details
  )
  |> Repo.one()
end

The reason you might want a left join over an inner join is that if the given user has no profile an inner join would return nothing.

The preload will automatically add the details to the user without needing to define the select.

4

If you have a has_one association set up in your User schema, then you could just do this:

Project.Repo.preload(some_user, :details)

To set up that association, add this to your User schema:

has_one :details, Project.Accounts.UserDetails

(assuming user_details has a user_id on its schema that corresponds to users)

1 Like