Assessing the same object through the span relation in qs.excude ()

Question

Consider a hotel with Room objects and Reservation Objects for these rooms. I want to find which rooms are available in a certain period or (especially in the example below) with which date and time.

The reservation can be "deleted", which is obtained by the settings in the "Live" field. Therefore, they are not actually deleted, but simply inactive, and this should remain that way.

>>> indate = "20141225"
>>> Room.objects.exclude(
(models.Q(reservation__live=True , reservation__date_out__gt=indate) 
| models.Q(reservation__date_out__isnull=True, reservation__live=True))
)

Reporting problems . The above code has an unfortunate side effect: when reserving with live=True, which is outside the period, and when during the period for another reservation live=Falsein the same room, this room will be excluded. This should not be so: since the reservation during the period that I request is not set in live=True, it should not be taken into account.

It seems that my request above does not consider the same disclaimer when comparing (live = True and date_out__gt = indate) via the number reservation relation.

Question : is there a way, within exclude (), to provide the same redundancy in comparison?

. Q (~Models.Q) . , ( ) . qs.filter(reservation__live=True). .

: . Edit2: , , exclude() @knbk.

class AvailableRoomManager(models.Manager):

    def available_with_Q(self, indate, outdate):
        qs = super(AvailableRoomManager, self).get_queryset()

        if indate and outdate:
            qs = qs.exclude(models.Q(reservation__date_out__gt=indate, reservation__date_in__lt=outdate, reservation__live=True)
                            | models.Q(reservation__date_out__isnull=True, reservation__date_in__isnull=False, reservation__date_in__lt=outdate, reservation__live=True))

        elif indate and not outdate:
            qs = qs.exclude((models.Q(reservation__date_out__gt=indate, reservation__date_in__isnull=False, reservation__live=True)
                            | models.Q(reservation__date_out__isnull=True, reservation__date_in__isnull=False, reservation__live=True)))

        return qs

    def available_with_chained_excludes(self, indate, outdate):
        qs = super(AvailableRoomManager, self).get_queryset()

        if indate and outdate:
            qs = qs.exclude(reservation__date_out__gt=indate, reservation__date_in__lt=outdate, reservation__live=True) \
                   .exclude(reservation__date_out__isnull=True, reservation__date_in__isnull=False, reservation__date_in__lt=outdate, reservation__live=True)

        elif indate and not outdate:
            qs = qs.exclude(reservation__date_out__gt=indate, reservation__date_in__isnull=False, reservation__live=True) \
                   .exclude(reservation__date_out__isnull=True, reservation__date_in__isnull=False, reservation__live=True)

        return qs


class Room(models.Model):
    name = models.CharField(max_length=30, unique=True)

    objects = models.Manager()
    available_rooms = AvailableRoomManager()

    def __str__(self):
        return self.name


class Reservation(models.Model): 
    date_in = models.DateField()
    date_out = models.DateField(blank=True, null=True)
    room = models.ForeignKey(Room)
    live = LiveField()  # See django-livefield; to do deletion. Basically adds a field "live" to the model.

    objects = LiveManager()
    all_objects = LiveManager(include_soft_deleted=True)

exclude() , (live = True) , , , (live!= True).

, , :

# Let make two rooms, R001 and R002
>>> room1 = Room.objects.get_or_create(name="R001")[0]
>>> room2 = Room.objects.get_or_create(name="R002")[0]

# First reservation, with no date_out, is created but then "deleted" by setting field 'live' to False
>>> res1 = Reservation.objects.get_or_create(date_in="2014-12-01", date_out=None, room=room1)[0]
>>> res1.live = False
>>> res1.save()

# Second reservation in same room is created with date_out set to Dec 15th
>>> res2 = Reservation.objects.get_or_create(date_in="2014-12-01", date_out="2014-12-15", room=room1)[0]

# Here I'd expect to have R001 listed as well... this is not the case
>>> Room.available_rooms.available_with_Q("2014-12-16", "")
[<Room: R002>]
>>> Room.available_rooms.available_with_chained_excludes("2014-12-16", "")
[<Room: R002>]

# As a test, when changing "deleted" res1 Room to room2, the manager does return R001
>>> res1.room = room2
>>> res1.save()
>>> Room.available_rooms.available_with_Q("2014-12-16", "")
[<Room: R001>, <Room: R002>]
>>> Room.available_rooms.available_with_chained_excludes("2014-12-16", "")
[<Room: R001>, <Room: R002>]

Answer 1

github, , . , , filter INNER JOIN, exclude - , ( ) .

, :

elif indate and not outdate:
    ress = Reservation.objects.filter(Q(live=True, date_in__isnull=False), Q(date_out__gt=indate) | Q(date_out__isnull=True))
    rooms = Room.objects.exclude(reservation__in=ress)
etc...

Btw, - filter exclude, , , Django :

Room.objects.exclude(<some_filter>)
Room.objects.filter(~Q(<some_filter>))

Answer 2

, , , :

: R_IN User_IN R_out

: R_IN User_IN ( , )

: User_IN R_IN User_OUT

Room.objects.exclude(
    models.Q(reservation__live=False, reservation__date_in__lte=indate, reservation__date_out__gte=indate)
  |
    models.Q(reservation__live=False, reservation__date_in__lte=indate, reservation__date_out__isnull=True)
  |
    models.Q(reservation__live=False, reservation__date_in__gte=indate, reservation__date_in__lte=outdate)
)

, , , , , , -