tortoise / tortoise/tortoise-orm
from_tortoise_orm generated redundant queries
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 5.6k
- Forks
- 516
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 9
Description
The code is as follows:
query = User.get_or_none(id=id).prefetch_related('roles')
result = await User_Pydantic.from_tortoise_orm(await query)
** User and Role is M2M relationship.
From the logs, You can see that it executed 3 queries, with the third one being obviously redundant:
# 1st
SELECT `id`,`name`,`phone` FROM `user` WHERE `id`=%s ASC LIMIT %s : [1, 2]
# 2nd
SELECT `sq0`.`_backward_relation_key` `_backward_relation_key`,`role`.`name` `name`,`role`.`code` `code`,`role`.`id` `id`
FROM `role` JOIN (SELECT `user_id` `_backward_relation_key`,`role_id` `_forward_relation_key` FROM `role_user`
WHERE `user_id` IN (%s)) `sq0` ON `sq0`.`_forward_relation_key`=`role`.`id` : [1]
# 3rd
SELECT `sq0`.`_backward_relation_key` `_backward_relation_key`,`role`.`name` `name`,`role`.`code` `code`,`role`.`id` `id`
FROM `role` JOIN (SELECT `user_id` `_backward_relation_key`,`role_id` `_forward_relation_key` FROM `role_user`
WHERE `user_id` IN (%s)) `sq0` ON `sq0`.`_forward_relation_key`=`role`.`id` : [1]
If I return the queryset directly instead of executing from_tortoise_orm, the SQL will be executed twice (properly).
Did I write something incorrectly there?
Of course, the above issue is not a bug, just an efficiency problem.
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
The report identifies prefetch_related and User_Pydantic.from_tortoise_orm as the relevant entry points; start by reproducing the example and tracing how the prefetched roles are handled during serialization. Done means the same relation is not queried redundantly while from_tortoise_orm still returns the expected related data.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python
- Domain
- database
- Issue type
- Refactor
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 35/100