Django complex filtering of related objects

Question

Hello, I've faced with following issue, basically I do it like this:

class Account(models.Model):
    TraffPerMonth = models.IntegerField(default=0)
    Tariff = models.ForeignKey('Tariff')

class Tariff(models.Model):
    InetTraff = models.IntegerField(default='0')

and here is the select:

for user in Account.objects.all():
  t_traff = long(user.Tariff.InetTraff)
 if u_traff >= t_traff:
            #do something
            pass

it takes some time, in most cases I waste processor time, doing lookups through users which are not overuse their limit. Here is simple SQL query which do what I need:

select ba.TraffPerMonth from billing_account ba , billing_tariff bt where ba.TraffPerMonth > bt.InetTraff group by ba.id;

How can I acheave query above with Django? I've tried search in google, but what I saw is examples if something is NULL, and in my example I need to compare with some value.

Thank you in advance.

Answer 1

I don't understand your SQL query - you are grouping by account.TraffPerMonth but selecting a single value rather than an aggregate, which doesn't make any sense. And the join is also incorrect.

However, I'll assume you want to join users to their tariffs via the ForeignKey, and select users who have used more than their tariff's allowance. That is quite simple:

accounts = Account.objects.filter(Tariff__InetTraff__gte=F('TraffPerMonth'))

You need to use an F() object there because you are comparing items within the same (joined) row of the query, rather than comparing an item in the query with a constant.

A quick style point: in Python you should use lower_case_with_underscore style for attributes such as model fields, and reserve InitialCaps style for models and other classes.