ECTO: associated (belongs_to) schema with parent schema query - how to?

I have two tables - User and Tag

- User - has_many tags with fields :last_name and :email
- Tag - belongs_to User with fields: :name and :description 

I need to return Tag with information about who it assigned. So the DS should look like this assuming I have 1 Tag and 2 Users with this Tag

  description: t.description,
  assigned_to: [%{u.last_name,}, %{u.last_name,}]

So if I will build this query below, it will return, obviously 2 Users:

from(u in User,
  join: t in assoc(u, :tag),
  select: %{
    description: t.description,
    assigned_to: [
      %{name: u.last_name, email:}

Couldn’t figure out how to make it using Ecto only without extra data manipulation?!
Or that might be possible with SQL somehow?

Could anyone please give me a clue?

Thank you all in advance!

1 Like

Your design is probably wrong because if You use belongs_to, a tag will belongs to one user only…

If You want a tag to have multiple users, I suggest You look at many_to_many.

1 Like

Let’s think through the SQL in detail:

  • as seen in your example, you can get rows that consist of a user’s columns and a single tag’s columns together using JOIN
  • you want a single row per tag
  • therefore you need to combine all the JOIN rows for a single tag into a final result. That’s typically done with GROUP BY
  • for each group, you want an array of details (one entry per tag) - an aggregate function that can do this is ARRAY_AGG

Alternatively, you could load whole Tag structs and their Users, then transform the result:

|> Repo.preload(:users)
|> tag -> 
    description: tag.description,
    assigned_to:, &%{name: &1.last_name, email: &})

Thanks a lot, Matt! I appreciate it!

Yeah, I ended up with a SQL approach you described!

Thank you so much again!