Create django query satisfying two conditions involving related models

user_5206417 0
Viewed 233

I have two models, let's say:

class Order(models.Model):
  # ... fields

class Product(models.Model):
  quantity = models.PositiveIntegerField(null=False, blank=False, default=1)
  order = models.ForeignKey(Order, on_delete=models.CASCADE, related_name='products')
  b_product = models.ForeignKey(BaseProduct, null=False, blank=False, on_delete=models.CASCADE)

class BaseProduct(models.Model):
  type = #.... choices = [rtProduct, rtPack]

I want to make a query that includes all the Orders which have more than one Product related to it, or if at least one Product has a quantity greater than 1, or if the BaseProduct type is rtProduct

For the first part I have this:

queryset = Order.objects.annotate(products_count=Count('products')).filter(products_count__gt=1)

I am stuck on adding the OR condition to also include the other condition. Thank you so much in advance.

django-rest-framework django-queryset django
1 Answers

You can sum up the quantity and determine if the Order has two items or more with:

from django.db.models import Sum

Order.objects.alias(
    num_items=Sum('product__quantity')
).filter(num_items__gt=1)

or prior to django-3.2 with:

from django.db.models import Sum

Order.objects.annotate(
    num_items=Sum('product__quantity')
).filter(num_items__gt=1)

If it is possible to have an Order object with quantity less than or equal to one, we should do this differently, and work with:

from django.db.models import Count, Exists, OuterRef, Q

Order.objects.alias(
    n_items=Count('product')
).filter(
    Q(n_item__gt=1) | Exists(
        Product.objects.filter(order_id=OuterRef('pk'), quantity__gt=1)
    )
)

or prior to django-3.2:

from django.db.models import Count, Exists, OuterRef, Q

Order.objects.annotate(
    n_items=Count('product')
).filter(
    Q(n_item__gt=1) | Exists(
        Product.objects.filter(order_id=OuterRef('pk'), quantity__gt=1)
    )
)
user_675790
Related