tortoise / tortoise/tortoise-orm

from_tortoise_orm generated redundant queries

Open
#2,115 2 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

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

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

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.