编写并验证复杂数据库查询
根据数据模型编写复杂查询,检查关联与聚合逻辑,并通过样例数据和查询次数测试验证结果。
完整提示词
<context>
Most wrong Django queries look right and return plausible numbers. Two `annotate(Count(...))` calls across different multi-valued relations join both tables and multiply the counts; the fix is `distinct=True` for counts or a `Subquery` per aggregate for sums. The order of `values()` and `annotate()` decides the grouping: `values("field").annotate(...)` groups by that field, while `annotate` before `values` does not, and a model `Meta.ordering` can silently add columns to the GROUP BY on older versions. Filters on multi-valued relations behave differently in one `filter()` call than in two chained calls. `Subquery` must return one column and one row, so it needs `.values("col")[:1]`, and aggregates inside it need an explicit grouping with `.values("fk").annotate(...)`. `Sum` over no rows returns `None`, not 0 (`Coalesce` fixes that, with a matching `output_field` for decimals). Python-side loops over querysets hide N+1 queries that `select_related`, `prefetch_related` or `Prefetch` would remove. `print(qs.query)` shows approximate SQL, and `connection.queries` or `assertNumQueries` show what actually ran.
</context>
<task>
Write the Django ORM query for: {{question}}
{{#models}}Models: {{models}}{{/models}}
1. Read the models, their relations, managers and `Meta.ordering`, and any existing queries or custom QuerySet methods in the same area. Reuse a manager method if one already does part of the job.
2. Restate the result in one line: one row per what, which columns, which filters, which order. If the question is ambiguous (for example "orders" includes cancelled ones or not), state the assumption.
3. Write the query with the right tools: `F` for column references, `Q` for OR and NOT, `annotate` and `aggregate`, `Subquery` with `OuterRef` for per-row aggregates across a second relation, `Exists` for existence filters, `Coalesce` for empty aggregates, `Case` and `When` for conditional counts, and `select_related` or `prefetch_related` for what the caller will touch.
4. Print the SQL (`str(qs.query)`) and read it: check the joins, GROUP BY columns, and that no aggregate is multiplied by a second join.
5. Write a test with a few hand-built rows that would expose the classic mistakes (two related sets of different sizes, a customer with no orders, a NULL value) and `assertNumQueries` for the expected count.
6. Run the test and report the result. Mention any index the filter or ordering would need, without adding a migration unless asked.
</task>
<constraints>
- Do not fall back to raw SQL unless the ORM cannot express the query; if you do, use parameters, never string formatting.
- Do not loop in Python over a queryset to compute what the database can aggregate.
- Do not change models or add migrations to make the query easier without asking.
{{> guardrails/scope-discipline}}
{{> guardrails/verify-before-done}}
</constraints>
<output_format>
## Query
The queryset in a fenced block, with where it lives.
## Generated SQL
The printed SQL, with one line on the joins and grouping.
## Traps avoided
Bullets naming each trap that applied here and how the query avoids it.
## Check
The test and its real result, including the query count.
## Open questions
Numbered assumptions to confirm, or "None".
</output_format>可替换内容
- {{question}}
- {{#models}}
- {{models}}
- {{/models}}
- {{> guardrails/scope-discipline}}
- {{> guardrails/verify-before-done}}
- [:1]
分类与标签
编程开发、数据库查询、聚合统计、查询性能、自动化测试