Django: How To Annotate M2m Or Onetomany Fields Using A Subquery?

I have Order objects and OrderOperation objects that represent an action on a Order (creation, modification, cancellation). Conceptually, an order has 1 to many order operations. E

Solution 1:

ArrayAgg will be great if you want to fetch only one variable (ie. name) from all articles. If you need more, there is a better option for that:

prefetch_related

Instead, you can prefetch for each Order, latest OrderOperation as a whole object. This adds the ability to easily get any field from OrderOperation without extra magic.

The only caveat with that is that you will always get a list with one operation or an empty list when there are no operations for selected order.

To do that, you should use prefetch_related queryset model together with Prefetch object and custom query for OrderOperation. Example:

from django.db.models import Max, F, Prefetch

last_order_operation_qs = OrderOperation.objects.annotate(
    lop_pk=Max('order__orderoperation__pk')
).filter(pk=F('lop_pk'))

orders = Order.objects.prefetch_related(
    Prefetch('orderoperation_set', queryset=last_order_operation_qs, to_attr='last_operation')
)

Then you can just use order.last_operation[0].ordered_articles to get all ordered articles for particular order. You can add prefetch_related('ordered_articles') to first queryset to have improved performance and less queries on database.

Solution 2:

To my surprise, your idea with ArrayAgg is right on the money. I didn't know there was a way to annotate with an array (and I believe there still isn't for backends other than Postgres).

from django.contrib.postgres.aggregates.general import ArrayAgg

qs = Order.objects.annotate(oo_articles=ArrayAgg(
            'order_operation__ordered_articles__id',
            'DISTINCT'))

You can then filter the resulting queryset using the ArrayField lookups:

# Articles that contain the specified array
qs.filter(oo_articles__contains=[42,43])
# Articles that are identical to the specified array
qs.filter(oo_articles=[42,43,44])
# Articles that are contained in the specified array
qs.filter(oo_articles__contained_by=[41,42,43,44,45])
# Articles that have at least one element in common
# with the specified array
qs.filter(oo_articles__overlap=[41,42])

'DISTINCT' is needed only if the operation may contain duplicate articles.

You may need to tweak the exact name of the field passed to the ArrayAgg function. For subsequent filtering to work, you may also need to cast id fields in the ArrayAgg to int as otherwise Django casts the id array to ::serial[], and my Postgres complained about type "serial[]" does not exist:

from django.db.models import IntegerField
from django.contrib.postgres.fields.array import ArrayField
from django.db.models.functions import Cast

ArrayAgg(Cast('order_operation__ordered_articles__id', IntegerField()))
# OR
Cast(ArrayAgg('order_operation__ordered_articles__id'), ArrayField(IntegerField()))

Looking at your posted code more closely, you'll also have to filter on the one OrderOperation you are interested in; the query above looks at all operations for the relevant order.