CODEWITHSUNDEEP
pythondjangopostgresqldjango-queryset
ZmuA
ZmuA

Reputation: 161

Django Query with conditional function When (?)

I have a database of a diner:

class Product(models.Model):
    name = models.CharField(max_length=250)
    sides = models.ManyToManyField(Sides, blank=True)
    price = models.DecimalField(max_digits=5, decimal_places=2)

    def __unicode__(self):
        return '%s %s %s' % (self.name, self.sides, self.price)

    def __repr__(self):
        return unicode(self).encode('utf-8')


class Menu(models.Model):
    date = models.DateField()
    product = models.ForeignKey(Product)

    def __unicode__(self):
        return '%s %s' % (self.date, self.product)

    def __repr__(self):
        return unicode(self).encode('utf-8')


class Order(models.Model):
    date = models.DateField()
    user = models.ForeignKey(User, null=True)

    def __unicode__(self):
        return '%s %s' % (self.date, self.user)

    def __repr__(self):
        return unicode(self).encode('utf-8')


class OrderItems(models.Model):
    order = models.ForeignKey(Order)
    product = models.ForeignKey(Menu, null=True, blank=True)
    quantity = models.IntegerField(default=0)
    take_away = models.BooleanField(default=False)

    def __unicode__(self):
        return '%s %s %s %s' % (self.order, self.product, self.quantity, self.take_away)

    def __repr__(self):
        return unicode(self).encode('utf-8')

I need to search the database for the cost of dishes ordered by my users in a given period, so the first query is quite simple:

report = OrderItems.objects.filter(Q(order__date__range=(strfrom, strto)), ~Q(order__user_id=None))

The problem begins when it comes to calculation of total cost per user in this period, because I have to add 1 to each item that has been marked as take away (boolean field). My query so far, that ignores the boolean field, is like that:

per_person = report.values('order__user').annotate(per_capita=Sum(F('quantity')*(F('product__product__price')), output_field=DecimalField()))

My question is: how can I add 1 to each item price that has the boolean field marked True? I assume that I have to use the conditional function 'When'.

I tried something like:

report.values('order__user').annotate(per_capita=Sum(F('quantity')*(F(When('take_away'==True, then='product__product__price')+1)|(When('take_away'==False, then='product__product__price'))), output_field=DecimalField()))

But, obviously, it gives an error __init__() takes either a Q object or lookups as keyword arguments.

Upvotes: 2

Views: 3223

Answers (1)

JoseKilo
JoseKilo

Reputation: 2443

You could use a Case-When. Check the example in https://docs.djangoproject.com/en/1.10/ref/models/conditional-expressions/#case

.annotate(per_capita=Sum(
    F('quantity') * (F('product__product__price') +
    Case(When(take_away=True, then=Value(1)),
         default_case=Value(0),
         output_field=DecimalField())
    )
))

Upvotes: 2

Related Questions