Create django query satisfying two conditions involving related models-CodePudding

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.

CodePudding user response：

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