dimagi / django-cte Goto Github PK
View Code? Open in Web Editor NEWCommon Table Expressions (CTE) for Django
License: Other
Common Table Expressions (CTE) for Django
License: Other
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?
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
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'
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()
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.
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)
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?
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?
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
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__
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
Hi, how are you supposed to use this package without (or even with) a date series queryset, in a GENERATE_SERIES()
fashion?
Thanks.
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?
See #1 for a possible solution.
>>> 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.
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!
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
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
Line 89 in 88843ef
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
),
),
)
Got this error when I had an empty query set in a With clause.
AttributeError: 'CTEQueryCompiler' object has no attribute 'col_count'
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
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 %@gmail.com
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 %@yahoo.com
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 %@gmail.com) 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 %@yahoo.com)
# 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 %@gmail.com) 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 %@yahoo.com)
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...
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.
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?
Specifically, https://github.com/dimagi/django-cte/blob/master/django_cte/cte.py#L103 refers to .objects
, where it should refer to ._default_manager
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)
)
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?
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?
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
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?
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?
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.
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?
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 << ----------------------
(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'
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 ?
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=???)
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.
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
$ ./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
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?
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!
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.
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.