Using left_join twice in a query

Hello! I have a query:

def all_for_user_with_subscribers_count(user) do
    subscribers = all_for_user(user)

    from(
      sl in SubscriptionList,
      left_join: ss in "subscriber_subscription_lists",
      on: ss.subscription_list_id == sl.id,
      left_join: sb in ^subscribers,
      on: sb.id == ss.subscriber_id,
      where: sl.user_id == ^user.id,
      group_by: sl.id,
      select: {sl, count(ss.id)}
    ) |> Repo.all
  end

def only_active(query) do
    from(
      s in query,
      where: #...
      )
    )
  end

Here I try to select all subscription lists with their subscribers count inside. The query above works to the extent that it omits empty subscription lists (i.e. without subscribers inside)
The query generates this SQL code:

SELECT s0."id", s0."user_id", s0."name", count(s1."id") FROM "subscription_lists" AS s0 LEFT OUTER JOIN "subscriber_subscripti
on_lists" AS s1 ON s1."subscription_list_id" = s0."id" INNER JOIN "subscribers" AS s2 ON ...

and I found that second left_join will generated as INNER JOIN. Why this happens?

4 Likes

If that’s happening, then it surely is a bug. Can you please open up an issue including your Ecto version and database? I have tried to reproduce it locally but failed.

2 Likes

Nevermind. I got it. :wink:

3 Likes

It has been fixed on Ecto master and v2.1 branches. You can choose it as:

{:ecto, github: "elixir-ecto/ecto", branch: "v2.1", override: true}

You can go back to use it as a package when a new release is out.

8 Likes

Thanks for so quick help! :heart:

3 Likes