tortoise / tortoise/tortoise-orm

`Prefetch`+`Count` cannot fetch count correctly!

Open
#1,218 3 comments 0 reactions 0 assignees View on GitHub

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

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.