Django: filtering models on the difference of Counts of related models

Question

I've got a bunch of Order objects, each connected to one or more OrderRow objects (visible as a reverse relation to their parent Orders with Order.order_rows.all()).

Each OrderRow has a collection_status attribute, which can be 'collected', 'uncollected' or a bunch of other special values. Each Order has a status attribute, one of the valid values being 'processing'.

I'm at a loss trying to build an Order QuerySet that lists Order objects with the following criteria: Order status is 'processing', the count of its collection_status='collected' OrderRows is less than the total count of its OrderRows. Orders that have been not at all collected or partially collected, but not fully collected, that is.

To put it in an explicit way:

Order with two rows, both 'uncollected': included in the QS
Order with three rows, one 'collected', two 'uncollected': included in the QS
Order with two rows, both 'collected': NOT included in the QS!

(You can replace 'uncollected' with any other value that isn't 'collected' and the criteria is still the same; it's 'collected' vs. any other collection_status)

I've gotten as far as Order.objects.filter(status__exact='processing'), but beyond that, all my attempts at annotations, Q() objects etc. have failed miserably.

Answer 1

I reread your question at least 30 times. Let me see if I understand what you need.

this won't work because any Order with a OrderRow.collection_status=='collected' would be excluded

Order.objects.filter(status='processing').exclude(order_row__collection_status='collected')

Lets try something else

orders=Order.objects.filter(status='processing')
arr=[]
for order in orders:
  col=order.order_rows.filter(collection_status='collected')
  if col.count() < order.order_rows.all().count():
    arr.append(order.pk)

orders=orders.filter(pk__in=arr)

Just to clarify

the count of its collection_status='collected' OrderRows is less than the total count of its OrderRows

After reading your question for a 31st time it seems that the total number of OrderRows any Order could have will always be >= the number of 'collected' OrderRows

Should an order with 29 rows, 14 collected and 15 other be included? What about an order with 29 rows, 15 collected and 14 other?