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:

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.

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.

I tried doing this but it is really slow (like using the "extra" in answer 1 was almost instant but with this it took each entry more than 5 seconds). Is there a way that is faster without dropping into raw SQL?
–
daveelooJun 8 '11 at 23:57

OP should unaccept original and accept this one
–
jsjMar 18 '12 at 10:38

1

@llazzaro - no it doesn't. Django docs have never said "you shouldn't use SQL or you'll break the abstraction". If they had they wouldn't have let you do it. Of course you always avoid it, but sometimes you have to.
–
JoeNov 7 '12 at 11:29

1

@llazzaro fine, but the question is about Django. And the answer you were commenting on was about Django.
–
JoeNov 8 '12 at 13:02

I'd recommend modifying your design to include some status field on A.

The issue is one of "why?" Why does A have < 2 B's and why does A have >= 2 B's. Is it because user's didn't enter something? Or is because they tried and their input had errors. Or is it because the < 2 rule doesn't apply in this case.

Using presence or absence of a Foreign Key limits the meaning to -- well -- present or absent. You don't have any way to represent "why?"

Also, you have the following option

[ a for a in A.objects.all() if a.b_set.count() < 2 ]

This can be pricey because it does fetch all the A's rather than force the database to do the work.

A is something like a pool thing, and users (the B) join pool. if there's only 1 or 0 joined, the pool shouldnt be displayed at all... That's why I wanted to not include such status - would require me to watch for user join / user leaving the pool events. But, maybe that's the way...
–
kenderNov 3 '08 at 11:27

I assume that joining or leaving the pool may not happen as often as listing (showing) the pools. I also believe that it would be more efficient for the users join/leave actions to update the pool display status. This way, listing & showing the pools would require less time as you would just run a single query for SHOW_STATUS of the pool objects.