It is my opinion that the Subquery object that already exists in Django should act as a CTE. There is a ticket for this feature already: https://code.djangoproject.com/ticket/28919
I have noticed that several developers have proposed several different patches for CTEs, but I do not know why they have never made it in. Perhaps someone with more knowledge on that matter can help. From: django-developers@googlegroups.com <django-developers@googlegroups.com> On Behalf Of Alexandr Tatarinov Sent: Monday, April 6, 2020 3:22 AM To: Django developers (Contributions to Django itself) <django-developers@googlegroups.com> Subject: Subquery join support Hello folks, Following the discussion https://groups.google.com/forum/#!topic/django-developers/b370mxfKCHg, I would like to suggest adding the ability to join QuerySet with a subquery clause to Django. The benefits are pretty much described and discussed in the linked topic, the only one I would like to add (my use case) is the ability to select multiple columns from a Subquery without the need to repeat the subquery clause, which hurts performance. As Anssi Kääriäinen mentioned, generic subquery supports seems to be a better idea than CTEs, and simple implementation as a starting point will allow moving gradually and add RETURNING support later on (for nested creates and updates). I have created a working prototype and successfully using it in one of my projects in production. After reading Daniel Miller's https://github.com/dimagi/django-cte, I've rewritten the API inspired by his ideas, plus tried to keep it as Django-style as possible. Here is the usage example. taxes = JoinedSubquery(Tax.objects.all()) products = Product.objects.all() # .join is probably better be placed in QuerySet class, so it will be products.join(taxes, ...) products_with_taxes = taxes.join( products, join_type=LOUTER, # Optional argument, LOUTER by default country=taxes.F('product_country'), # Also Q objects are supported type=taxes.F('product_type') ).annotate( tax_amount=taxes.F('amount') # .join() and .annotate() calls needn't to be chained ) I am not sure about named joins discussed in the CTE topic qs.attach(q1=some_qs).filter(a=F('q1__b')) It seems to be more implicit and harder to implement, requiring changes to existing functionality and thus introducing a big patch which I would like to avoid. It is not possible to follow relations in join.F expressions, but it is easily achievable by firstly fetching needed columns by annotate(), so I don't see the need to implement it. Source code is available here https://gist.github.com/tatarinov1997/126c49c4b1bb44ae6c57afbc5f43f58d , tested with Django 2.2. Feel free to copy and play around. So, please let me know what do you think about it. I am willing to continue working on it, and any help is appreciated. Thanks, Alexandr. -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com<mailto:django-developers+unsubscr...@googlegroups.com>. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/8c41e40f-876c-46c0-9e24-09e8c72592ce%40googlegroups.com<https://groups.google.com/d/msgid/django-developers/8c41e40f-876c-46c0-9e24-09e8c72592ce%40googlegroups.com?utm_medium=email&utm_source=footer>. -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/1a5ac0adfa6a4b2a98b1a7862a6a2246%40iss2.ISS.LOCAL.
