no such table: cte while importing fixtures

[JulienPalard]

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

[millerdev]

Are you trying to do what was described in this comment (construct a query with a FROM clause that does not include a model table)? I don't think that's currently possible.

[JulienPalard]

construct a query with a FROM clause that does not include a model table

Exactly :D

But the querying part do work, that's just the insertion part which is failing.

The SELECT is properly generated and it works without a model in the FORM:

>>> str(UserInfo.with_rank.all().query)
'WITH RECURSIVE cte AS (SELECT "issue39_userinfo"."id", "issue39_userinfo"."user_id", "issue39_userinfo"."points", DENSE_RANK() OVER (ORDER BY "issue39_userinfo"."points" DESC) AS "r" FROM "issue39_userinfo") SELECT "cte"."id", "cte"."user_id", "cte"."points", "cte"."r" AS "r" FROM "cte"'

I tried with the Django model as a FORM by slighly changing my return in def with_rank to:

    return (
        with_rank.join(UserInfo, user_id=with_rank.col.user_id)
        .with_cte(with_rank)
        .annotate(r=with_rank.col.r)
    )

I'm now having:

django.db.utils.OperationalError: Problem installing fixture '/tmp/cteissue/issue39/fixtures/initial.json': Could not load issue39.UserInfo(pk=1): near ")": syntax error

The query being:

WITH RECURSIVE cte AS (SELECT "issue39_userinfo"."id", "issue39_userinfo"."user_id", "issue39_userinfo"."points", DENSE_RANK() OVER (ORDER BY "issue39_userinfo"."points" DESC) AS "r" FROM "issue39_userinfo")
UPDATE "issue39_userinfo" SET "user_id" = ?, "points" = ?
WHERE "issue39_userinfo"."id" IN (
    WITH RECURSIVE cte AS (SELECT "issue39_userinfo"."id", "issue39_userinfo"."user_id", "issue39_userinfo"."points", DENSE_RANK() OVER (ORDER BY "issue39_userinfo"."points" DESC) AS "r" FROM "issue39_userinfo")
)

Using a CTE as base_manager_name may not be a good idea :D I was just hoping to make this work:

In [1]: from issue39.models import *
In [2]: User.objects.first().hkis.r  # Note `.r` is from the cte.
Out[2]: 1  # And yes it does work \o/

[millerdev]

construct a query with a FROM clause that does not include a model table

Actually, this is possible. It's covered in the newly written documentation: https://dimagi.github.io/django-cte/#selecting-from-a-common-table-expression

However, I'm not sure that is the real issue here. @JulienPalard Did you ever get this resolved?

[JulienPalard]

Hi! Currently an alteriative works, that I use in production: https://framagit.org/hackinscience/hkis-website/-/blob/2ad08645/hkis/models.py#L28

But the reproducer I liked in the first issue still raises the no such table: cte.