Problem with an Ecto.Query, retrieve a loaded assoc

Questions / Help
1

I have a problem, I wanted to load all the tags attached on a given dataset but I’m receiving a duplicated dataset for each tag, I think an example would clarify all.

I have a function like:

defp do_get_datasets_by_name_like!(str) do
    query_str = "%"<>str<>"%"
    from(
      d in Dataset,
      join: o in assoc(d, :owner),
      left_join: t in assoc(d, :tags),
      where: ilike(d.name, ^query_str),
      select: %{name: d.name, owner_name: o.name, owner_lasname: o.last_name, tags: t}
    )
    |> Repo.all()
  end

And I’m receiving a response like:

{
    "datasets": [
        {
            "name": "INH on Colombian patients",
            "owner_lasname": "Otalvaro",
            "owner_name": "Julian D.",
            "tags": {
                "name": "Anti-TB"
            }
        },
        {
            "name": "INH on Colombian patients",
            "owner_lasname": "Otalvaro",
            "owner_name": "Julian D.",
            "tags": {
                "name": "Colombia"
            }
        }
    ]
}

As you see, the dataset is duplicated because more than one tag belongs to it, I know I can write a different function that returns all the tags for the given dataset, but I was wondering if it’s possible to modify the query in order to receive the data as I want.

2

If you’re on Postgres, grouping by d.id and using array_agg would let you get an array in tags.

1 Like
3

Final solution thanks to @al2o3cr for the idea:

  defp do_get_datasets_by_name_like!(str) do
    query_str = "%"<>str<>"%"
    from(
      d in Dataset,
      join: o in assoc(d, :owner),
      left_join: t in assoc(d, :tags),
      group_by: [d.id, o.id],
      where: ilike(d.name, ^query_str),
      select: %{
        name: d.name, 
        owner_name: o.name, 
        owner_lasname: o.last_name, 
        tags: fragment("json_agg(json_build_object('id',?,'name',?)) AS tag", t.id, t.name)
      }
    )
    |> Repo.all()
  end