dimagi/django-cte

Common Table Expressions (CTE) for Django

Common Table Expressions with Django

Installation

pip install django-cte

Documentation

The django-cte documentation shows how to use Common Table Expressions with the Django ORM.

Running tests

cd django-cte
mkvirtualenv cte  # or however you choose to setup your environment
pip install django pynose flake8

nosetests
flake8 --config=setup.cfg

All feature and bug contributions are expected to be covered by tests.

Uploading to PyPI

Package and upload the generated files. This assumes the django-cte repository has been configured in ~/.pypirc.

pip install -r pkg-requires.txt

python setup.py sdist bdist_wheel
twine upload --repository=django-cte dist/*

django-cte's Issues

Can I count all children in Queryset annotate?

I need to count the number of all children as below and use it in annotate, but
Is this feasible in cte?

Comment.objects.filter(...).annotate(reply_count=???)

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?

Muliples With joins

Hi everyone.

I have the following scenario:

first_with = With(Model1, name="first_with")
second_with = With(Model2, name="second_with")

Then:

first_with.join(Model0, id=first_with.col.id).with_cte(first_with)...

That generate the SQL as expected like:

WITH RECURSIVE first_with AS ( ... )
SELECT <Model0 attributes>, <first_with attributes>
FROM <Molde0> join with <first_with> ...

So far good.

But then I want to add the second_with to the sql, but I was not able to add the join with Model0, so for example I tried:

first_with.join(Model0, id=first_with.col.id).with_cte(first_with).with_cte(second_with)...

That add the second_with to the WITH RECURSIVE list, but didn't make the join with Model0, I didn't figure out how to
do that. So for the sql looks like:

WITH RECURSIVE first_with AS ( ... ), second_with AS ( ... ) 
SELECT <Model0 attributes>, <first_with attributes>, <second_with attributes>
FROM <Model0> join with <first_with> ...

But second_with is not in the join with Model0, so the query fails.

I tried to following the samples in the test cases, but I think there is no samples for my case.
Is it possible do to what I'm trying?

`.update()` does not work when accessing nested tables

Hello. There are the following data structure

# Set the CTEQuerySet class as shown in the [documentation](https://dimagi.github.io/django-cte/)
class OrganizationQuerySet(CTEQuerySet):  # previously inheritance was from `models.QuerySet`
    def test_method(self):
        return None
    ...


class Organization(models.Model):
    profile = models.ForeignKey('app.Profile', on_delete=models.SET_NULL)
    active = models.BooleanField(default=True)
    ...
    objects = OrganizationQuerySet.as_manager()


class Profile(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    ...

After replacing the models.QuerySet value in the OrganizationQuerySet class with CTEQuerySet, .update() with nesting stopped working

Organization.objects.filter(profile__user__username='test').update(active=False)

The following error is thrown

django.db.utils.ProgrammingError: syntax error at or near ")"
LINE 1: ...app_organization" SET "active" = false WHERE "app_organization"."id" IN ()

This generates the following SQL

UPDATE "app_organization"
SET "active" = false
WHERE "app_organization"."id" IN ()

And not such an option if you use models.QuerySet

UPDATE "app_organization"
SET "active" = false 
WHERE "app_organization"."id" IN (
    SELECT U0."id" 
    FROM "app_organization" U0 
        INNER JOIN "app_profile" U1 ON (U0."profile_id" = U1."id") 
        INNER JOIN "auth_user" U2 ON (U1."user_id" = U2."id") 
WHERE U2."username" = \'test\'

Please help me figure out if this is a bug or I configured the model incorrectly?

Using Django 3.2.19 and Python 3.9

hnej

Django 3.0 AttributeError: 'QJoin' object has no attribute 'join_field'

Happens with Django 3.0 and the test

======================================================================
ERROR: test_named_ctes (tests.test_cte.TestCTE)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/travis/build/SebCorbin/django-cte/tests/test_cte.py", line 234, in test_named_ctes
    region_total=Sum("amount"),
  File "/home/travis/virtualenv/python3.6.7/lib/python3.6/site-packages/django/db/models/query.py", line 1078, in annotate
    clone.query.set_group_by()
  File "/home/travis/virtualenv/python3.6.7/lib/python3.6/site-packages/django/db/models/sql/query.py", line 1939, in set_group_by
    model = join.join_field.related_model
AttributeError: 'QJoin' object has no attribute 'join_field'

Minor : typo in the doc

You have a typo in the doc (and it is not python ^) :

class LargeOrdersQueySet(CTEQuerySet):
    return self.filter(amount__gt=100)


class Order(Model):
    large = LargeOrdersQueySet.as_manager()

Support Django 4.0

When using django-cte with Django 4.0 I get the following error:

...
File ...
    .union(cte.join(Category, parent_id=cte.col.id).values('id'), all=True)
  File "C:\Users\XXX\.virtualenvs\XXX\lib\site-packages\django_cte\cte.py", line 89, in join
    query.join(QJoin(parent, self.name, self.name, on_clause, join_type))
  File "C:\Users\XXX\.virtualenvs\XXX\lib\site-packages\django\db\models\sql\query.py", line 964, in join
    reuse_aliases = [
  File "C:\Users\XXX\.virtualenvs\XXX\lib\site-packages\django\db\models\sql\query.py", line 966, in <listcomp>
    if (reuse is None or a in reuse) and j.equals(join)
  File "C:\Users\XXX\.virtualenvs\XXX\lib\site-packages\django\db\models\sql\datastructures.py", line 185, in equals
    return self.identity == other.identity
AttributeError: 'QJoin' object has no attribute 'identity'

It seems the property got added with this commit:
django/django@bbf141b#diff-7961d220f663d3b5ba4fcb42fa82b34f048de60c655a5c06e369a784e91be1e1

It also throws another error, namely that CTEQuery.get_compiler() got an unexpected keyword argument 'elide_empty'. Added in this commit: django/django@f3112fd.

Cannot install via pipenv

(fb_marketplace) ross@ross-Aspire-TC-780:~/Desktop/web_dev/projects/fb_marketplace$ pipenv install django-cte
Installing django-cte…
Adding django-cte to Pipfile's [packages]…
✔ Installation Succeeded
Pipfile.lock (00d562) out of date, updating to (60deb0)…
Locking [dev-packages] dependencies…
Locking [packages] dependencies…
Building requirements...
Resolving dependencies...
✘ Locking Failed!
Traceback (most recent call last):
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/resolver.py", line 807, in
main()
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/resolver.py", line 802, in main
_main(parsed.pre, parsed.clear, parsed.verbose, parsed.system, parsed.write,
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/resolver.py", line 785, in _main
resolve_packages(pre, clear, verbose, system, write, requirements_dir, packages)
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/resolver.py", line 758, in resolve_packages
results = clean_results(results, resolver, project)
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/resolver.py", line 640, in clean_results
entry_dict = translate_markers(entry.get_cleaned_dict(keep_outdated=False))
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/resolver.py", line 239, in get_cleaned_dict
self.validate_constraints()
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/resolver.py", line 565, in validate_constraints
constraints = self.get_constraints()
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/resolver.py", line 493, in get_constraints
pipfile_constraint = self.get_pipfile_constraint()
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/resolver.py", line 507, in get_pipfile_constraint
return self.constraint_from_parent_conflicts()
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/resolver.py", line 527, in constraint_from_parent_conflicts
if not p.requirements:
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/resolver.py", line 625, in getattribute
return super(Entry, self).getattribute(key)
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/resolver.py", line 370, in requirements
self.project.environment.get_package_requirements(self.name)
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/environment.py", line 708, in get_package_requirements
return [self._get_requirements_for_package(p, key_tree) for p in nodes]
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/environment.py", line 708, in
return [self._get_requirements_for_package(p, key_tree) for p in nodes]
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/environment.py", line 685, in _get_requirements_for_package
d['dependencies'] = [
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/environment.py", line 686, in
cls._get_requirements_for_package(c, key_tree, parent=node,
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/environment.py", line 677, in _get_requirements_for_package
d = node.as_dict()
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/vendor/pipdeptree.py", line 284, in as_dict
'installed_version': self.installed_version,
File "/home/ross/.local/lib/python3.8/site-packages/pipenv/vendor/pipdeptree.py", line 168, in getattr
return getattr(self._obj, key)
AttributeError: 'Requirement' object has no attribute 'installed_version'

issue while using cte with union querysets

When using cte with a queryset that has been union before, it is losing the WITH part on the SQL.

# example 
non_staff_users = User.objects.filter(is_staff=False).only("id", "email")
non_staff_users_cte = With(non_staff_users, name="non_staff_users_cte")

non_staff_users_cte_queryset = non_staff_users_cte.queryset().only("id", "email")


non_staff_gmail_users = non_staff_users_cte_queryset.filter(
    email__endswith="@gmail.com"
).annotate(
    email_provider=Value("gmail", output_field=TextField()),
)

non_staff_yahoo_users = non_staff_users_cte_queryset.filter(
    email__endswith="@yahoo.com"
).annotate(
    email_provider=Value("yahoo", output_field=TextField()),
)


non_staff_gmail_and_yahoo_users = non_staff_gmail_users.union(
    non_staff_yahoo_users, all=True)

non_staff_gmail_users_with_cte = non_staff_gmail_users.with_cte(
    non_staff_users_cte)
print(non_staff_gmail_users_with_cte.query)
# WITH RECURSIVE "non_staff_users_cte" AS (SELECT "auth_user"."id", "auth_user"."email" FROM "auth_user" WHERE NOT "auth_user"."is_staff") SELECT "non_staff_users_cte"."id", "non_staff_users_cte"."email", gmail AS "email_provider" FROM "non_staff_users_cte" WHERE "non_staff_users_cte"."email"::text LIKE [e-mail]

non_staff_yahoo_users_with_cte = non_staff_yahoo_users.with_cte(
    non_staff_users_cte)
print(non_staff_yahoo_users_with_cte.query)
# WITH RECURSIVE "non_staff_users_cte" AS (SELECT "auth_user"."id", "auth_user"."email" FROM "auth_user" WHERE NOT "auth_user"."is_staff") SELECT "non_staff_users_cte"."id", "non_staff_users_cte"."email", yahoo AS "email_provider" FROM "non_staff_users_cte" WHERE "non_staff_users_cte"."email"::text LIKE [e-mail]

non_staff_gmail_and_yahoo_users_with_cte = non_staff_gmail_and_yahoo_users.with_cte(
    non_staff_users_cte)
print(non_staff_gmail_and_yahoo_users_with_cte.query)
# Incorrectly Printed Result
# (SELECT "non_staff_users_cte"."id" AS "col1", "non_staff_users_cte"."email" AS "col2", gmail AS "email_provider" FROM "non_staff_users_cte" WHERE "non_staff_users_cte"."email"::text LIKE [e-mail]) UNION ALL (SELECT "non_staff_users_cte"."id" AS "col1", "non_staff_users_cte"."email" AS "col2", yahoo AS "email_provider" FROM "non_staff_users_cte" WHERE "non_staff_users_cte"."email"::text LIKE [e-mail])

# Expected Result
# WITH RECURSIVE "non_staff_users_cte" AS (SELECT "auth_user"."id", "auth_user"."email" FROM "auth_user" WHERE NOT "auth_user"."is_staff") (SELECT "non_staff_users_cte"."id" AS "col1", "non_staff_users_cte"."email" AS "col2", gmail AS "email_provider" FROM "non_staff_users_cte" WHERE "non_staff_users_cte"."email"::text LIKE [e-mail]) UNION ALL (SELECT "non_staff_users_cte"."id" AS "col1", "non_staff_users_cte"."email" AS "col2", yahoo AS "email_provider" FROM "non_staff_users_cte" WHERE "non_staff_users_cte"."email"::text LIKE [e-mail])

if you attemt to call

non_staff_gmail_and_yahoo_users_with_cte.first()

it is going to throw an error as:

Unhandled ProgrammingError: relation "non_staff_users_cte" does not exist
LINE 1: ...mail" AS "col2", 'gmail' AS "email_provider" FROM "non_staff...
                                                             ^

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedTable: relation "non_staff_users_cte" does not exist
LINE 1: ...mail" AS "col2", 'gmail' AS "email_provider" FROM "non_staff...

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/code.py", line 90, in runcode
    exec(code, self.locals)
  File "<console>", line 1, in <module>
  File "/usr/local/lib/python3.9/site-packages/django/db/models/query.py", line 1057, in first
    for obj in queryset[:1]:
  File "/usr/local/lib/python3.9/site-packages/django/db/models/query.py", line 398, in __iter__
    self._fetch_all()
  File "/usr/local/lib/python3.9/site-packages/django/db/models/query.py", line 1881, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/usr/local/lib/python3.9/site-packages/django/db/models/query.py", line 91, in __iter__
    results = compiler.execute_sql(
  File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1562, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: relation "non_staff_users_cte" does not exist
LINE 1: ...mail" AS "col2", 'gmail' AS "email_provider" FROM "non_staff...

Django 4.2 error: each UNION query must have the same number of columns

In Django 4.2, using UNION queries with order by and select_related causes the above error, as originally referenced in #81 (comment)

We're encountering this now in our production code base, and it is currently preventing an update beyond Django 4.1.

Related: #66

CTEUpdateQueryCompiler as_sql passes extraneous arguments to superclass method

CTEUpdateQueryCompiler inherits from SQLUpdateCompiler, and overrides its as_sql method. However, the overridden method accepts arguments, whereas the parent method does not*, and the child method tries to pass those arguments to the parent method, resulting in a TypeError (see below).

*at least not in the current version of Django, and as far back as 1.11.4

Child method:

class CTEUpdateQueryCompiler(SQLUpdateCompiler):

    def as_sql(self, *args, **kwargs):
        def _as_sql():
            return super(CTEUpdateQueryCompiler, self).as_sql(*args, **kwargs)
        return CTECompiler.generate_sql(self.connection, self.query, _as_sql)

Parent method:

class SQLUpdateCompiler(SQLCompiler):
    def as_sql(self):
        """
        Creates the SQL for this query. Returns the SQL string and list of
        parameters.
        """
        ...

Observed error:

[07/Sep/2018 16:22:22]::ERROR::exception_logger.py:6::as_sql() takes exactly 1 argument (3 given) 
  File "/usr/local/lib/python2.7/dist-packages/django/core/handlers/base.py", line 185, in _get_response 
     response = wrapped_callback(request, *callback_args, **callback_kwargs) 
   File "/usr/local/lib/python2.7/dist-packages/newrelic/hooks/framework_django.py", line 544, in wrapper 
     return wrapped(*args, **kwargs) 
   File "/usr/local/lib/python2.7/dist-packages/django/views/decorators/csrf.py", line 58, in wrapped_view 
     return view_func(*args, **kwargs) 
   File "/usr/local/lib/python2.7/dist-packages/django/views/generic/base.py", line 68, in view 
     return self.dispatch(request, *args, **kwargs) 
   File "/usr/local/lib/python2.7/dist-packages/newrelic/hooks/component_djangorestframework.py", line 46, in _nr_wrapper_APIView_dispatch_ 
     return wrapped(*args, **kwargs) 
   File "/usr/local/lib/python2.7/dist-packages/rest_framework/views.py", line 489, in dispatch 
     response = self.handle_exception(exc) 
   File "/usr/local/lib/python2.7/dist-packages/newrelic/hooks/component_djangorestframework.py", line 53, in _handle_exception_wrapper 
     return wrapped(*args, **kwargs) 
   File "/usr/local/lib/python2.7/dist-packages/rest_framework/views.py", line 449, in handle_exception 
     self.raise_uncaught_exception(exc) 
   File "/usr/local/lib/python2.7/dist-packages/rest_framework/views.py", line 486, in dispatch 
     response = handler(request, *args, **kwargs) 
   File "./asset_api/api/asset_checkin/api.py", line 35, in post 
     raise e 

How to use in python 2 and django 1.8

I wanted to use your library but due to the old version of Django (1.8.7) (and python 2.7) I got an error in some functions, is it possible to use this library in older versions of Django?

Usage in the context of time series

Hi, how are you supposed to use this package without (or even with) a date series queryset, in a GENERATE_SERIES() fashion?

Thanks.

it's wrong number when i wnat to get total number by using count()

environment：

Django                    3.2.16     
django-mysql              3.9.0      
django-cte                1.3.1      

code：

        cte = With(flowTaskDealRecordModel.objects.filter(conn).annotate(
            rw=models.Window(
                expression=window.RowNumber(),
                partition_by=[models.F('taskID_id')],
                order_by=[models.F('cTime').desc()],
            )
        ))
        
        qs = cte.queryset().with_cte(cte).select_related("flowID","taskID").filter(Q(rw=1)).order_by("-cTime")
        print(qs.query)
        count = qs.count()
        print("count",count)
        count2 = len(qs)
        print("count2",count2)
        count3 = qs.count()
        print("count3",count3)
        

output:

count 18143
count2 17906

sql

WITH RECURSIVE `cte` AS (
	SELECT
		`flow_taskDealRecord`.`id`,
		`flow_taskDealRecord`.`flowID_id`,
		`flow_taskDealRecord`.`taskID_id`,
		`flow_taskDealRecord`.`index`,
		`flow_taskDealRecord`.`accountID`,
		`flow_taskDealRecord`.`accountIDName`,
		`flow_taskDealRecord`.`personFactName`,
		`flow_taskDealRecord`.`company`,
		`flow_taskDealRecord`.`companyName`,
		`flow_taskDealRecord`.`result`,
		`flow_taskDealRecord`.`processFlag`,
		`flow_taskDealRecord`.`reason`,
		`flow_taskDealRecord`.`validFlag`,
		`flow_taskDealRecord`.`cTime`,
		`flow_taskDealRecord`.`detail`,
		ROW_NUMBER() OVER ( PARTITION BY `flow_taskDealRecord`.`taskID_id` ORDER BY `flow_taskDealRecord`.`cTime` DESC ) AS `rw` 
	FROM
		`flow_taskDealRecord` 
	) SELECT
	`cte`.`id`,
	`cte`.`flowID_id`,
	`cte`.`taskID_id`,
	`cte`.`index`,
	`cte`.`accountID`,
	`cte`.`accountIDName`,
	`cte`.`personFactName`,
	`cte`.`company`,
	`cte`.`companyName`,
	`cte`.`result`,
	`cte`.`processFlag`,
	`cte`.`reason`,
	`cte`.`validFlag`,
	`cte`.`cTime`,
	`cte`.`detail`,
	`cte`.`rw` AS `rw`,
	`flow_flow`.`id`,
	`flow_flow`.`typeID_id`,
	`flow_flow`.`name`,
	`flow_flow`.`flowDescribe`,
	`flow_task`.`id`,
	`flow_task`.`taskName`,
	`flow_task`.`taskObjID`,
	`flow_task`.`taskObjCode`,
	`flow_task`.`taskObjName`,
	`flow_task`.`taskObjCompanyID`,
	`flow_task`.`taskObjCompanyName`,
	`flow_task`.`taskObjTeamID`,
	`flow_task`.`taskObjTeamName`,
	`flow_task`.`flowType_id`,
	`flow_task`.`flowID_id`,
	`flow_task`.`currentIndex`,
	`flow_task`.`resultCallbackURL`,
	`flow_task`.`detailCallbackURL`,
	`flow_task`.`status`,
	`flow_task`.`reason`,
	`flow_task`.`cTime`,
	`flow_task`.`mTime`,
	`flow_task`.`callBackSuccess` 
FROM
	`cte`
	INNER JOIN `flow_flow` ON ( `cte`.`flowID_id` = `flow_flow`.`id` )
	INNER JOIN `flow_task` ON ( `cte`.`taskID_id` = `flow_task`.`id` ) 
WHERE
	`cte`.`rw` = 1 
ORDER BY
	`cte`.`cTime` DESC

i get 18143 when use count() first, i get 17096 when i use len(). and then i get 17096 when using count() second.
i get 17096 when execute sql directly.so why i can get 18143?
so is there some bugs for count() when using django-cte?

Recursive query with 2 parents

I was wondering if it is possible to have a recursive query with 2 parent fields with django-cte, i.e., something like this SQL:

WITH RECURSIVE ped_cte AS (
  SELECT 1 AS n, d.id, d.name, d.father_id, d.mother_id
  FROM dog AS d
  WHERE d.id = 16324  

  UNION ALL

  SELECT n+1, f.id, f.name, f.father_id, f.mother_id
  FROM ped_cte  
  INNER JOIN dog AS f ON ped_cte.father_id = f.id  
  WHERE n < 5

  UNION ALL

  SELECT n+1, m.id, m.name, m.father_id, m.mother_id
  FROM ped_cte  
  INNER JOIN dog AS m ON ped_cte.mother_id = m.id  
  WHERE n < 5  
)
SELECT * FROM ped_cte;

Edit by @millerdev to fix code formatting.

CTECompiler incompatible with GIS lookups

When using GIS lookups, the CTECompiler cast Subquery params to tuple, making it impossible to spatially filter a field provided by a Subquery.

I get this error :

Traceback (most recent call last):
[...]
  File "lib/python3.8/site-packages/django/db/models/query.py", line 274, in __iter__
    self._fetch_all()
  File "lib/python3.8/site-packages/django/db/models/query.py", line 1242, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "lib/python3.8/site-packages/django/db/models/query.py", line 55, in __iter__
    results = compiler.execute_sql(chunked_fetch=self.chunked_fetch, chunk_size=self.chunk_size)
  File "lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 1127, in execute_sql
    sql, params = self.as_sql()
  File "lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 489, in as_sql
    where, w_params = self.compile(self.where) if self.where is not None else ("", [])
  File "lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 405, in compile
    sql, params = node.as_sql(self, self.connection)
  File "lib/python3.8/site-packages/django/db/models/sql/where.py", line 81, in as_sql
    sql, params = compiler.compile(child)
  File "lib/python3.8/site-packages/django/db/models/sql/compiler.py", line 405, in compile
    sql, params = node.as_sql(self, self.connection)
  File "lib/python3.8/site-packages/django/contrib/gis/db/models/lookups.py", line 78, in as_sql
    lhs_sql, sql_params = self.process_lhs(compiler, connection)
AttributeError: 'tuple' object has no attribute 'extend'

When modifying CTECompiler.generate_sql to return " ".join(sql), params instead of return " ".join(sql), tuple(params), error is gone and queryset works well

This error happens only on Django < 3.1.4 as the handling of params has been strengthened since django/django@0290e01

Edit: So it's just that the core compiler was not consistent with the signature (see https://code.djangoproject.com/ticket/31002), what do you suggest? Should we fix it in django-cte ?

CTEColumn lookup

Hello,
in my query i have

.annotate(
                drawdown=Max(cte.col.drawdown),
                total_trades=Count(cte.col.strategy_id),
                pips=Sum(cte.col.profit),
            )

this code works as expected but when i try to use the lookup __lt doing (i added profit_count):

    .annotate(
                drawdown=Max(cte.col.drawdown),
                total_trades=Count(cte.col.strategy_id),
                pips=Sum(cte.col.profit),
                profit_count=Count(Case(
                    When(profit__lt=0, then=1),
                    output_field=IntegerField(),
                )),
            )

i do not know what is the correct way to call __lt in this case. How should i use the lookups?

“no such column”/“column does not exist” errors in Django 4.2

With Django 4.2, django-cte causes SQL errors in production as well as its own test suite. I’ve bisected the failure to django/django@70499b2 which addressed https://code.djangoproject.com/ticket/34123. That commit results in extra aliases in the generated SQL:

 WITH RECURSIVE "rootmap" AS (
-  SELECT "region"."name",
+  SELECT "region"."name" AS "col1",
     "region"."name" AS "root"
   FROM "region"
   WHERE "region"."parent_id" IS NULL

   UNION ALL

-  SELECT "region"."name",
+  SELECT "region"."name" AS "col1",
     "rootmap"."root" AS "root"
   FROM "region"
   INNER JOIN "rootmap" ON "region"."parent_id" = ("rootmap"."name")
 ),
 "totals" AS (
   SELECT "rootmap"."root" AS "root",
     COUNT("orders"."id") AS "orders_count",
     SUM("orders"."amount") AS "region_total"
   FROM "orders"
   INNER JOIN "rootmap" ON "orders"."region_id" = ("rootmap"."name")
   GROUP BY "root"
 )
 SELECT "region"."name",
   "region"."parent_id",
   "totals"."orders_count" AS "orders_count",
   "totals"."region_total" AS "region_total"
 FROM "region"
 INNER JOIN "totals" ON "region"."name" = ("totals"."root")

which breaks the references to rootmap.name:

======================================================================
ERROR: test_named_ctes (tests.test_cte.TestCTE)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/anders/python/django-cte/tests/test_cte.py", line 290, in test_named_ctes
    data = sorted(
  File "/home/anders/python/django-cte/.direnv/python-3.9.16/lib/python3.9/site-packages/django/db/models/query.py", line 398, in __iter__
    self._fetch_all()
  File "/home/anders/python/django-cte/.direnv/python-3.9.16/lib/python3.9/site-packages/django/db/models/query.py", line 1898, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/home/anders/python/django-cte/.direnv/python-3.9.16/lib/python3.9/site-packages/django/db/models/query.py", line 91, in __iter__
    results = compiler.execute_sql(
  File "/home/anders/python/django-cte/.direnv/python-3.9.16/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1516, in execute_sql
    cursor.execute(sql, params)
  File "/home/anders/python/django-cte/.direnv/python-3.9.16/lib/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/home/anders/python/django-cte/.direnv/python-3.9.16/lib/python3.9/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/anders/python/django-cte/.direnv/python-3.9.16/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/home/anders/python/django-cte/.direnv/python-3.9.16/lib/python3.9/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/anders/python/django-cte/.direnv/python-3.9.16/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/home/anders/python/django-cte/.direnv/python-3.9.16/lib/python3.9/site-packages/django/db/backends/sqlite3/base.py", line 378, in execute
    return super().execute(query, params)
django.db.utils.OperationalError: no such column: rootmap.name
-------------------- >> begin captured stdout << ---------------------
WITH RECURSIVE "rootmap" AS (SELECT "region"."name" AS "col1", "region"."name" AS "root" FROM "region" WHERE "region"."parent_id" IS NULL UNION ALL SELECT "region"."name" AS "col1", "rootmap"."root" AS "root" FROM "region" INNER JOIN "rootmap" ON "region"."parent_id" = ("rootmap"."name")), "totals" AS (SELECT "rootmap"."root" AS "root", COUNT("orders"."id") AS "orders_count", SUM("orders"."amount") AS "region_total" FROM "orders" INNER JOIN "rootmap" ON "orders"."region_id" = ("rootmap"."name") GROUP BY "root") SELECT "region"."name", "region"."parent_id", "totals"."orders_count" AS "orders_count", "totals"."region_total" AS "region_total" FROM "region" INNER JOIN "totals" ON "region"."name" = ("totals"."root")

--------------------- >> end captured stdout << ----------------------

WITH query name specified more than once

python: 3.11
version: 1.3.2

Description:

I try to generate query with cte like this:

class CustomQuerySet(CTEQuerySet):
    def custom_annotate(self) -> HouseQuerySet:
        
        custom_cte = With(
            BoosterModel.objects
            .values("user_id")
            .annotate(
                boosters_efficient_impact=models.Sum("efficient_impact"),
                boosters_mood_impact=models.Sum("mood_impact"),
            ),
            name="custom_cte"
        )

        return self.with_cte(custom_cte).annotate(
            mood_value=models.functions.Round(
                100
                + models.functions.Coalesce(models.Sum("buildings__data__mood_impact"), 0)
                + models.functions.Coalesce(
                    models.Subquery(
                        custom_cte.queryset()
                        .filter(user_id=models.OuterRef("owner_id"))
                        .values("boosters_mood_impact"),
                    ),0
                ),
            ),
            efficient_value=models.functions.Round(
                100
                + models.functions.Coalesce(models.Sum("buildings__data__efficient_impact"), 0)  # эффективность зданий
                - (100 - models.F("mood_value"))
            ),
  )

it looks like example

django-cte/tests/test_cte.py

Line 89 in 88843ef

def test_cte_queryset(self):

but when i try generate query, i've got this:
WITH query name "custom_cte" specified more than once

I've use cte only in this query

sql:

WITH RECURSIVE "custom_cte" AS (
        SELECT "house_boosters"."user_id",
               SUM("house_boosters"."efficient_impact") AS "boosters_efficient_impact",
               SUM("house_boosters"."mood_impact") AS "boosters_mood_impact"
          FROM "house_boosters"
         GROUP BY "house_boosters"."user_id"
       ),
       "custom_cte" AS (
        SELECT "house_boosters"."user_id",
               SUM("house_boosters"."efficient_impact") AS "boosters_efficient_impact",
               SUM("house_boosters"."mood_impact") AS "boosters_mood_impact"
          FROM "house_boosters"
         GROUP BY "house_boosters"."user_id"
       ) SELECT "houses"."created_at",
       "houses"."modified_at",
       "houses"."id",
       "houses"."name",
       "houses"."owner_id",
       ROUND((('Int4(100)' + COALESCE(SUM("building_data"."mood_impact"), 'Int4(0)')) + COALESCE((SELECT U0."boosters_mood_impact" AS "boosters_mood_impact" FROM "custom_cte" U0 WHERE U0."user_id" = ("houses"."owner_id")), 'Int4(0)')), 'Int4(0)') AS "mood_value",
       ROUND((('Int4(100)' + COALESCE(SUM("building_data"."efficient_impact"), 'Int4(0)')) - ('Int4(100)' - ROUND((('Int4(100)' + COALESCE(SUM("building_data"."mood_impact"), 'Int4(0)')) + COALESCE((SELECT U0."boosters_mood_impact" AS "boosters_mood_impact" FROM "custom_cte" U0 WHERE U0."user_id" = ("houses"."owner_id")), 'Int4(0)')), 'Int4(0)'))), 'Int4(0)') AS "efficient_value",
       COALESCE(SUM(("building_upgrade_info"."amount_impact" * "building_info"."level")) FILTER (WHERE "building_data"."type" = '''Workshop'''), 'Int4(0)') AS "workshop_amount"
  FROM "houses" .........

What i missed/did wrong?

UPDATE
It seems like prefetch_related duplicate ctes:

def efficient_prefetch(self):
        return self.prefetch_related(
            models.Prefetch(
                "members",
                queryset=UserModel.objects.all().prefetch_related(
                    models.Prefetch("house", HouseModel.objects.all().custom_annotate()), # <-- duplicate here
                ),
            ),
        )

How to use as default manager?

I tried to use a CTE as default manager, but I failed to do so:

class PeriodsManager(models.Manager):
    def get_queryset(self):
        # get the end date of the period, oldest period first, newest last
        cte = With(
            QuerySet(MembershipTypePeriods)
            # MembershipTypePeriods.objects
            .order_by('start_date').annotate(
            end_date=Window(
                partition_by=[F('membership_id')],
                expression=Lead('start_date'),
                order_by=F('start_date').asc()
            )
        ))
        return cte.queryset().with_cte(cte)

class MembershipTypePeriods(models.Model):
    objects = PeriodsManager()
    start_date = models.DateField(default=date.today)
    type = models.ForeignKey(MembershipType, on_delete=RESTRICT)
    membership = models.ForeignKey(Membership, on_delete=CASCADE)

Something like

class PeriodsManager(CTEManager):
    def get_queryset(self):
        # get the end date of the period, oldest period first, newest last
        return super().get_queryset().order_by('start_date').annotate(
            end_date=Window(
                partition_by=[F('membership_id')],
                expression=Lead('start_date'),
                order_by=F('start_date').asc()
            )
        )

does not seems to use CTEs

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

i define 3 CTEs. each consecutive cte joins with the previously defined cte. something like this:
with A as (select ... from table), B as (select ... from table join A on (...)), C as (select ... from table join B on (...))

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

i tried using raw_cte, but it didnt work and just want to confirm that this is not supported.

Thank you

issue while trying to use with_cte on EmptyResultSet

Django has a built-in check when the rhs of the query should return an empty queryset. It raises an EmptyResultSet and returns an empty list for the result. When you combine an EmptyResultSet query with_cte unfortunately, django-cte does not recognize it, and throws an error instead of returning an empty list.

-- queryset -- <CTEQuerySet []>
cte.query 
 SELECT "population_cte"."id", "population_cte"."year", "population_cte"."population" FROM "population_cte" WHERE "population_cte"."population" <= 10000000
--POPULATIONS QUERY : 
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/ahp/Desktop/cte_test/testapp/core/api.py", line 87, in empty_query_issue
    print(
  File "/Users/ahp/Desktop/cte_test/venv/lib/python3.9/site-packages/django/db/models/query.py", line 374, in __repr__
    data = list(self[: REPR_OUTPUT_SIZE + 1])
  File "/Users/ahp/Desktop/cte_test/venv/lib/python3.9/site-packages/django/db/models/query.py", line 398, in __iter__
    self._fetch_all()
  File "/Users/ahp/Desktop/cte_test/venv/lib/python3.9/site-packages/django/db/models/query.py", line 1881, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
  File "/Users/ahp/Desktop/cte_test/venv/lib/python3.9/site-packages/django/db/models/query.py", line 99, in __iter__
    model_cls = klass_info["model"]
TypeError: 'NoneType' object is not subscriptable

# simple model
from django.db import models
from django_cte import CTEManager


class Population(models.Model):
    class Meta:
        db_table = "population"

    year = models.PositiveIntegerField()
    population = models.BigIntegerField()

    objects = CTEManager()

    def __str__(self) -> str:
        return f"{self.year} : {self.population}"

# test function

def empty_query_issue():
    queryset = Population.objects.filter(year__in=[])
    # print(
    #     "--queryset.query \n",
    #     queryset.query,
    # )
    print(
        "-- queryset",
        f"-- {queryset}"
    )
    cte = With(
        queryset,
        name="population_cte",
    )
    cte_queryset = cte.queryset()
    cte_queryset = cte_queryset.filter(population__lte=10000000)
    print(
        "cte.query \n",
        cte_queryset.query,
    )

    populations = cte_queryset.with_cte(cte)
    print(
        '--POPULATIONS QUERY : \n',
        populations,
    )

---

for a quick fix, you can overwrite the __iter__ method of the CTEQuerySet, but this is not a proper solution.

from django_cte import CTEQuerySet
from typing import Iterator
from django.core.exceptions import EmptyResultSet


class FixedCTEQuerySet(CTEQuerySet):

    def __iter__(self) -> Iterator:
        try:
            return super(CTEQuerySet, self).__iter__()
        except Exception:
            try:
                str(self.query)
            except EmptyResultSet:
                return iter([])
            else:
                raise


CTEQuerySet.__iter__ = FixedCTEQuerySet.__iter__

no such table: cte while importing fixtures

With the following model:

class UserInfoCTEQuerySet(CTEQuerySet):
    """QuerySet attached to the UserInfo.with_rank manager."""

    def with_rank(self):
        """Use a Common Table Expression to add rank to UserInfos.

        The resulting query looks like:

            WITH cte AS (
                SELECT *, DENSE_RANK() OVER (ORDER BY hkis_userinfo.points DESC) AS r
                FROM "hkis_userinfo")
            SELECT * FROM cte

        The idea is with_rank() can be changed with filters without
        modifying the window.

        Without a CTE,
        `UserInfo.with_rank.filter(user__username="anyone")`
        would always be ranked 1st (as the only one in its selection).
        """
        with_rank = With(
            self.annotate(r=Window(order_by=F("points").desc(), expression=DenseRank()))
        )
        return with_rank.queryset().with_cte(with_rank)


class UserInfoManager(CTEManager):
    """UserInfo.with_rank manager (the base one), allows for:

        User.objects.first().hkis.r

    and:

        UserInfo.with_rank.first().r
    """

    def get_queryset(self):
        return UserInfoCTEQuerySet(self.model, using=self._db).with_rank()


class UserInfo(models.Model):
    class Meta:
        base_manager_name = "with_rank"
        indexes = [
            models.Index(fields=["-points"]),
        ]

    objects = CTEManager()
    with_rank = UserInfoManager.from_queryset(UserInfoCTEQuerySet)()
    user = models.OneToOneField(to=User, on_delete=models.CASCADE, related_name="hkis")
    points = models.FloatField(default=0)  # Computed sum of solved exercise positions.

    def __str__(self):
        return f"{self.user.username} {self.points} points"

If I create some values, use dumpdata to save them, and loaddata to get them back I'm getting:

django.db.utils.OperationalError: Problem installing fixture '/tmp/cteissue/issue39/fixtures/initial.json': Could not load issue39.UserInfo(pk=1): no such table: cte

I'm having a small reproducer here, just run:

./manage.py loaddata initial

Or if you want to start it from scratch, it looks like:

django-admin startproject cteissue
 cd cteissue/
 ./manage.py startapp issue39
editor issue39/models.py # Paste my model 
./manage.py makemigrations
 ./manage.py migrate
./manage.py createsuperuser
sqlite3 db.sqlite3 <<< 'insert into issue39_userinfo (points, user_id) values (1, 1);' 
mkdir issue39/fixtures
./manage.py dumpdata -o issue39/fixtures/initial.json
./manage.py loaddata initial

Full stack trace

$ ./manage.py loaddata initial
Traceback (most recent call last):
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/backends/sqlite3/base.py", line 423, in execute
    return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: no such table: cte

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/tmp/cteissue/./manage.py", line 22, in <module>
    main()
  File "/tmp/cteissue/./manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/home/mdk/.local/lib/python3.9/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/core/management/commands/loaddata.py", line 78, in handle
    self.loaddata(fixture_labels)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/core/management/commands/loaddata.py", line 123, in loaddata
    self.load_label(fixture_label)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/core/management/commands/loaddata.py", line 190, in load_label
    obj.save(using=self.using)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/core/serializers/base.py", line 223, in save
    models.Model.save_base(self.object, using=using, raw=True, **kwargs)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/models/base.py", line 763, in save_base
    updated = self._save_table(
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/models/base.py", line 845, in _save_table
    updated = self._do_update(base_qs, using, pk_val, values, update_fields,
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/models/base.py", line 899, in _do_update
    return filtered._update(values) > 0
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/models/query.py", line 802, in _update
    return query.get_compiler(self.db).execute_sql(CURSOR)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1559, in execute_sql
    cursor = super().execute_sql(result_type)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
    cursor.execute(sql, params)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/home/mdk/.local/lib/python3.9/site-packages/django/db/backends/sqlite3/base.py", line 423, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: Problem installing fixture '/tmp/cteissue/issue39/fixtures/initial.json': Could not load issue39.UserInfo(pk=1): no such table: cte

Can't call update() when using CTE manager

>>> type(qs)
<class 'django_cte.cte.CTEQuerySet'>
>>> str(qs.query)
'SELECT * FROM model INNER JOIN model2 ON (model."obj_id" = "model2"."id") WHERE ( "model"."id" IN (1, 2, 3, 5, 11, 100)) ORDER BY "notification_inbox"."sent_timestamp" DESC'
>>> qs.update(field1='Hello')
*** django.db.utils.ProgrammingError: syntax error at or near ")"
LINE 1: ... SET "field1" = 'Hello' WHERE "model"."id" IN ()

Looking at the postgres logs, im able to see that the query being ran is: UPDATE "model" SET "field1" = 'Hello' WHERE "model"."id" IN ()

If i swap out the objects = CTEManager() for a objects = models.Manager() everything works as expected.

As a temporary solution ive keep the objects = models.Manager() and added a cte_objects = CTEManager() -- while not ideal, it works.

Requires a manager called "objects"

Specifically, https://github.com/dimagi/django-cte/blob/master/django_cte/cte.py#L103 refers to .objects, where it should refer to ._default_manager

Joins don't use aliases in subqueries

See #1 for a possible solution.

Combining with django-reversion

I am trying to use django-cte on models managed with reversion. When running createinitialrevisions, it seems that the correct QuerySet or manager is not used:

  File "/home/lukas/.cache/pypoetry/virtualenvs/aleksis-core-yiBKpV3N-py3.10/lib/python3.10/site-packages/reversion/management/commands/createinitialrevisions.py", line 66, in handle
    live_objs = _safe_subquery(
  File "/home/lukas/.cache/pypoetry/virtualenvs/aleksis-core-yiBKpV3N-py3.10/lib/python3.10/site-packages/reversion/models.py", line 389, in _safe_subquery
    left_query = left_query.annotate(**{exist_annotation_name: models.Exists(right_subquery)})
  File "/home/lukas/.cache/pypoetry/virtualenvs/aleksis-core-yiBKpV3N-py3.10/lib/python3.10/site-packages/django/db/models/query.py", line 1091, in annotate
    return self._annotate(args, kwargs, select=True)
  File "/home/lukas/.cache/pypoetry/virtualenvs/aleksis-core-yiBKpV3N-py3.10/lib/python3.10/site-packages/django/db/models/query.py", line 1130, in _annotate
    clone.query.add_annotation(
  File "/home/lukas/.cache/pypoetry/virtualenvs/aleksis-core-yiBKpV3N-py3.10/lib/python3.10/site-packages/django_cte/query.py", line 49, in add_annotation
    super(CTEQuery, self).add_annotation(annotation, *args, **kw)
  File "/home/lukas/.cache/pypoetry/virtualenvs/aleksis-core-yiBKpV3N-py3.10/lib/python3.10/site-packages/django/db/models/sql/query.py", line 1039, in add_annotation
    annotation = annotation.resolve_expression(self, allow_joins=True, reuse=None,
  File "/home/lukas/.cache/pypoetry/virtualenvs/aleksis-core-yiBKpV3N-py3.10/lib/python3.10/site-packages/django_cte/expressions.py", line 45, in resolve_expression
    for cte in get_query(clone)._with_ctes:
AttributeError: 'Query' object has no attribute '_with_ctes'

I cannot really wrap my head around it; apart from running the management command, everything works fine.

Maybe someone who knows about how django-cte uses custom QuerySets can help understand the issue?

Mutiple CTEs Possible?

Is it possible to use multiple CTEs in one query? E.g:

WITH your_cte_name AS (
    SELECT foo, bar
    FROM your_table
),
cte_number_2_name AS (
    SELECT foo
    FROM your_cte_name
)

SELECT * FROM cte_number_2_name

Not able to do full outer join with django_cte

Django-cte is wonderful library. It has solved most my cases when comes to writing complex sql queries in django but recently i came across a situation where i had to do full outer join and i tired to do it with django_cte, i failed and when i debug the code in the library . i found it supports only inner and left outer join. So my point here is that can we add full outer join functionality in django_cte?? I assume my developers ran into this situation.

CTE statement is not being generated

First of all, sorry for posting this as an issue. I could not think of any other place to ask. This is the first time I'm trying django-cte so my mistake could be silly, however I mostly copied the README example.

This is my CTE code:

class Annotation(models.Model):
 objects = django_cte.CTEManager()

 uuid = models.UUIDField(null=True, unique=True)
 instance = models.ForeignKey(Instance, models.CASCADE,
                              related_name='annotations')
 type = models.CharField(max_length=64, choices=TYPE_CHOICES)
 data = models.TextField()
 dependencies = models.ManyToManyField('self', symmetrical=False,
                                       related_name="dependents",
                                       through="Dependency")

  def make_regions_cte(cte):
       return Annotation.objects.filter(
           # start with root nodes
           id__in=annotation_ids
       ).values(
           "id",
           "uuid",
           depth=value0,
       ).union(
           # recursive union: get descendants
           cte.join(Annotation, uuid=cte.col.uuid).values(
               "id",
               "uuid",
               depth=cte.col.depth + value1,
           ),
           all=True,
       )

   cte = With.recursive(make_regions_cte)

   annotations = (
       cte.join(Annotation, uuid=cte.col.uuid)
       .with_cte(cte)
       .annotate(
           depth=cte.col.depth,
       )
       .order_by("depth")
   )

This is the resulting SQL query:

WITH RECURSIVE cte AS ((SELECT "collab_annotation"."id", "collab_annotation"."uuid", ("cte"."depth" + 1) AS "depth" FROM "collab_annotation" INNER JOIN "cte" ON "collab_annotation"."uuid" = ("cte"."uuid"))) SELECT "collab_annotation"."id", "collab_annotation"."uuid", "collab_annotation"."instance_id", "collab_annotation"."type", "collab_annotation"."data", "cte"."depth" AS "depth" FROM "collab_annotation" INNER JOIN "cte" ON "collab_annotation"."uuid" = ("cte"."uuid") ORDER BY "depth" ASC  LIMIT 21; args=(1,)

This is the error I'm getting:

django.db.utils.ProgrammingError: recursive query "cte" does not have the form non-recursive-term UNION [ALL] recursive-term
LINE 1: WITH RECURSIVE cte AS ((SELECT "collab_annotation"."id", "co...

Thanks for reading through!

Can't use CTE within a subquery?

ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

I have a pretty complex query, but thanks to your package, I've managed to make it an ORM based one (which is useful for annotation).

But I've got a problem: I'm wondering why I can't use CTE in a subquery, here's the query I'm trying to achieve (yes, it's complex, and I've already simplified it):

SELECT V0."id"
FROM "plant" V0
WHERE (CASE
           WHEN (V0."plantation_date" > 2020 - 03 - 30
               OR (V0."death_date" IS NOT NULL
                   AND V0."death_date" <= 2020 - 03 - 30))
               THEN -1
           WHEN (
                   (WITH RECURSIVE cte AS
                                       (SELECT "observation"."plant_id",
                                               "observation"."date",
                                               "observation"."emission",
                                               LEAD("observation"."emission", 1)
                                               OVER (ORDER BY "observation"."date" DESC) AS "prev_emission"
                                        FROM "observation"
                                        WHERE "observation"."date" <= 2020 - 03 - 30
                                               AND "observation".plant_id = V0.plant_id
                                        ORDER BY "observation"."date" DESC)
                    SELECT CASE
                               WHEN (LEAD(U0."date", 1)
                                     OVER (ORDER BY U0."date" DESC) IS NOT NULL
                                   AND EXTRACT(
                                               'year' FROM
                                               LEAD(U0."date", 1)
                                               OVER (ORDER BY U0."date" DESC)
                                           ) =
                                       EXTRACT('year' FROM U0."date"))
                                   THEN EXTRACT('year' FROM U0."date")
                               ELSE NULL
                               END AS "last_emissions_year"
                    FROM "cte" U0
                    WHERE U0."emission" IS DISTINCT
                        FROM U0."prev_emission"
                    ORDER BY U0."date" DESC
                    LIMIT 1) = 2020
               ) THEN 2
           ELSE 0
           END = 2
          )

Which translates as

at_date = date.today()

cte = With(
    Observation.objects.filter(
        date__lte=at_date,
    ).annotate(
        prev_emission=Window(
            Lead('emission'),
            order_by=F('date').desc()
        )
    ).order_by('-date').values('plant_id', 'date', 'emission', 'prev_emission')
)
qs = cte.queryset().with_cte(cte).annotate(
    other_field=Window(Lead('date'), order_by=F('date').desc()),
    last_emissions_year=Case(
        When(
            Q(other_field__isnull=False) &
            Q(other_field__year=ExtractYear('date')),
            then=ExtractYear('date')
        ),
    ),
).filter(
    emission__isdistinctfrom=F('prev_emission')
).order_by('-date').values('last_emissions_year')

Plant.objects.annotate(
    last_emissions_year=Subquery(qs[:1]),
    plant_state=Case(
        When(
            Q(plantation_date__gt=at_date) | (
                    Q(death_date__isnull=False) &
                    Q(death_date__lte=at_date)
            ),
            then=STATE_INACTIVE,
        ),
        When(
            Q(
                last_emissions_year__isnull=False,
                last_emissions_year=at_date.year,
            ),
            then=STATE_EMISSION_END,
        ),
        default=STATE_NO_EMISSION,
        output_field=IntegerField(),
    ),
).filter(plant_state=STATE_EMISSION_END)

The one above works fine but is missing AND "observation".plant_id = V0.plant_id in the CTE, and when I add plant_id=OuterRef('pk') to the CTE

cte = With(
    Observation.objects.filter(
        plant_id=OuterRef('pk'),
        date__lte=at_date,
    ).annotate(
        prev_emission=Window(
            Lead('emission'),
            order_by=F('date').desc()
        )
    ).order_by('-date').values('plant_id', 'date', 'emission', 'prev_emission')
)

I get the error mentioned, any ideas?

Add support django 3.0

annotate() usage in recursion

Hi,

Imagine the following models:

class Person(models.Model):
    parent = models.ForeignKey('self', null=True, blank=True, on_delete=models.SET_NULL)

    from django_cte import CTEManager
    objects = CTEManager()

class Event(models.Model):
    when = modelsDateTimeField(null=False, blank=False)
    person = models.ForeignKey(Person, null=False, blank=False, on_delete=models.CASCADE, default=None)

Now I would like to get the count() of all the events for a tree/family of Person objects. I am not sure I am doing correctly, but if I am, something is not working? (SubqueryCount is local and confirmed to work)

def make_person_cte(cte):
    return Person.objects.filter(
        parent__isnull=True
    ).annotate(
        event_count=SubqueryCount(
            Event.objects.only('pk').filter(
                person__pk=OuterRef('pk'),
            ),
            output_field=models.IntegerField(),
        ),
    ).union(
        # recursive union: get children
        cte.join(
            Person,
            parent=cte.col.pk
        ),
        all=True,
    )

persons_cte = With.recursive(make_person_cte)
persons = persons_cte.join(
    Person,
    pk=persons_cte.col.pk
).with_cte(
    persons_cte 
)

and the trace looks like the following:

Traceback (most recent call last):
  File "/home/foo/main/pro/web/foo/manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/home/foo/main/env/lib/python3.9/site-packages/django/core/management/__init__.py", line 419, in execute_from_command_line
    utility.execute()
  File "/home/foo/main/env/lib/python3.9/site-packages/django/core/management/__init__.py", line 413, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/foo/main/env/lib/python3.9/site-packages/django/core/management/base.py", line 354, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/foo/main/env/lib/python3.9/site-packages/django/core/management/base.py", line 398, in execute
    output = self.handle(*args, **options)
  File "/home/foo/main/pro/web/foo/utils/management/commands/utils_django_cte.py", line 43, in handle
    campaigns_cte = With.recursive(make_campaigns_cte)
  File "/home/foo/main/env/lib/python3.9/site-packages/django_cte/cte.py", line 53, in recursive
    cte.query = make_cte_queryset(cte).query
  File "/home/foo/main/pro/web/foo/utils/management/commands/utils_django_cte.py", line 25, in make_campaigns_cte
    return Campaign.objects.filter(
  File "/home/foo/main/env/lib/python3.9/site-packages/django/db/models/query.py", line 1091, in annotate
    return self._annotate(args, kwargs, select=True)
  File "/home/foo/main/env/lib/python3.9/site-packages/django/db/models/query.py", line 1130, in _annotate
    clone.query.add_annotation(
  File "/home/foo/main/env/lib/python3.9/site-packages/django_cte/query.py", line 49, in add_annotation
    super(CTEQuery, self).add_annotation(annotation, *args, **kw)
  File "/home/foo/main/env/lib/python3.9/site-packages/django/db/models/sql/query.py", line 1062, in add_annotation
    annotation = annotation.resolve_expression(self, allow_joins=True, reuse=None,
  File "/home/foo/main/env/lib/python3.9/site-packages/django_cte/expressions.py", line 45, in resolve_expression
    for cte in get_query(clone)._with_ctes:
AttributeError: 'Query' object has no attribute '_with_ctes'

Any idea?

thanks!

CTESubqueryResolver is not in the latest release

Greatly appreciate the work you guys have done on this package,

I noticed support for OuterRef in the Subquery was added in #19, but it is not available in the latest release on pypi (1.1.5). I would greatly appreciate if you could do another release soon with this functionality.

Ability to query CTE without joining

So I have a CTE that I want to aggregate over

WITH contrived as (SELECT
  "mytable"."category",
  SUM("mytable"."thing") as "total_thing"
FROM "mytable"
GROUP BY "mytable"."category")
SELECT 
  SUM("contrived"."total_thing") as "all_the_things"
FROM "contrived"

Basically I have an original query that I just want to aggregate stats for.

Trying to do this with the following

purchases = With(get_purchases(profile, start_date, end_date), name="purchases")
queryset = Purchase.objects.with_cte(
    purchases
).aggregate(
    total_sales=Sum(purchases.col.total_sales)
)

Add custom filters on CTE after join

Hello,
i am using django-cte with django-filter, at the moment i am stuck at adding filters on CTE.

I have two models:

class Strategy(models.Model):
    symbol = models.ForeignKey(Symbol, on_delete=models.CASCADE)
    timeframe = models.ForeignKey(Timeframe, on_delete=models.CASCADE)

class Trade(models.Model):
    strategy = models.ForeignKey(Strategy, on_delete=models.CASCADE, related_name='trades')
    profit = models.DecimalField(max_digits=10, decimal_places=5, blank=True, null=True)
    entry_time = models.DateTimeField(blank=True, null=True)   
    balance = models.DecimalField(max_digits=10, decimal_places=5, blank=True, null=True)

and this query:

cte = With(
    models.Trade.objects.annotate(
        drawdown = Window(
            expression=Max('balance'),
            partition_by=[F('strategy_id')],
            order_by=F('id').asc(),
        ) - F('balance'),              
        gain_op=Case(
            When(profit__gte=0, then=1),
            default=0,
            output_field=IntegerField(),
        ),       
        loss_op=Case(
            When(profit__lt=0, then=1),
            default=0,
            output_field=IntegerField(),
        )                                                                                            
    )
)

qs = (
    cte.join(models.Strategy, id=cte.col.strategy_id)
    .with_cte(cte)            
    .annotate(
        drawdown=Max(cte.col.drawdown),                
        ratio=Sum(cte.col.gain_op) * 100 / (Sum(cte.col.gain_op) + Sum(cte.col.loss_op)),                                
    )                
    .order_by("drawdown")            
)

the query works good, i return it as querySet to let django-filter apply its filters.
My problem is that i also need to add a filter on the CTE too, in my case i need a filter like:

.filter(entry_time__gt=value)

the problem is that the filters are applied all at once (after the querySet creation), i must add the CTE in the "main" query, otherwise the annotate() will return an error when i call cte.col.drawdown
For this reason i hope there is a way to "override" or inject filters on the on the CTE.

What is the correct approach?

Chaining With Clauses

I was wondering if its possible to chain with statements, like so:

with cte_1 as ( ... ),
cte_2 as ( select * from my_table t1 join cte_1 as t2 on t2.x = t1.y )
select
    *
from
    some_table t1
    join cte_1 as t2 on t2.x = t1.y
    join cte_2 as t3 on t3.a = t1.b

Currently, when I try chaining using multiple join or with_cte, I end up getting cte_1 nested within cte_2.

I have tried the following (but it does not work):

cte_1 = With(
    Model1.objects.filter(**filters)
)

cte_2 = cte_1.join(Model2, field1=cte_1.col.field2).with_cte(cte_1).filter(**filters2)

final_query = cte_2.join(Model3, field1=cte_2.col.field2).with_cte(cte_2).filter(field2=cte_1.col.field2)

Support UNION queries that share a CTE

I would like to write queries of the form:

WITH cte AS (SELECT * FROM ....)
(SELECT * FROM cte WHERE ...
UNION
SELECT * FROM cte WHERE ...)

Unfortunately, it seems that when trying to build such a query, the WITH clause is no added to the query. I believe this is due to the first if clause in CTECompiler. generate_sql.

I'm happy to put up a PR to remove the clause and attempt to make it work, but curious if there's a more fundamental not adding the WITH clause to queries with combinations?

Improve documentation

I am so pleased to found this, as it helped me solve a hairy problem that Django was stuck on (Windowing on a Windowed field). I've long wanted in Django a feature that can turn a QuerySet into the subject of another query.

But I admit I would like to understand what is going on here. I dump the SQL and like what I see, and it works for me, but I'm following the README scrip without comprehension.

cte = With(
    Order.objects
    .values("region_id")
    .annotate(total=Sum("amount"))
)

orders = (
    cte.join(Order, region=cte.col.region_id)
    .with_cte(cte)
    .annotate(region_total=cte.col.total)
    .order_by("amount")
)

What role do With, .join and .with_cte play here. It seems clunky syntax and there is no explanation of these component parts are doing.

As an ignoramus who has no clue what they do, I experimented of course, not least with the simple syntax I'd love to enjoy:

orders = cte.annotate(region_total=cte.col.total).order_by("amount")

which fails, of course.

I can see what the .join() does I guess in the resulting SQL, arises because the CTE is flexible and can be joined with other tables.

But could I for example select FROM the CTE? Without a join?

The .with_cte puzzles me as cte is at the head and self to the join which could also provide same downstream, and it's a puzzle why cte is passed back into a cte chain. It would be nice to understand why.

On the whole, very happy to have found this, and it's been empowering, but I would like understanding as well if I can get to it. I may drill down into the code a little to get that.

Prefetch objects with their nested children

I have this comment model that i need to get all the parent comments and their children, then prefetch it to my main queryset, this is what i got so far:

def recursive_comments(cte):
    return AnalysisComment.objects.filter(
        parent__isnull=True
    ).union(
        cte.join(AnalysisComment, parent_id=cte.col.id),
        all=True,
    )

cte = With.recursive(recursive_comments)
comments = cte.join(AnalysisComment, parent__isnull=True).with_cte(cte).select_related('user__profile').distinct()

qs = self.queryset

qs = qs.prefetch_related(
            Prefetch(
                "comments",
                queryset=comments,
                to_attr="parent_comments",
            ),
        )

I also need to use select_related('user__profile') on each comment object.
this is my Comment model:

class AnalysisComment(TimeStampMixin):
    """
    Represents comments made on Analysis.
    """
    parent = models.ForeignKey(
        "self", on_delete=models.CASCADE, null=True, blank=True, related_name="children"
    )
    uuid = models.UUIDField(
        verbose_name=_("UUID"),
        max_length=36,
        help_text=_("Unique identifier."),
        default=uuid.uuid4,
    )
    user = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        verbose_name=_("User"),
        on_delete=models.CASCADE,
        related_name="analyses_comments",
    )
    text = models.TextField(_("Text"))
    analysis = models.ForeignKey(
        Analysis,
        verbose_name=_("Analysis"),
        related_name="comments",
        on_delete=models.CASCADE,
    )
    objects = CTEManager()

now the problem is when i call the one of my parent comment children -> qs[0].parent_comments[0].children.all() then it calls queries again.
Can you help me how can i fix that?

With(empty_qs) throws Error

Got this error when I had an empty query set in a With clause.

AttributeError: 'CTEQueryCompiler' object has no attribute 'col_count'

Can we get another release

I'm running into an issue where QJoin has not attribute join_field which I see was resolved in 0fc7da7

@millerdev could we get a 1.1.6 release?

Allow non-recursive CTEs to avoid optimization fence

In Postgres 12 the default behavior of CTEs changed. They're no longer materialized if they are non-recursive, side-effect free and only appear once in the query: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=608b167f9f9c4553c35bb1ec0eab9ddae643989b

django-cte makes all CTEs recursive: https://github.com/dimagi/django-cte/blob/master/django_cte/query.py#L85

It would be nice to have the option to have non-recursive CTEs to avoid them being optimization fences.