Django ListView queryset for available items

Question

So I want to display all available items for any given date, shouldn't be that hard but somehow I ran into a problem concerning related items.

Let's say we have the following models, a model to store all bookings and a model with the Item.

Then I would create ListView to retrieve all items available between any given dates. I override the queryset to retrieve the data filled in by the user.

This seems to be working but there's an issue, even though I check if the "form_start_date" or "form_end_data" are in conflict with existing bookings, when a single Item has multiple bookings it does not work.

example

Bookings [X] for item #01:
01-01-2019 to 01-03-2019
01-11-2019 to 01-18-2019

  Jan 2019   1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18  
 ---------- --- --- --- --- --- --- --- --- --- ---- ---- ---- ---- ---- ---- ---- ---- ---- 
  Item #01   X   X   X           O   O   O            X    X    X    X    X    X    X    X   
  Item #02       X   X   X   X   X                                                 

When I check for availablity [O] for 01-06-2019 to 01-08-2019, item #01 is not available, what am I missing here?

models.py

class Booking(models.Model):
    item = models.ForeignKey('Item', on_delete=models.SET_NULL, null=True)
    start_date = models.DateField()
    end_date = models.DateField()

class Item(models.Model):
    name = models.CharField(max_length=20, unique=True)

views.py

class AvailableItems(generic.ListView):
    model = Item

    def get_queryset(self):
        start_date = datetime.strptime(self.request.GET.get('start_date'), '%Y-%m-%d')
        end_date = datetime.strptime(self.request.GET.get('end_date'), '%Y-%m-%d')
        # As Willem suggested in the comments, it is easier to check for available items
        available_items = (
            Item.objects.filter(booking__start_date__gte = start_date, booking__end_date__lte = end_date)
        )

        if start_date and end_date:
            return available_items
        else:
            return Item.objects.all()

Answer 1

Let us first analyze when two intervals (f₁, t₁) and (f₂, t₂) overlap. A simpler problem to solve, is finding out when two intervals do not overlap. That holds for two cases:

1. given t₁ < f₂, since then the first event ends before the second; or
2. given t₂ < f₁, since then the first event ends before the second begins.

So that means that two events overlap given t₁ ≥ f₂ and t₂ ≥ f₁.

With this knowledge, we can design a filter like:

bookings = Booking.objects.filter(
    end_date__gte=form_start_date,
    start_date__lte=form_end_date
)

return Item.objects.exclude(
    booking__in=bookings
)

This then results in a query like:

SELECT item.*
FROM item
WHERE NOT (
    item.id IN (
        SELECT V1.item_id
        FROM booking V1
        WHERE (V1.id IN (
            SELECT U0.id FROM booking U0
            WHERE (U0.end_date >= 2019-01-01 AND U0.start_date <= 2019-02-02)
            AND V1.item_id IS NOT NULL
        )
    )
)

(here 2019-01-01 and 2019-02-02 are hypothetical start and end dates).

I think it is probably better to process the two dates through a Form however to do proper validation and cleaning.

For example if we populate an empty database with the data as provided in the question, we get:

>>> i1 = Item.objects.create(name='Item #01')
>>> i2 = Item.objects.create(name='Item #02')
>>> b1 = Booking.objects.create(item=i1, start_date=)
KeyboardInterrupt
>>> from datetime import date
>>> b1 = Booking.objects.create(item=i1, start_date=date(2019,1,1), end_date=date(2019, 1, 3))
>>> b2 = Booking.objects.create(item=i1, start_date=date(2019,1,11), end_date=date(2019, 1, 18))
>>> bookings = Booking.objects.filter(
...     end_date__gte=date(2019, 1, 6),
...     start_date__lte=date(2019, 1, 8)
... )
>>> Item.objects.exclude(
...     booking__in=bookings
... )
<QuerySet [<Item: Item object (2)>, <Item: Item object (3)>]>
>>> b3 = Booking.objects.create(item=i2, start_date=date(2019,1,2), end_date=date(2019, 1, 6))
>>> bookings = Booking.objects.filter(
...     end_date__gte=date(2019, 1, 6),
...     start_date__lte=date(2019, 1, 8)
... )
>>> Item.objects.exclude(
...     booking__in=bookings
... )
<QuerySet [<Item: Item object (2)>]>

So first I constructed two items, and made two bookings on the first item. If we then make a query, we see that both items pop up. If we then add an extra booking for Item #02, then if we perform the query again, we see that only the first item (I first made an item for test purposes that was then removed) is showing up, since for the given range, the second item is no longer available: it has been booked by booking b3.