no such table: cte while importing fixtures

[dimagi]

no such table: cte while importing fixtures

[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.