tortoise / tortoise/tortoise-orm
ValuesQuery execution fails when using select_related()
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 5.6k
- Forks
- 516
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 9
Description
Describe the bug
When trying to join multiple tables and select only specific fields then the ValuesQuery fails.
To Reproduce
Version:
from tortoise import __version__
print(f"Tortoise ORM version: {__version__}")
Tortoise ORM version: 0.20.0
The models:
class A(Model):
id = fields.IntField(pk=True)
name = fields.CharField(max_length=10)
rel = fields.ReverseRelation["models.B"]
class B(Model):
a = fields.OneToOneField('models.A', related_name='rel')
id = fields.IntField(pk=True)
score = fields.IntField()
Create test data:
a = await A.create(name="test")
b = await B.create(score=1, a=a)
The query:
query = A.all().select_related("B").values('name', 'rel__score')
query.sql() generates the proper SQL query:
raw_sql = query.sql()
print(raw_sql)
SELECT "a"."name" "name","b"."score" "rel__score" FROM "a" LEFT OUTER JOIN "b" ON "a"."id"="b"."a_id"
Executing the raw SQL works:
conn = Tortoise.get_connection("default")
result = await conn.execute_query(raw_sql)
print(result)
(1, [<Record name='test' rel__score=1>])
Using _execute() works as well:
result = await query._execute()
print(result)
[{'name': 'test', 'rel__score': 1}]
Executing the query fails:
result = await query
print(result)
asyncpg.exceptions.UndefinedColumnError: column "score" does not exist
Expected behavior
await query should not fail as the query is correct.
Additional context
I played a bit with the ValuesQuery class in queryset.py and added some prints to see where things go wrong.
Added some prints the __await__ :
def __await__(self) -> Generator[Any, None, Union[List[Dict[str, Any]], Dict[str, Any]]]:
if self._db is None:
self._db = self._choose_db() # type: ignore
print("==============================")
print(self.query)
self._make_query()
print(self.query)
print("==============================")
return self._execute().__await__() # pylint: disable=E1101
The result is:
==============================
SELECT "a"."name" "name","b"."score" "rel__score" FROM "a" LEFT OUTER JOIN "b" ON "a"."id"="b"."a_id"
SELECT "name" "name","score" "rel__score" FROM "a"
==============================
From the looks of it self._make_query() leaves out the table joins for some reason in ValuesQuery.
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
Start in queryset.py with ValuesQuery.await and _make_query(), then run the reproduction against the PostgreSQL backend. Compare the query before and after _make_query(); done means awaiting the ValuesQuery preserves the joins and returns the same values as _execute() and the raw SQL.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, python
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Clearly specified
- Newbie friendliness
- 48/100