Use other queries in joins?

I have some Ecto questions.

Question 1.

Is it possible to use other queries as joins? I mean, let’s say Post can have many Comments. I want to fetch all posts and preload all approved comments.

It can look something like this:

def posts(scope) do
  from p in scope,
    join: c in Comment,
    on: p.id = c.id,
    where: c.approved = true
end

Now, if I have another query wich looks like this:

def approved_comments(scope \\ __MODULE__) do
  from c in scope,
    where: c.approved = true
end

Can I use this second query in the first somehow?

Something like this:

def posts(scope \\ __MODULE__) do
  from p in scope,
    join: c in Comment.approved_comments(),
    on: p.id = c.id
end

I have tried the above (and other variants) but I can’t get it to work. Any ideas?

Question 2.

In the above example, if I’d like to get the count of approved posts instead of loding the entire dataset, how would that look?

1 Like

Check out this answers @josevalim kindly gave me:

2 Likes