Created
January 23, 2018 04:46
-
-
Save martsberger/c9971b553738869a87af29f52c4085cd to your computer and use it in GitHub Desktop.
Azee Risk Subqueries
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# You can put the EqualsAny class in a utility file somewhere, It's necessary | |
# in order to compare the result of ArrayAgg with a column | |
from django.db.models import Field | |
@Field.register_lookup | |
class EqualsAny(Lookup): | |
""" | |
Adds the `__any` lookup for generating SQL like: | |
SELECT * | |
FROM table | |
WHERE column = ANY('{1, 2, 3}'); | |
With ORM code like: | |
``` | |
Table.objects.filter(column__any=[1, 2, 3]) | |
``` | |
""" | |
lookup_name = 'any' | |
def as_sql(self, compiler, connection): | |
lhs, lhs_params = self.process_lhs(compiler, connection) | |
rhs, rhs_params = self.process_rhs(compiler, connection) | |
params = lhs_params + rhs_params | |
return '%s = ANY(%s)' % (lhs, rhs), params | |
# End utility section | |
# Add an array aggregation of Risk ids to your annotation0 | |
from django.contrib.postgres.aggregates import ArrayAgg | |
annotation0 = { | |
'AcRiskIntensity': Sum('RelatedRisk__RiskIntensity'), | |
'RiskIds': ArrayAgg('id') | |
} | |
# Now sub_filter0 can just match the aggregate Ids, eliminating joins | |
# from subquery0, subquery1, and subquery3 | |
sub_filter0 = Q(id__any=OuterRef('RiskIds')) |
So, this is something that to me looks like it might be a bug in Django where it tries to add something to the group by that doesn't need to be there. Fortunately, we can work around it.
Currently you have:
subquery0 = Risk.objects...
result = result.annotate(MaxRiskIntensity=Subquery(subquery0))
You'll need to change this to:
subquery0 = Subquery(Risk.objects...)
subquery0.get_group_by_cols = lambda: [] # Small hack to prevent the subquery from getting added to the group by
result = result.annotate(MaxRiskIntensity=subquery0)
Thanks.
Well that's odd. Unfortunately I'm still getting that ugly error:
Traceback (most recent call last):
File "C:\Program Files\Python36\lib\site-packages\django\db\backends\utils.py"
, line 65, in execute
return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: aggregate functions are not allowed in GROUP BY
LINE 1: ... U0."id", U2."RoutePart_id" HAVING U0."id" = ANY((ARRAY_AGG(...
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thanks. I really appreciate it.
I applied the code. However, I am getting the error: