Django - How to do complex query with left join and coalesce?

Question

Scenario: Showing all voucher that a user can apply.

I have 2 tables Voucher (with all information of a voucher) and VoucherCustomer (listing number of vouchers that a user has used)

A validation voucher that can show to user on the application should be

1. Within use-able duration

2. Do not exceed number of times used within a day

3. Do not exceed number of times used per user

4. Do not exceed number of times used for a voucher

5. Must active

Here is my model:

class Voucher(models.Model):
    code = models.ForeignKey('VoucherCustomer', related_name= 'voucher_code', on_delete = models.CASCADE)  (1)
    start_at = models.DateTimeField()                                                                      (2)
    end_at = models.DateTimeField()                                                                        (3)
    usage_limit_per_customer = models.BigIntegerField()                                                    (4)
    times_used = models.BigIntegerField()                                                                  (5)                                                                           
    usage_limit_daily = models.BigIntegerField()                                                           (6)
    times_used_daily = models.BigIntegerField()                                                            (7)
    is_global = models.BooleanField(blank=True, null=True)                                                 (8)
    is_active = models.BooleanField()                                                                      (9)

class VoucherCustomer(models.Model):
    voucher_code = models.CharField(max_length = 255, primary_key=True)                                    (1)
    customer_id = models.IntegerField()                                                                    (2)
    times_used = models.BigIntegerField(blank=True, null=True)                                             (3)
    created_at = models.DateTimeField(blank=True, null=True)                                               (4)
    updated_at = models.DateTimeField(blank=True, null=True)                                               (5)

Here is the sample data:

+++++++ Voucher ++++++++
 (1)             (2)                   (3)           (4)   (5)    (6)   (7)   (8)    (9)
TEST01 | 2020-11-30 17:00:00 | 2021-03-01 16:59:59 | 100 | 1124 | 5000 | 6 | true | true

+++++++ VoucherCustomer ++++++++
(1)     (2)    (3)            (4)                         (5)   
TEST01 10878    9   2020-12-03 02:17:32.012722  2020-12-08 10:32:03.877349
TEST01 12577    1   2020-12-02 07:17:34.005964  2020-12-02 07:17:34.005964
TEST01 8324    18   2020-12-02 07:49:37.385682  2021-02-01 14:35:38.096381
TEST01 7638     2   2020-12-02 08:17:46.532566  2020-12-02 08:17:46.532566
TEST01 3589     1   2020-12-02 14:57:01.356616  2020-12-02 14:57:01.356616

My expected query:

SELECT v.*
FROM leadtime.voucher v
LEFT JOIN leadtime.voucher_customer vc ON v.code = vc.voucher_code AND vc.customer_id in ({input_customer_id})
WHERE 1=1
AND v.start_at <= CURRENT_TIMESTAMP
AND v.end_at >= CURRENT_TIMESTAMP
AND v.is_active = TRUE
AND v.times_used < v.usage_limit
AND v.times_used_daily < v.usage_limit_daily
AND (v.customer_id = {input_customer_id} OR v.is_global)
AND COALESCE(vc.times_used,0) < usage_limit_per_customer
ORDER BY created_at

Here is my code on Django:

from django.db.models import Q, F, Value
from django.db.models.functions import Coalesce
now = datetime.now()


customer_input = customer_id = request.GET.get('customer_id')
query = Voucher.objects.filter(
        start_at__lte = now,
        end_at__gte = now,
        is_active = True,
        times_used__lt = F('usage_limit'),
        times_used_daily__lt = F('usage_limit_daily'),
        Q(customer_id = customer_id_input ) | Q(is_global = True),
        VoucherCustomer__customer_id = customer_id_input ,
        Coalesce(VoucherCustomer__times_used, Value(0)) <= F('usage_limit_per_customer')
        ).order_by('created_at').values()

Obviously, it does not work.

I got this error:

   File "/code/app_test/views.py", line 467
     ).order_by('created_at').values()
     ^
 SyntaxError: positional argument follows keyword argument

Anyway, since I am just a beginner, if there are parts that I could improve in my code please feel free to tell me.

------------ Updated ---------------- The current code is not working as I received this err

Cannot resolve keyword 'VoucherCustomer' into field. Choices are: airport, arrival_airport, code, code_id, content, created_at, customer_id, delivery_type, departure_airport, description, discount_amount, discount_type, end_at, from_time, id, is_active, is_global, max_discount_amount, start_at, times_used, times_used_daily, tittle, to_time, updated_at, usage_limit, usage_limit_daily, usage_limit_per_customer

I tried to change the model of VoucherCustomer into this one but still not working.

class VoucherCustomer(models.Model):
    voucher_code = models.ManyToOneRel(field = "voucher_code", field_name = "voucher_code", to = "")
    ......................
        class Meta:
            managed = False
            db_table = 'voucher_customer'

Answer 1

The code is not syntetically correct, you can't use <= inside a method signature, ie use that in filter(), also you need to pass the arguments before passing the keyword arguments through the function, ie some_function(a, b, x=y).

But, you can use Coalesce to annotate the value with Voucher queryset then run the filter again, like this:

query = Voucher.objects.filter(
        Q(customer_id = customer_id_input ) | Q(is_global = True),
        start_at__lte = now,
        end_at__gte = now,
        is_active = True,
        times_used__lt = F('usage_limit'),
        times_used_daily__lt = F('usage_limit_daily'),
        code__customer_id = customer_id_input
    ).annotate(
        usage_so_far=Coalesce('code__times_used', Value(0))
    ).filter(
        usage_so_far__gte=F('usage_limit_per_customer')
    ).order_by('created_at').values()