is there a way to select from a cte without joining it with a model?

[dimagi]

is there a way to select from a cte without joining it with a model?

[millerdev]

Yes, you can do that. See the test_named_ctes test.

django-cte/tests/test_cte.py

Lines 252 to 255 in a3f35fe

	paths = With.recursive(make_paths_cte, name="region_paths")
	def make_groups_cte(groups):
	return paths.join(Region, name=paths.col.name).values(

django-cte/tests/test_cte.py

Lines 267 to 270 in a3f35fe

	groups = With.recursive(make_groups_cte, name="region_groups")
	region_totals = With(
	groups.join(Order, region_id=groups.col.name)

Note that it is not necessary to use recursive CTEs, it's just something being done it that test.

You can run the test to see the generated SQL:

nosetests tests/test_cte.py:TestCTE.test_named_ctes --nocapture

For that one the query is

WITH RECURSIVE "region_paths" AS (
    SELECT
        "tests_region"."name",
        "tests_region"."name" AS "path"
    FROM
        "tests_region"
    WHERE
        "tests_region"."parent_id" IS NULL
    UNION
    ALL
    SELECT
        "tests_region"."name",
        COALESCE("region_paths"."path",) || COALESCE(
            COALESCE(,) || COALESCE("tests_region"."name",),
        ) AS "path"
    FROM
        "tests_region"
        INNER JOIN "region_paths" ON "tests_region"."parent_id" = ("region_paths"."name")
),
"region_groups" AS (
    SELECT
        "tests_region"."name",
        "region_paths"."path" AS "parent_path",
        "tests_region"."name" AS "parent_name"
    FROM
        "tests_region"
        INNER JOIN "region_paths" ON "tests_region"."name" = ("region_paths"."name")
    UNION
    ALL
    SELECT
        "tests_region"."name",
        "region_groups"."parent_path" AS "parent_path",
        "region_groups"."parent_name" AS "parent_name"
    FROM
        "tests_region"
        INNER JOIN "region_groups" ON "tests_region"."parent_id" = ("region_groups"."name")
),
"region_totals" AS (
    SELECT
        "region_groups"."parent_name" AS "name",
        "region_groups"."parent_path" AS "path",
        COUNT("tests_order"."id") AS "orders_count",
        SUM("tests_order"."amount") AS "region_total"
    FROM
        "tests_order"
        INNER JOIN "region_groups" ON "tests_order"."region_id" = ("region_groups"."name")
    GROUP BY
        "region_groups"."parent_name",
        "region_groups"."parent_path"
)
SELECT
    "tests_region"."name",
    "tests_region"."parent_id",
    "region_totals"."path" AS "path",
    "region_totals"."orders_count" AS "orders_count",
    "region_totals"."region_total" AS "region_total"
FROM
    "tests_region"
    INNER JOIN "region_totals" ON "tests_region"."name" = ("region_totals"."name")
ORDER BY
    "path" ASC

[millerdev]

Sorry, I just noticed the second part of your question

after defining the CTEs, i want to select directly from C without having to join with a model. is that doable?

No, that is not possible, but why would you want to do that? Would it work to make the query in C be the final query instead of a CTE?

[iMakedonsky]

@millerdev

No, that is not possible, but why would you want to do that? Would it work to make the query in C be the final query instead of a CTE?

I've currently run into such a thing.
I want to filter based on window function results and select * from cte_name;.

To do that, I could annotate CTE with my window function, then select * from it with a where statement.

[adhamselman]

No, that is not possible, but why would you want to do that? Would it work to make the query in C be the final query instead of a CTE?

I use the CTEs to get the data i need and then want to group/count the data.

I will explore your suggestion by using a raw_cte to count the data as D and then join with a model in the select.
Thank you for your help.

[artemistomaras]

Does this example help?

https://github.com/artemistomaras/django-cte-example