Ecto: inner join nested into left join

How could I translate the following query in an Ecto Query?

SELECT users.*, articles.*
FROM users
LEFT OUTER JOIN (
  articles
  INNER JOIN comments ON comments.article_id = articles.id
    AND comments.user_id = 100
) ON articles.author_id = users.id
WHERE users.name = 'Alice'

Below is not an attempt, I know very well this won’t work, but it can be served as a base to work from:)

from u in User,
  left_join: a in Article, on: a.author_id == u.id,
  join: c in Comment, on: c.article_id == a.id and c.user_id == ^100,
  where: u.name == ^"Alice",
  preload: [:authored_articles]

Most importantly, how do you translate the part where the INNER JOIN is nested into a LEFT JOIN?

Thank you for any help!

You need a subquery for that.

@LostKobrakai thank you. In the original query shown above, this is not called a subquery in the SQL world, right? I consider a subquery to be a nested select.

Am I wrong? But if right, then you mean that in Ecto it is not possible to translate the original query above, and that I rather have to resort to another technique, e.g. a subquery?

I didn’t even notice that there is no select in there. Seems like the syntax in the parenthesis is called “Joined Tables” T1 join_type T2 [ join_condition ] (https://www.postgresql.org/docs/13/queries-table-expressions.html). Generally I’d just join both tables in a non nested manner.

This will yield different results.

If you nest the inner join into the left join, and the inner join doesn’t match results, the left join will work, i.e. it will act as an optional join.

If you don’t nest the inner join, then no results will be returned. It “cancels” the optionality that you want through the left join.

Then you might need an proper subquery. I doubt ecto somehow supports this with ecto queries (as opposed to fragments/sql).

The equivalent query rewritten with a subquery (nested select) would look like:

SELECT users.*, articles.*
FROM users
LEFT OUTER JOIN articles ON articles.author_id = users.id
AND articles.id IN (
  SELECT comments.article_id
  FROM comments
  WHERE comments.article_id = articles.id
    AND comments.user_id = 100
)

# Get user 'Alice', and fetch 
# all of her articles that have been commented by user id 100

You can see that the subquery refers to a field in the parent query (WHERE comments.article_id = articles.id). The query works fine in just SQL.

How could I rewrite this with Ecto and subquery ?
Attempt:

subset =
  from comments in Comment,
  where: comments .article_id == articles.id, # <- error, no binding 'articles'
  where: comments.user_id == ^100,
  select: [comments.article_id]

query =
  from users in User,
  left_join: articles in Article,
    on: articles.author_id == users.id
        and articles.id in subquery(subset), # <- error, can't we have `subquery/2` inside `:on` as in SQL?
  preload: [:authored_articles]

Name the parent binding and use parent_as in the subquery: