场景:显示用户可以应用的所有凭证。
我有两个表Voucher(包含凭证的所有信息)和VoucherCustomer(列出用户使用过的凭证的数量)
可以在应用程序上显示给用户的验证凭证应该是
-
在可使用时间内
-
一天内不要超过使用次数
-
不要超过每个用户的使用次数
-
请勿超过使用次数
- 必须活跃
这是我的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)
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
我期望的查询:
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
下面是我在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()
显然，它不起作用。
我得到这个错误:
File "/code/app_test/views.py", line 467
).order_by('created_at').values()
^
SyntaxError: positional argument follows keyword argument
无论如何，因为我只是一个初学者，如果有我可以改进我的代码的部分，请随时告诉我。
------------更新----------------当前代码不工作，因为我收到了这个错误
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
我试着把VoucherCustomer的模型改成这个，但还是不行。
class VoucherCustomer(models.Model):
voucher_code = models.ManyToOneRel(field = "voucher_code", field_name = "voucher_code", to = "")
......................
class Meta:
managed = False
db_table = 'voucher_customer'
代码不是综合正确的，您不能在方法签名中使用<=，即在filter()中使用，还需要在通过函数传递关键字参数之前传递参数，即some_function(a, b, x=y)。
但是，您可以使用Coalesce用凭证查询集注释值，然后再次运行filter，如下所示:
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()