CODEWITHSUNDEEP
pythondjangodate
Miki Torandell
Miki Torandell

Reputation: 183

Django filter foreign key by booking dates

I have the following model:

class House(models.Model): 
   Name = models.CharField(max_length=200, unique=True) 
   Reference = models.CharField(max_length=50, unique=True)

class Booking(models.Model): 
   House = models.ForeignKey(House, related_name='booking')
   InitialDate = models.DateField()
   FinalDate= models.DateField()

Now, I want to perform a query to filter all the AVAILABLE houses for a range date (eg: 2013-07-21 to 2013-07-30), so It should exclude all the houses with a booking starting and ending between those two dates. I would be able to perform this query with raw SQL but no with the django syntax.

Could anyone please help?

Many thanks!

Upvotes: 1

Views: 1245

Answers (4)

Henrik Andersson
Henrik Andersson

Reputation: 47212

When using the Django ORM one of the practices used is that you should always start with the relationship you want to end up with, in your case House.

I argue that this would be your way of going about it then:

unbooked_houses = House.objects.exclude(
    booking__InitialDate__gte=start_date, booking_FinalDate__lte=end_date)

This way you'll end up with a QuerySet and need not bother with list comprehensions and whatnot.

Furthermore, Python PEP-8 document dictates that you should follow the following naming convention for variables and properties:

Instead of InitialDate it should preferably be initial_date.

Upvotes: 4

christophe31
christophe31

Reputation: 6467

I took the freedom to use a more pythonic case.

class House(models.Model): 
    name = models.CharField(max_length=200, unique=True) 
    reference = models.CharField(max_length=50, unique=True)

class Booking(models.Model): 
    house = models.ForeignKey(House, related_name='booking')
    initial_date = models.DateField()
    final_date= models.DateField()

import datetime
start_date = datetime.datetime(2013, 07, 21)
end_date = datetime.datetime(2013, 07, 30)

# so simply intersect
booked = set(values['house_id'] for values in Booking.objects.objects.filter(
    initial_date__lte=end_date, final_date__gte=start_date).values('house_id'))

House.objects.exclude(id__in=booked)

thanx to richsilv for the set idea, logical but I thaught about it by reading his answer. It simply reduce the sql statement by only using distinct houses.

Upvotes: 3

bambata
bambata

Reputation: 313

It is possible to directly filter thanks to the reverse relation created by Django :

bookedHouses = House.objects.filter(booking__InitialDate__lte=enddate)\ .filter(booking__FinalDate__gte=startdate)

Upvotes: 0

richsilv
richsilv

Reputation: 8013

Assuming the period you're talking about is between startdate and enddate, would this work:

booked = set([x.House.Reference for x in \
   Booking.objects.filter(InitialDate__lte=enddate).filter(FinalDate__gte=startdate)])
available = House.objects.exclude(Reference__in=booked)

Upvotes: 0

Related Questions