Let’s suppose I have Account model that has_many Transactions.
I want to load the list of Accounts, with an average transaction, in one SQL query.
What is the correct way of doing that in Ecto? I am using Ecto 2.0.
Given the other discussion, I would try to write this as a regular query:
from a in "accounts",
join: t in "transactions",
on: t.account_id == a.id,
group_by: [a.id, a.info],
select: %{account: %{id: a.id}, average: avg(t.value)}
Then, if you want to move to schemas, I would build on top of the query above and rely on things like associations and others:
from a in Account,
join: t in assoc(a, :transactions),
group_by: [a.id, a.info],
select: %{account: a, average: avg(t.value)}
Please let us know what you end–up with to see if we can further improve it.
2 Likes
yup, I figured exactly that out a bit earlier today. But I did not get the feeling that this is “kosher” way of doing it, hence my question to this forum. Thanks @josevalim
