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

Questions / Help
1

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,
  name: t.name,
  assigned_to: [%{u.last_name, u.email}, %{u.last_name, u.email}]
}

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,
    name: t.name,
    assigned_to: [
      %{name: u.last_name, email: u.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
2

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
3

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.all(Tag)
|> Repo.preload(:users)
|> Enum.map(fn tag -> 
  %{
    description: tag.description,
    name: tag.name,
    assigned_to: Enum.map(tag.users, &%{name: &1.last_name, email: &1.email})
  }
end)
4

Thanks a lot, Matt! I appreciate it!

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

Thank you so much again!