Django query with simple arithmetic among model fields and comparison with field from another model

Question

(All code below is a simplified representation of the actual code).

If I have the following Django models: -

class Material(models.Model):
    name = models.CharField(max_length=110)

class OrderDetail(models.Model):
    material = models.ForeignKey(Material)
    order_quantity = models.IntegerField()
    quantity_delivered = models.IntegerField(null=True, blank=True)
    is_open = models.BooleanField()
    is_active = models.BooleanField()

class MaterialRequirement(models.Model):
    material = models.ForeignKey(Material)
    quantity = models.IntegerField()

I have with me an instance of the MaterialRequirement model, let's call it material_requirement. I am trying to retrieve OrderDetail objects which satisfy the following criteria:-

1. material = material_requirement.material

2. is_open = True

3. is_active = True

4. order_quantity > quantity_delivered

5. material_requirement.quantity <= order_quantity - quantity_delivered

My question is how do I incorporate the 5th selection criteria in my ORM query below.

order_details = OrderDetail.objects.select_related('material').filter(material=material_requirement.material,
                                                                    is_open=True,
                                                                    is_active=True,
                                                                    order_quantity__gt=F('quantity_delivered'))

Apologize to all - point no. 5 should be material_requirement.quantity <= order_quantity - quantity_delivered and not material_requirement.quantity >= order_quantity - quantity_delivered. I have corrected the question. Again, apologies. — chefsmart, Mar 23 '13 at 12:25

score 2 · Accepted Answer · answered Mar 23 '13 at 12:13

2

order_details = (OrderDetail.objects.select_related('material')
                 .filter(material=material_requirement.material,
                         is_open=True,
                         is_active=True,
                         order_quantity__gt=F('quantity_delivered'),
                         quantity_delivered__gte=F('order_quantity') - material_requirement.quantity,
                         )
)

answered Mar 23 '13 at 12:13

Davide R.

860
5
24

There was a mistake in my question, please see the edit notes. Anyway, this is what I went with - blame my mental spaghetti for getting confused. I ended up using order_details = OrderDetail.objects.select_related('material').filter(material=material_requirement.material, is_open=True, is_active=True, order_quantity__gte=F('quantity_delivered') + material_requirement.quantity) – chefsmart Mar 23 '13 at 14:27
You might want to update your answer to reflect the rectified question, thanks. – chefsmart Mar 23 '13 at 14:28

score 0 · Answer 2 · answered Mar 23 '13 at 11:46

0

order_details = order_detail.extra(where=["order_quantity - quantity_delivered <= %d" % material_requirement.quantity]))

answered Mar 23 '13 at 11:46

Pawel Furmaniak

4,648
3
29
33

Django query with simple arithmetic among model fields and comparison with field from another model

2 Answers2

Linked