Ecto preload and join nested models

Question

Here is the code:

query = from(p in Tree,
     where: p.name == ^tree,
     where: p.user_id == ^user_id,
      preload: [kw: :keyo]
      )
     data = Repo.all(query)

This works but returns data in this structure like this:

{
 Kw: [
  {user_id: 1, keyo_id: 2, keyo: {some_keyo_thing: 5},
  {user_id: 1, keyo_id: 32, keyo: {#keyo data}
  ...
  ]
}

Anyway to get merged output of kw/keyo data in the query above? I would like result to be:

[
  {user_id: 1, keyo_id: 2, some_keyo_thing: 5,
  {user_id: 1, keyo_id: 32, , some_keyo_thing: 3
  ...
  ]

PaReeOhNos PaReeOhNos · Accepted Answer · 2016-09-09T10:47:14

Instead of using preload, you're probably better off going with an explicit join, and then selecting the fields you want returned

query = from(p in Tree,
             join: kw in Kw, on: kw.tree_id == p.id,
             join: k in Keyo, on: k.kw_id == kw.id,
             where: p.name == ^tree,
             where: p.user_id == ^user_id,
             select: %{ user_id: p.user_id, keyo_id: k.id, some_keyo_thing: k.some_keyo_thing }
        )

Repo.all(query)

It's a little more verbose, but gives you a lot more control. I've guess at your model names for kw and keyo, so correct as necessary but this should work

Ecto preload and join nested models

1 Answers