CODEWITHSUNDEEP
djangopostgresqldjango-queryset
Uri
Uri

Reputation: 3301

How do we query reverse foreign key attributes in Django?

We are using Django 1.4.12 with PostgreSQL to build a website for hotels. We have hotels, for each hotel we have rooms and in each room we have guests (a guest has a foreign key to a room, and a room to a hotel). After a guest checks out from a room, his departure_date is set to now, and if he is in the room, it is none. I want to select the rooms which have guests and the rooms which don't have guests, but I can't use "guests__isnull" because the guests remain connected to the room after they check out. I also need to search rooms when the guests' name contain a keyword, but only if they are in the room. If there is at least one guest in the room with a matching name, I want the room to be selected. But if this guest checked out, the room can't be selected.

Until a few days ago we removed the room of the guests after they checked out, but we need to keep it to save history. Our code was something like this:

hotel.rooms.filter(
    Q(description__icontains=keyword) | 
    Q(identifier__icontains=keyword) | 
    (Q(guests__isnull=False) & Q(guests__name__icontains=keyword))
).distinct()

and for checked in rooms we added .exclude(guests__isnull=True). But now we keep the room and we need to check if the departure_date is null or not. How do we do it now? Remember that we need to match the keyword with guests that are in the room, but not with guests that are not in the room. We also need to count the total number of rooms with guests and the total number of rooms without guests.

Update: I tried to count the number of rooms with guests with the following query:

hotel.rooms.filter(guests__isnull=False, guests__departure_date__isnull=True).count()

But it returns an incorrect number (14 instead of 10). Do you know how to fix the query?

By the way, this query returns the correct number of rooms without guests (9 rooms):

hotel.rooms.exclude(guests__isnull=False, guests__departure_date__isnull=True).count()

Why is the exclude correct and the filter is not correct?

Upvotes: 1

Views: 244

Answers (1)

Uri
Uri

Reputation: 3301

OK, I found a solution. It appears that with filter(...), the rooms are duplicated per the number of guests in the room, but with exclude they are not. There are 2 possible solutions to count the number of rooms with guests:

hotel.rooms.filter(guests__isnull=False, guests__departure_date__isnull=True).distinct().count()

or:

hotel.rooms.exclude(~Q(guests__isnull=False, guests__departure_date__isnull=True)).distinct().count()

To count the number of rooms without guests:

hotel.rooms.exclude(guests__isnull=False, guests__departure_date__isnull=True).distinct().count()

or:

hotel.rooms.exclude(Q(guests__isnull=False, guests__departure_date__isnull=True)).distinct().count()

And to search rooms:

hotel.rooms.filter(
    Q(description__icontains=keyword) |
    Q(identifier__icontains=keyword) |
    Q(guests__isnull=False, guests__departure_date__isnull=True, guests__name__icontains=keyword)
).distinct()

Upvotes: 1

Related Questions