Use a model field to query another model field in django

Question

I have two models in our django app

class Reg(models.Model):
   transactions = ManyToMany
   price = IntegerField

class Transaction(models.Model)
   amount = IntegerField

Now I would like to make a lookup like:

Registration.objects.filter(reg__price==transaction__amount)

Previously we used the following approach:

Registration has a property is_paid that computes wether a transaction with equal amount exists
[r for r in Registration.objects.filter(...) if r.is_paid]

This is ofc very query-consuming and inefficient. I wonder whether there would be a better way to do this! Any hint is appreciated :)

You did not construct the fields, you should write IntegerField() and ManyToMany('Transaction'). — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Jan 19, 2018 at 13:22
Furthermore it is not very clear when an invoice is paid? If the sum of the transactions sum up to amount? — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Jan 19, 2018 at 13:23
@WillemVanOnsem this was obviously pseudo-code. For the second though, well, if enough transaction amount exists to cover the price == paid — Fohlen
– Fohlen, Commented Jan 19, 2018 at 13:28

user2390182 · Accepted Answer · 2018-01-19 14:24:04Z

2

You can use an F expression for such a query:

from django.db.models import F

Registration.objects.filter(price=F('transactions__amount'))

This will filter all Registration instances whose price is equal to one of their transactions' amount. If you want all transactions amounts' sum to be equal or more than the registration price, you can use annotations to aggregate each registration's Sum:

paid_registrations = Registration.objects.\
   annotate(ta=Sum('transactions__amount')).\  # annotate with ta sum
   filter(price__lte=F('ta'))  # filter those whose price is <= that sum

edited Jan 19, 2018 at 14:24

answered Jan 19, 2018 at 13:24

user2390182

73.7k6 gold badges71 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use a model field to query another model field in django

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related