Django models - how to filter number of ForeignKey objects

Question

I have a models A and B, that are like this:

class A(models.Model):   title = models.CharField(max_length=20)   (...)  class B(models.Model):   date = models.DateTimeField(auto_now_add=True)   (...)   a = models.ForeignKey(A) 

Now I have some A and B objects, and I'd like to get a query that selects all A objects that have less then 2 B pointing at them.

A is something like a pool thing, and users (the B) join pool. if there's only 1 or 0 joined, the pool shouldn't be displayed at all.

Is it possible with such model design? Or should I modify that a bit?

Answer 1

The question and selected answer are from 2008 and since then this functionality has been integrated into the django framework. Since this is a top google hit for "django filter foreign key count" I'd like to add an easier solution with a recent django version using Aggregation.

from django.db.models import Count cats = A.objects.annotate(num_b=Count('b')).filter(num_b__lt=2) 

In my case I had to take this concept a step further. My "B" object had a boolean field called is_available, and I only wanted to return A objects who had more than 0 B objects with is_available set to True.

A.objects.filter(B__is_available=True).annotate(num_b=Count('b')).filter(num_b__gt=0).order_by('-num_items') 

Answer 2

Sounds like a job for extra.

A.objects.extra(     select={         'b_count': 'SELECT COUNT(*) FROM yourapp_b WHERE yourapp_b.a_id = yourapp_a.id',     },     where=['b_count < 2'] ) 

If the B count is something you often need as a filtering or ordering criterion, or needs to be displayed on list views, you could consider denormalisation by adding a b_count field to your A model and using signals to update it when a B is added or deleted:

from django.db import connection, transaction from django.db.models.signals import post_delete, post_save  def update_b_count(instance, **kwargs):     """     Updates the B count for the A related to the given B.     """     if not kwargs.get('created', True) or kwargs.get('raw', False):         return     cursor = connection.cursor()     cursor.execute(         'UPDATE yourapp_a SET b_count = ('             'SELECT COUNT(*) FROM yourapp_b '             'WHERE yourapp_b.a_id = yourapp_a.id'         ') '         'WHERE id = %s', [instance.a_id])     transaction.commit_unless_managed()  post_save.connect(update_b_count, sender=B) post_delete.connect(update_b_count, sender=B) 

Another solution would be to manage a status flag on the A object when you're adding or removing a related B.

B.objects.create(a=some_a) if some_a.hidden and some_a.b_set.count() > 1:     A.objects.filter(id=some_a.id).update(hidden=False)  ...  some_a = b.a some_b.delete() if not some_a.hidden and some_a.b_set.count() < 2:     A.objects.filter(id=some_a.id).update(hidden=True)