How to apply lookups in django filter query according to conditions?-CodePudding

I want to make a query where i can apply lookups only when some conditions satisfy. e.g

A customer can pick only that food from stall for which he paid for

class Customer(models.Model):
    food_type = models.CharField()
    fruit_id = models.ForeignKey(Fruit, null=True)
    vegetable_id = models.ForeignKey(Vegetable, null=True)
    is_paid = models.BooleanField()
    
class Food(models.Model):
    fruit_id = models.ForeignKey(Fruit, null=True)
    vegetable_id = models.ForeignKey(Vegetable, null=True)

So i need to do something like:

q = Food.objects.all()
if Customer.objects.filter(id=(id of customer), food_type='fruit', is_paid=True).exists():
    q = q.filter(fruit_id__in=Customer.objects.filter(id=(id of customer), food_type='fruit', is_paid=True).values_list('fruit_id', flat=True))

if Customer.objects.filter(id=(id of customer), food_type='vegetable', is_paid=True).exists():
    q = q.filter(vegetable_id__in=Customer.objects.filter(id=(id of customer), food_type='vegetable', is_paid=True).values_list('vegetable_id', flat=True))

How can i optimize this as this is a small example, in my case there are many more conditions. I want to reduce the number of times it is hitting the database. Also is there any way i can use conditional expressions here? e.g When()

Any help will be appreciated.

CodePudding user response：

You can use Q.

from django.db.models import Q

q = Food.objects.all()
q = q.filter(
    Q(
       vegetable_id__in=Customer.objects.filter(id=(id of customer), food_type='vegetable', is_paid=True).values_list('vegetable_id', flat=True)
    )|Q(
       fruit_id__in=Customer.objects.filter(id=(id of customer), food_type='fruit', is_paid=True).values_list('fruit_id', flat=True)
    )
)

CodePudding user response：

The reason for the complexity is because of the structure of your models - you have the Customer connected to Fruit and/or Vegetables and Food also connected to Fruit/Vegetables, so Customer and Food only potentially connect through Fruit/Vegetable - very confusing and long-winded.

It also explains why you have 'food_type' on your Customer model, to try and compensate. I would suggest a much simpler structure but with one additional model:

class Customer(models.Model):
    food = models.ManyToManyField(Food, ... # customer can have many Food
    is_paid = models.BooleanField()

# create a new food type model that stores Fruit, Vegetable, etc.
class FoodType(models.Model):
    name = models.CharField(...

class Food(models.Model):
    # food now connects to the food type
    food_type = models.ForeignKey(FoodType, ...

You can now keep your queries far more concise:

# get food that the customer has paid for
Food.objects.filter(customer__is_paid=True)

# get fruit that the customer has paid for
Food.objects.filter(customer__is_paid=True, food_type__name="Fruit")