How can I retrieve a relationated entity but also edit which fields to select?

Phoenix Forum Questions / Help
, ,
1

I have a entity X that has_many of entity Y. I create a base query to retrieve X and then make a join to get Y and then in select I choose which fields I want from both X and Y, however this results in duplicated data.

Example:

from(x in X, as: :x)
|> join(:inner, [x], y in assoc(x, :y), as: :y)
|> select(%{
  id: as(:x).y,
  y: [%{
    id: as(:y)/id,
    status: as(:y).status
  }]
})

result:

[
  %X{id: 1, y: [%Y{id: 1, status: "pending"}]},
  %X{id: 1, y: [%Y{id: 2, status: "canceled"}]},
  %X{id: 1, y: [%Y{id: 3, status: "running"}]},
]

I want tio achieve this result:

[
  %X{id: 1, y: [
    %Y{id: 1, status: "pending"},
    %Y{id: 2, status: "canceled"},
    %Y{id: 3, status: "running"}
  ]},
]

how can I translate this to Ecto.Query? I tried to sue a subquery but I don’t know if it’s the best option

2

edit: misread

3

sorry, is something unclear?

4

I don’t think it’s possible to even get that from SQL

select(%{
  id: as(:x).y,
  y: [%{
    id: as(:y)/id,
    status: as(:y).status
  }]

However, once you managed to get your data, then you can use Elixir to parse your results from your query and parse them as you wish.

BUT don’t believe. I let someone else to answer this.

2 Likes
5

I would also recommend this, but in case you really really need those SQL call to return exactly like that, i think possibly you could get it via combination of array_agg / json_agg fragment, wrapping query via subquery.

1 Like