Skip to content

Django-cte produces more CTEs when using UNION of two queryset #106

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
ptbang opened this issue Mar 8, 2025 · 0 comments
Open

Django-cte produces more CTEs when using UNION of two queryset #106

ptbang opened this issue Mar 8, 2025 · 0 comments

Comments

@ptbang
Copy link

ptbang commented Mar 8, 2025

To use the same CTE for both queries and merge them with UNION, I use the following code:

cte = With(Product.objects.values('id', 'level', 'category_id'))
levels = cte.queryset().with_cte(cte).values('level').annotate(
    agg_field=Value('level'),
    field_value=cte.col.level,
    count=Count(cte.col.level),
)
categories = cte.queryset().with_cte(cte).values('category_id').annotate(
    agg_field=Value('category_id'),
    field_value=cte.col.category_id,
    count=Count(cte.col.category_id),
)
queryset = levels.union(categories).order_by('agg_field', 'field_value')
print(queryset.query)

The output of the above code is:

(
    WITH RECURSIVE "cte" AS MATERIALIZED (
        SELECT DISTINCT
            "product"."id", "product"."level", "product"."category_id"
        FROM "product"
        ORDER BY "product"."id" ASC
    )
    SELECT 'level' AS "agg_field", ("cte"."level") AS "field_value", COUNT("cte"."level") AS "count"
    FROM "cte"
    GROUP BY "cte"."level", 2
)
UNION (
    WITH RECURSIVE "cte" AS MATERIALIZED (
        SELECT DISTINCT
            "product"."id", "product"."level", "product"."category_id"
        FROM "product"
        ORDER BY "product"."id" ASC
    )
    SELECT 'category_id' AS "agg_field", ("cte"."category_id") AS "field_value", COUNT("cte"."category_id") AS "count"
    FROM "cte"
    GROUP BY "cte"."status_id", 2
)
ORDER BY 1 ASC, 2 ASC

As you can see, the above code produces two identical CTEs for each queryset. The expected behavior is to use the same CTE for both queries. The expected output is:

WITH RECURSIVE "cte" AS MATERIALIZED (
    SELECT DISTINCT
        "product"."id", "product"."level", "product"."category_id"
    FROM "product"
    ORDER BY "product"."id" ASC
)
SELECT 'level' AS "agg_field", ("cte"."level") AS "field_value", COUNT("cte"."level") AS "count"
FROM "cte"
GROUP BY "cte"."level"
UNION
SELECT 'category_id' AS "agg_field", ("cte"."category_id") AS "field_value", COUNT("cte"."category_id") AS "count"
FROM "cte"
GROUP BY "cte"."category_id"
ORDER BY 1 ASC, 2 ASC

Is there any way to use the same CTE for both queries and merge them with UNION?
PS. Django version 5.0, django-cte ver. 1.3.3

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant