Django ORM: count related objects with a feature

Question

Lets say we have House and Room models.
Each house has several rooms and each room has a number of beds.

How to select number of bedrooms in each house? All houses must appear in the result.

Model classes:

class House(models.Model):
    pass

class Room(models.Model):
    house = models.ForeignKey('House')
    beds = models.IntegerField()

I tried:

House.objects.filter(room__beds__gt=0).annotate(bedrooms=Count('room'))

But this solution does not contain houses without bedrooms.

I prefer not to use pure SQL.

ciobrelis · Accepted Answer · 2013-12-18 18:48:54Z

1

You can add houses without bedrooms to your query like this:

from itertools import chain
from django.db.models import Q, Count

qs = House.objects.all()
q = {'room__beds__gt': 0}
qs_with = qs.filter(Q(**q)).annotate(bedrooms=Count('room'))
qs_without = qs.filter(~Q(**q)).extra(select={'bedrooms': 0})
qs_all = chain(qs_with, qs_without)

answered Dec 18, 2013 at 18:48

ciobrelis

961 silver badge3 bronze badges

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

1 Comment

niekas Over a year ago

Perfect! I can use queryset filtering before calculating the bedrooms.

kanu · Accepted Answer · 2013-12-12 18:04:44Z

1

Give this a try.

House.objects.extra(select={
        "bedrooms": "SELECT count(*) FROM YOURAPP_room WHERE house_id=YOURAPP_house.id AND beds > 0"})

answered Dec 12, 2013 at 18:04

kanu

7265 silver badges9 bronze badges

4 Comments

niekas Over a year ago

I prefer not to use pure SQL. Any other suggestions?

kanu Over a year ago

I don't think this works in a single query without SQL.

niekas Over a year ago

This solution is practically unusable. This query takes 14 mins to load for 1k House and 100k Room records. While filtering by room__beds__gt=0 and annotating takes 5 seconds.

niekas Over a year ago

This SQL query ignores filter() applied before extra() call.

H H H · Accepted Answer · 2013-12-12 18:23:27Z

0

Add a related_name to your house field in Room class. It will look like this:

house = models.ForeignKey('House', related_name='rooms')

then you can access Room objects from House objects.

Now, if h1 is an object of House you can do something like this..

rooms_total = 0

for room in h1.rooms.all():
  rooms_total += room.beds

answered Dec 12, 2013 at 18:23

H H H

5291 gold badge7 silver badges21 bronze badges

2 Comments

niekas Over a year ago

I need to do this in a single query. This means you have to use Django Queryset API, no custom Python code can be used.

niekas Over a year ago

You don't have to add related_name to access the set of rooms. Default related_name is '<fieldname>_set' (e.g. room_set).

Collectives™ on Stack Overflow

Django ORM: count related objects with a feature

3 Answers 3

1 Comment

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related