Query problem join return list

Questions / Help
1

Hi, i have problem with simply query…

I have 2 tables:

users
field :user, :string
field :items, {:array, :integer}

items
field :item_id, :integer
field :value, :string

My query:

       from u in User,
        join: i in Items,
        on: i.item_id in u.items,
        select: %{
          user: u.user,
          items: i.value,
        }

It’s work but if i have more then one items (in users) i receive list of maps (i need return all data in one map)

Example:

[
%{ 
 user: user1,
 items: value1
 },
 %{ 
 user: user1,
 items: value2
 }
 ]

But i need:

 %{ 
 user: user1,
 items: [value1, value2]
 }
2

A possibility would be to group_by User. Here are the docs: https://hexdocs.pm/ecto/Ecto.Query.html#group_by/3

2 Likes
3

When i use group_by i get error

i1.value must appear in the GROUP BY clause or be used in an aggregate function

from u in User,
        join: i in Items,
        on: i.item_id in u.items,
        select: %{
          user: u.user,
          items: i.value,
        },
group_by: u.id
4 
from u in User,
  join: i in Items,
  on: i.item_id in u.items,
  select: %{
    user: u.user,
    items: fragment("array_agg(?)", i.value)
  },
  group_by: u.id
3 Likes
5

Or you solve this in Elixir instead:

from(u in User,
  join: i in Items,
  on: i.item_id in u.items,
  select: %{
    user: u.user,
    value: i.value
  }
)
|> Repo.all()
|> Enum.group_by(& &1.user)
|> Enum.map(fn {user, values} -> %{user: user, values: Enum.map(values, & &1.value)} end)
1 Like
6

Thank you, it’s work :grinning:

1 Like