pythondjangodjango-querysetdjango-q

Django filter query using Q


Can anyone help me. Im trying to use a django filter with Q. This is my function

def get_first_time_customer_ids(year: int, month: int) -> QuerySet:
    return Customer.objects.filter(
        Q(bookings__status=Booking.STATUS.completed, bookings__pickup_time__year=year, bookings__pickup_time__month=month) &
        ~Q(bookings__status=Booking.STATUS.completed, bookings__pickup_time__lt=date(year, month, 1))
        ).distinct().values_list('id', flat=True)

What im trying to achieve is to yield all the customer id that have the first time booking for any given year and month. But its failing on my test case. My test case :

def test_get_first_time_customer_ids(self) -> None:
        customer_1 = Customer.objects.create(name="Customer 1")
        customer_2 = Customer.objects.create(name="Customer 2")
        Booking.objects.bulk_create([
            Booking(number="A", customer=customer_1, price=100_000, status=Booking.STATUS.completed,
                    pickup_time=dt(2023, 2, 4, 12), route_id=1, vehicle_category_id=1),
            Booking(number="B", customer=customer_1, price=100_000, status=Booking.STATUS.completed,
                    pickup_time=dt(2023, 1, 5, 12), route_id=1, vehicle_category_id=1),
            Booking(number="E", customer=customer_2, price=100_000, status=Booking.STATUS.completed,
                    pickup_time=dt(2023, 2, 10, 12), route_id=1, vehicle_category_id=1)
        ])        
        ids = get_first_time_customer_ids(2023, 2)
      
        self.assertTrue(customer_2.id in ids)
        self.assertFalse(customer_1.id in ids)

Its failing in the last line. The customer id for customer_1 included in query, it shouldnt have. Any help is appreciated


Solution

  • def get_first_time_customer_ids(year: int, month: int) -> QuerySet:
        qs1 = Customer.objects.filter(
            bookings_status=Booking.STATUS.completed,
            bookings__pickup_time__year=year,
            bookings__pickup_time__month=month,
        ).distinct().values("id")
    
        qs2 = Customer.objects.filter(
            bookings_status=Booking.STATUS.completed,
            bookings__pickup_time__lt=date(year, month, 1),
        ).distinct().values("id")
    
        return qs1.exclude(id__in=qs2).values_list('id', flat=True)
    

    Try this code. I add distinct operations for both of querysets (qs1, qs2) But in original code, distinct operation is only at the end.