Using Ecto select, select_merge, and joins

Question

I'm trying to figure out how to combine a select, and a join table, but running into issues.

Let's say I have table A, table B, and table C. Table A has some values, and Table B has many items in Table C.

My query is something like this:

query = from(
  a in TableA,
  select: %NewStruct{
    a: a.value  
  }
) 

from(
  a in query,
  join: b in TableB,
  on: b.a_id == a.id,
  join: c in TableC,
  on: c.b_id == b.id,
  select_merge: %{
    c: [c]
  }
)
|> Repo.all()

This works in the sense that it will return 3 structs. They all have the same value of a, but c is each item from TableC.

Current result:

[
  %NewStruct{
    a: "value"
    id: 1,
    c: %TableC{
      id: 1
    }
  },
  %NewStruct{
    a: "value"
    id: 1,
    c: %TableC{
      id: 2
    }
  },
  %NewStruct{
    a: "value"
    id: 1,
    c: %TableC{
      id: 3
    }
  }
]

Normally, I know to use preload: [c: c] to get ecto to combine all of the joined items, and nest within a.c. But I can't in this situation, since NewStruct is just a defstruct. I tried turning NewStruct into an embedded_schema, but I was unable to get the has_many definitions to pickup properly.

My question is this: is there anyway to tell Ecto that the join table c should be preloaded/nested within a.c? I understand that if you did something like SELECT * FROM a INNER JOIN c.b_id ON b.id that you'd get a result of 3 rows from PSQL. But I do know that sometimes Ecto can work some magic, and wondering if I'm missing something.

Desired result:

[
  %NewStruct{
    a: "value"
    id: 1,
    c: [
      %TableC{
        id: 1
      },
      %TableC{
        id: 2
      },
      %TableC{
        id: 3
      },
    ]
  }
]

Aleksei Matiushkin Aleksei Matiushkin · Accepted Answer · 2019-08-29T05:47:16

I would build the query, preload TableC and only then select into the struct.

TableA
|> join(:left, [a], b in assoc(a, :table_b))
|> join(:left, [b], c in assoc(b, :table_c))
|> preload([b, c], [:table_b, table_c: c])
|> select([a], %NewStruct{a: a.value, c: [c]})
|> Repo.all()

Untested, but it should work.

Using Ecto select, select_merge, and joins

1 Answers