Unneccessary outer join breaks query

I want to be able to query from a common table expression. The documentation says this is possible and the example ORM code produces this sql -

WITH RECURSIVE "cte" AS (
    SELECT
        "orders"."id",
        "orders"."region_id",
        "orders"."amount",
        "region"."parent_id" AS "region_parent"
    FROM "orders"
    INNER JOIN "region" ON "orders"."region_id" = "region"."name"
)
SELECT
    "cte"."id",
    "cte"."region_id",
    "cte"."amount",
    "cte"."region_parent" AS "region_parent"
FROM "cte"

Notice there is no join added to the query from the cte table.

However when I try it raises a programming error. I can however inspect the sql generated which gives this -

WITH RECURSIVE "cte" AS 
(
    SELECT "shop_ordertestitem"."order_id", 
    "shop_cliniclocation"."clinic_id", 
    (
        SELECT U0."fee" 
        FROM 
        "shop_clinictestproduct" U0 
        WHERE (
            U0."clinic_id" = "shop_cliniclocation"."clinic_id" 
            AND 
            U0."test_product_option_id" = "shop_ordertestitem"."product_option_id"
        )
    ) AS "fee" 
    FROM "shop_ordertestitem" 
    INNER JOIN "shop_cliniclocation" ON ("shop_ordertestitem"."clinic_location_id" = "shop_cliniclocation"."id") 
    WHERE "shop_cliniclocation"."clinic_id" IS NOT NULL
) 
SELECT
    "cte"."order_id", 
    "shop_cliniclocation"."clinic_id", 
    "cte"."fee" AS "fee" 
FROM "cte" 
LEFT OUTER JOIN "shop_cliniclocation" 
ON ("cte"."clinic_location_id" = "shop_cliniclocation"."id") 
ORDER BY "cte"."created" DESC

The problem is simply the unnecessary left out join it has added. Is this a known issue I can somehow work around to avoid it adding this final outer join.

In terms of what I am trying to achieve... The first query will aggregate based on a grouping which gives multiple rows PER order (this is the query shown above).

I then want to query this first result a second time so that I get a single row PER order. (The hope is to use STRING_AGG which postgres offers). I can't however get past the first hurdle because of the join it is adding.

If, as I fear, this is a bug, I was thinking of using the raw sql escape hatch but is it possible to have two common table expressions where the first at least would need to use the raw sql option?

dimagi / django-cte

Unneccessary outer join breaks query #62