gpt4 book ai didi

mysql - 注释 Django 1.8 中复杂子查询的结果到 QuerySet

转载 作者:行者123 更新时间:2023-12-01 00:41:16 25 4
gpt4 key购买 nike

我必须遵循数据库架构:

main_account- usernameinvoice- amount- currency: 'EUR' or 'USD' or 'GBP'- username (main_account has many invoices)

and my goal is to list the "top payers" in dollars. Thus, I have to calculate a "total_paid" sum for each user from the invoice table (and take currency conversions into account). I also wish to filter by this "total_paid".

My current SQL looks like:

SELECT main_account.username, total_paid
FROM main_account JOIN
(
SELECT username,
(SELECT SUM(amount) FROM invoice WHERE main_account.username = invoice.username AND invoice.currency = 'EUR') AS total_eur,
(SELECT SUM(amount) FROM invoice WHERE main_account.username = invoice.username AND invoice.currency = 'USD') AS total_usd,
(SELECT SUM(amount) FROM invoice WHERE main_account.username = invoice.username AND invoice.currency = 'GBP') AS total_gbp,
(SELECT (IFNULL(total_usd,0) + 1.12 * IFNULL(total_eur,0) + 1.41 * IFNULL(total_gbp,0))) AS total_paid
FROM main_account
) as tbl
ON main_account.username = tbl.username
WHERE total_paid >= 2000
ORDER BY total_paid

我想知道如何使用 Django 的 ORM 完成此任务。

看来解决方案是这样的:

MainAccount.objects
.annotate(total_paid=???)
.filter(total_paid__gte=2000)
.order_by('total_paid')

一些注意事项:

  • MainAccount.extra(...).filter(...).order_by(...) 将不起作用。无法过滤在 extra 中创建的值。

  • 我已经尝试过 MainAccount.annotate(total_paid=RawSQL(...)),它运行良好,但在添加 .filter() 时遇到了一个奇怪的错误。出于某种原因,过滤器调用使用 SQL 参数改变 RawSQL 对象,然后抛出 "not all arguments converted during string formatting" 错误。

最佳答案

正如 BogdiG 所指出的,Django 1.8 条件表达式是解决方案。

Python/Django:

MainAccount.objects.annotate(
total_paid = Sum(
Case(
When(invoices__currency='EUR', then=F('invoices__amount') * 1.12),
When(invoices__currency='USD', then=F('invoices__amount')),
When(invoices__currency='GBP', then=F('invoices__amount') * 1.41)
)
)
).filter(total_paid__gte=2000).order_by('total_paid')

生成的 SQL 类似于:

SELECT  main_account.username, 
SUM(
CASE
WHEN invoice.currency = 'EUR' THEN (invoice.amount * 1.12)
WHEN invoice.currency = 'USD' THEN invoice.amount
WHEN invoice.currency = 'GBP' THEN (invoice.amount * 1.41)
ELSE NULL
END
) AS total_paid
FROM main_account
INNER JOIN invoice ON ( main_account.username = invoice.username )
GROUP BY main_account.username
HAVING total_paid >= '2000'
ORDER BY total_paid;

关于mysql - 注释 Django 1.8 中复杂子查询的结果到 QuerySet,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/31859867/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com