Azee Risk Subqueries

risk_subquery.py

# 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'))

Thanks. I really appreciate it.
I applied the code. However, I am getting the error:

psycopg2.ProgrammingError: aggregate functions are not allowed in GROUP BY
LINE 1: ... U0."id", U2."RoutePart_id" HAVING U0."id" = ANY((ARRAY_AGG(...

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