CODEWITHSUNDEEP
elixirecto
harryg
harryg

Reputation: 24107

In Ecto, preloading an aggregate query

Lets say I have tables of posts, comments and votes.

The votes table has a direction column which is either 1, 0 or -1.

I want to query all the posts and also the count of comments, and the sum of votes.direction for each post.

Is this achievable as sub-queries in Ecto, ideally using composable queries on the Post model?

Currently what I have is:

def count_comments(query) do
  from p in query,
    left_join: c in assoc(p, :comments),
    select: {p, count(c.id)},
    group_by: p.id
end

def get_score(query) do
  from l in query,
    left_join: v in assoc(p, :votes),
    select: {p, sum(v.direction)},
    group_by: p.id
end

But I can't compose both these queries as I get this error:

(Ecto.Query.CompileError) only one select expression is allowed in query

Upvotes: 0

Views: 1461

Answers (1)

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121010

It is unclear what your code fails on, but it could be likely accomplished with:

from p in Post,
  left_join: c in assoc(p, :comments),
  left_join: v in assoc(p, :votes),
  group_by: p.id,
  select: {p, count(c.id), sum(v.direction)}

Query compisition is allowed for “not terminated” queries only, that said, for the queries, having no select clause (untested, might slightly differ on your structure):

with_comments =
  from p in Post,
  left_join: c in assoc(p, :comments),
  group_by: p.id

with_score_and_comments = 
  from [p, c] in with_comments,
  left_join: v in assoc(p, :votes),
  group_by: p.id

result =
  from [p, c, v] in with_score_and_comments,
  select: {p, count(c.id), sum(v.direction)}

Upvotes: 2

Related Questions