tortoise / tortoise/tortoise-orm
`Prefetch`+`Count` cannot fetch count correctly!
Open
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 5.6k
- Forks
- 516
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 9
Description
class TypeEnum(str, Enum):
on = "ON"
off = "OFF"
recover = "RECOVER"
class Record(models.Model):
interactive_network_id: int
id = fields.IntField(pk=True)
type = fields.CharEnumField(TypeEnum)
interactive_network = fields.ForeignKeyField(
"my_models.InteractiveNetwork",
related_name="records",
index=True,
)
class InteractiveNetwork(models.Model):
id = fields.IntField(pk=True)
records: fields.ReverseRelation["Record"]
-- interactive_network
id |
--+
1 |
-- record
id | type |
--+------------+
1 | ON |
--+------------+
2 | RECOVER |
--+------------+
3 | RECOVER |
I want to count the number of InteractiveNetwork records with type recover.
result_list = await InteractiveNetwork.all().prefetch_related(
Prefetch("records", queryset=Record.filter(type=TypeEnum.recover))
).annotate(my_count=Count("records__id")).group_by("id")
print(result_list[0].my_count ) # output: 3
print(len(result_list[0].records) ) # output: 1
The recover records cannot be correctly counted by grouping.
InteractiveNetwork.all().prefetch_related(
Prefetch("records", queryset=Record.filter(type=TypeEnum.recover))
).annotate(my_count=Count("records__id")).group_by("id").sql()
The result of '_make_query()' is as follows:
SELECT
`interactive_network`.`id`,
COUNT(`record`.`id`) `my_count`
FROM
`interactive_network`
LEFT OUTER JOIN `record` ON
`interactive_network`.`id` = `record`.`interactive_network_id`
GROUP BY
`interactive_network`.`id`,
But the sql I expect to get should look like this:
SELECT
`interactive_network`.`id`,
COUNT(`record`.`id`) `my_count`
FROM
`interactive_network`
LEFT OUTER JOIN
(
SELECT *
FROM `record`
WHERE `record`.`type` = "RECOVER"
) AS `record`
ON
`interactive_network`.`id` = `record`.`interactive_network_id`
GROUP BY
`interactive_network`.`id`;
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Reproduce the shown InteractiveNetwork and Record query, then inspect the SQL produced by _make_query() for the interaction between prefetch_related, Prefetch, Count, and group_by. Compare it with the filtered join SQL in the issue; done means the recover-only count matches the prefetched records.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100