[FIXED] annotate() + distinct(fields) is not implemented. Python Django Error

Issue

When I try to use annotate with distinct, the following error occurs:

NotImplementedError at /guest/book/1/checkavailability/
annotate() + distinct(fields) is not implemented.
Request Method: GET
Request URL:    http://localhost:8000/guest/book/1/checkavailability/?start_date=2023-11-19&end_date=2023-11-28
Django Version: 4.2.7
Exception Type: NotImplementedError
Exception Value:    
annotate() + distinct(fields) is not implemented.
Exception Location: /home/abk/Desktop/project-qb/django/lib/python3.11/site-packages/django/db/models/sql/compiler.py, line 872, in as_sql
Raised during:  booking.views.views.RoomsAvailableBetweenSpecificDatesView
Python Executable:  /home/abk/Desktop/project-qb/django/bin/python
Python Version: 3.11.4

Here is my query

booked_rooms = RoomBooking.objects.filter(
            (Q(booking__start_date__range=(lookup_start_date, lookup_end_date))
             | Q(booking__end_date__range=(lookup_start_date, lookup_end_date)) | Q(booking__start_date__lt=lookup_start_date, booking__end_date__gt=lookup_end_date)),
            booking__hotel=self.hotel, booking__guest_status__in=[
                settings.NOT_CHECKED, settings.CHECKED_IN],
        ).select_related('booking', 'room').exclude(room__is_deleted=True).distinct('room__room_number').values(room_type=F('room__room_type')).annotate(count=Count('room__room_type')).order_by('room__room_type')

Here is my model

class RoomBooking(models.Model):
    booking = models.ForeignKey("Booking", on_delete=models.CASCADE)
    room = models.ForeignKey("Room", on_delete=models.CASCADE)


class Room(models.Model):
    room_number = models.CharField(max_length=25)
    hotel = models.ForeignKey("Hotel", on_delete=models.CASCADE)
    room_type = models.CharField(max_length=25)
    rate = models.IntegerField(validators=[MinValueValidator(0)])
    is_deleted = models.BooleanField(default=False)


class Booking(models.Model):
    NOT_CHECKED = 'not checked'
    CHECKED_IN = 'checked in'
    CHECKED_OUT = 'checkout out'
    CANCELLED = 'cancelled'
    GUEST_STATUS = [
        (NOT_CHECKED, "NOT_CHECKED"),
        (CHECKED_IN, "CHECKED_IN"),
        (CHECKED_OUT, 'CHECKED_OUT'),
        (CANCELLED, "CANCELLED")
    ]
    hotel = models.ForeignKey('Hotel', on_delete=models.CASCADE)
    guest = models.ForeignKey(
        "authentication.User", on_delete=models.CASCADE, null=True)
    total_rooms = models.IntegerField(
        default=1, validators=[MinValueValidator(1)])
    start_date = models.DateTimeField()
    end_date = models.DateTimeField()
    booked_date = models.DateTimeField(auto_now_add=True)
    guest_status = models.CharField(
        max_length=15, choices=GUEST_STATUS, default=NOT_CHECKED)

How could I make the same query without the issue?

After some googling I've found that if we try to call distinct on an annotated query, it sometimes works, sometimes get's ignore, and sometimes raises an error. But unable to solve my issue after playing with some query splitting too.

Solution

I was able to solve the issue by changing the query to the following.

RoomBooking.objects.filter(
            (Q(booking__start_date__range=(lookup_start_date, lookup_end_date))
             | Q(booking__end_date__range=(lookup_start_date, lookup_end_date)) | Q(booking__start_date__lt=lookup_start_date, booking__end_date__gt=lookup_end_date)),
            booking__hotel=self.hotel, booking__guest_status__in=[
                settings.NOT_CHECKED, settings.CHECKED_IN],
        ).select_related('booking', 'room').exclude(room__is_deleted=True).values(room_type=F('room__room_type')).annotate(count=Count('room__room_number', distinct=True)).order_by('room__room_type')

Hope it will be helpful for someone.

Answered By - Abk