`select_related` doesn't work: raises AttributeError

[mahenzon]

Describe the bug If using select_related, it raises AttributeError

Seems like the error is related to one-to-many relation

To Reproduce short:

# query:
t: Tournament = await Tournament.filter(name=tournament_name).select_related("events").first()

# exception
  File "/opt/project/api/stuff.py", line 51, in run
    t: Tournament = await Tournament.filter(name=tournament_name).select_related("events").first()
    │                     │          │           └ 'tournament'
    │                     │          └ <classmethod object at 0x7f95e62e5df0>
    │                     └ <class '__main__.Tournament'>
    └ <Tournament: 1>

  File "/usr/local/lib/python3.8/site-packages/tortoise/queryset.py", line 797, in _execute
    instance_list = await self._db.executor_class(
                          │    └ <member '_db' of 'QuerySet' objects>
                          └ <tortoise.queryset.QuerySet object at 0x7f95e5349c70>
  File "/usr/local/lib/python3.8/site-packages/tortoise/backends/base/executor.py", line 149, in execute_select
    setattr(ins, model_name, obj)
            │    │           └ <Event: 1>
            │    └ 'events'
            └ <Tournament: 1>

AttributeError: can't set attribute

Expected behavior Don't raise, just load selected relations

Additional context

Full example:

from tortoise import Tortoise, fields, run_async
from tortoise.models import Model


class Tournament(Model):
    id = fields.IntField(pk=True)
    name = fields.TextField()

    events: fields.ReverseRelation["Event"]

    def __str__(self):
        return self.name


class Event(Model):
    id = fields.IntField(pk=True)
    name = fields.TextField()
    tournament: fields.ForeignKeyRelation[Tournament] = fields.ForeignKeyField(
        "models.Tournament",
        related_name="events",
    )

    def __str__(self):
        return self.name


async def run():
    await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]})
    await Tortoise.generate_schemas()

    tournament_name = "tournament"
    tournament = await Tournament.create(name=tournament_name)
    await Event.create(name="First", tournament=tournament)
    await Event.create(name="Second", tournament=tournament)

    t: Tournament = await Tournament.filter(name=tournament_name).first()
    print(t, t.events)
    # tournament <tortoise.fields.relational.ReverseRelation object at 0x7f7e4b693e50>

    t: Tournament = await Tournament.filter(name=tournament_name).prefetch_related("events").first()
    print(t, t.events, list(t.events))
    # tournament <tortoise.fields.relational.ReverseRelation object at 0x7f7e4b6b3760> [<Event: 1>, <Event: 2>]

    t: Tournament = await Tournament.filter(name=tournament_name).select_related("events").first()
    print(t, t.events, list(t.events))
    # Traceback (most recent call last):
    #   File "/opt/project/api/stuff.py", line 47, in <module>
    #     run_async(run())
    #   File "/usr/local/lib/python3.8/site-packages/tortoise/__init__.py", line 636, in run_async
    #     loop.run_until_complete(coro)
    #   File "/usr/local/lib/python3.8/asyncio/base_events.py", line 616, in run_until_complete
    #     return future.result()
    #   File "/opt/project/api/stuff.py", line 42, in run
    #     t: Tournament = await Tournament.filter(name=tournament_name).select_related("events").first()
    #   File "/usr/local/lib/python3.8/site-packages/tortoise/queryset.py", line 797, in _execute
    #     instance_list = await self._db.executor_class(
    #   File "/usr/local/lib/python3.8/site-packages/tortoise/backends/base/executor.py", line 149, in execute_select
    #     setattr(ins, model_name, obj)
    # AttributeError: can't set attribute

if __name__ == "__main__":
    run_async(run())

---

relates to #500 and #501

[long2ice]

select_related only work in ForeignKeyRelation, not work in ReverseRelation. And select_related generate sql join, which has different behavior with prefetch_related

[mahenzon]

Anyway there has to be the way to select related items in one query. It's strange that there's no option to do this join yet

[drjackild]

@mahenzon but FK relation is "One to Many". There is no way to write such a query even in raw SQL, to join all related items, which has a link to an object. Only some aggregations, like array_agg, but this is not a join at all, it's just a sub-query.

[mahenzon]

@DrJackilD I'm sorry, I don't understand your point. What's wrong with one-to-many relationship?

There're two ways to do it: 1 - in two queries. at first query main objects, then collect their ids and then fetch related objects 2 - in one query, using join.

anyways in both cases we'll have to create python objects and connect them together

Creating tables and a relation with foreign key:

CREATE TABLE authors (
	author_id serial PRIMARY KEY,
	username VARCHAR ( 50 ) UNIQUE NOT NULL
);

CREATE TABLE articles (
	article_id serial PRIMARY KEY,
	title VARCHAR ( 50 ) NOT NULL,
	body text NOT NULL,
	author_id INT NOT NULL,
    CONSTRAINT fk_author
      FOREIGN KEY(author_id)
	  REFERENCES authors(author_id)
);

INSERT INTO authors (username) VALUES ('john');
INSERT INTO authors (username) VALUES ('james');
INSERT INTO authors (username) VALUES ('sam');
INSERT INTO articles (title, body, author_id) VALUES ('python lesson', 'text body', 1);
INSERT INTO articles (title, body, author_id) VALUES ('tortoise lesson', 'another body', 1);
INSERT INTO articles (title, body, author_id) VALUES ('django lesson', 'some body text', 2);

Queries:

-- selecting author by article.author_id ( =1)
SELECT author_id, username
FROM authors
WHERE author_id = 1;

author_id	username
1	john

-- selecting all authored posts by author.author_id
SELECT article_id, title, body, author_id
FROM articles
WHERE articles.author_id = 1;

article_id	title	body	author_id
1	python lesson	text body	1
2	tortoise lesson	another body	1

SELECT au.author_id, au.username, ar.article_id, ar.title, ar.body
FROM authors au
LEFT OUTER JOIN articles ar on au.author_id = ar.author_id
WHERE username ILIKE 'j%';

author_id	username	article_id	title	body
1	john	1	python lesson	text body
1	john	2	tortoise lesson	another body
2	james	3	django lesson	some body text

-- selecting one selected author's articles
SELECT au.author_id, au.username, ar.article_id, ar.title, ar.body
FROM authors au
LEFT OUTER JOIN articles ar on au.author_id = ar.author_id
WHERE username = 'james';

author_id	username	article_id	title	body
2	james	3	django lesson	some body text

-- selecting some selected authors articles
SELECT au.author_id, au.username, ar.article_id, ar.title, ar.body
FROM authors au
LEFT OUTER JOIN articles ar on au.author_id = ar.author_id
WHERE username ILIKE '%a%';

author_id	username	article_id	title	body
2	james	3	django lesson	some body text
3	sam	NULL	NULL	NULL

-- all authors with their articles
SELECT au.author_id, au.username, ar.article_id, ar.title, ar.body
FROM authors au
LEFT OUTER JOIN articles ar on au.author_id = ar.author_id;

author_id	username	article_id	title	body
1	john	1	python lesson	text body
1	john	2	tortoise lesson	another body
2	james	3	django lesson	some body text
3	sam	NULL	NULL	NULL

[metakot]

No offence, but... In django orm it just works, but here is not. Also, AttributeError: can't set attribute is a very strange error. It can be more specific, like You cannot do what you're used to, just use "prefetch_related" instead =) Just kidding.

[Olegt0rr]

Any news?

[AndTheDaysGoBy]

Exact same issue. E.g.

from tortoise import fields
from tortoise.model import Model


class User(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=50, unique=True)
    roles = fields.ManyToManyField('models.Role')


class Role(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=30, unique=Trure)

Naturally, one might want to get the user with their roles populated as some iteratable:

user = await User.all().select_related('roles').get(name='some username')
for role in user.roles:
    print(role)

However, despite tortoise generating the appropriate SQL statement, the parsing of the response fails (with the above AttributeError that OP gets).

DEBUG:db_client:
SELECT `user`.`name`,`user`.`id`,`role`.`name` `role.name`,`role`.`id` `role.id` FROM `user` LEFT OUTER JOIN `user_role` ON `user`.`id`=`user_role`.`user_id` LEFT OUTER JOIN `role` ON `user_role`.`role_id`=`role`.`id` WHERE `user`.`name`='some username' LIMIT 2

# pretty print
SELECT user.name, user.id, role.name, role.name, role.id, role.id
FROM (
    (user LEFT OUTER JOIN user_role ON user.id = user_role.user_id)
    LEFT OUTER JOIN role ON user_role.role_id=role.id
)
WHERE user.name="some username"
LIMIT 2

return await User.all().select_related('roles').get(name=username)
File "venv/lib/python3.8/site-packages/tortoise/queryset.py", line 879, in _execute
instance_list = await self._db.executor_class(
File "venv/lib/python3.8/site-packages/tortoise/backends/base/executor.py", line 156, in execute_select
    setattr(ins, model_name, [obj])
AttributeError: can't set attribute

# where
# parent model = <class User>
# ins = User(name="some username")
# model_name = "roles"
# obj = Role(name="Admin")

In the first place, setting the attribute on a many-2-many wouldn't make sense since, in the case of multiple related rows, each iteration will overwrite the previous.

A quick aside, doubt it impacts anything, but I do notice that fields for the table that's joined against, "role" in this case, are duplicated in the select statement.

Edit: inb4 I'm misunderstanding select_related vs. prefetch_related.

[Rebbit13]

Describe the bug If using select_related, it raises AttributeError

Seems like the error is related to one-to-many relation

To Reproduce short:

# query:
t: Tournament = await Tournament.filter(name=tournament_name).select_related("events").first()

# exception
  File "/opt/project/api/stuff.py", line 51, in run
    t: Tournament = await Tournament.filter(name=tournament_name).select_related("events").first()
    │                     │          │           └ 'tournament'
    │                     │          └ <classmethod object at 0x7f95e62e5df0>
    │                     └ <class '__main__.Tournament'>
    └ <Tournament: 1>

  File "/usr/local/lib/python3.8/site-packages/tortoise/queryset.py", line 797, in _execute
    instance_list = await self._db.executor_class(
                          │    └ <member '_db' of 'QuerySet' objects>
                          └ <tortoise.queryset.QuerySet object at 0x7f95e5349c70>
  File "/usr/local/lib/python3.8/site-packages/tortoise/backends/base/executor.py", line 149, in execute_select
    setattr(ins, model_name, obj)
            │    │           └ <Event: 1>
            │    └ 'events'
            └ <Tournament: 1>

AttributeError: can't set attribute

Expected behavior Don't raise, just load selected relations

Additional context

Full example:

from tortoise import Tortoise, fields, run_async
from tortoise.models import Model


class Tournament(Model):
    id = fields.IntField(pk=True)
    name = fields.TextField()

    events: fields.ReverseRelation["Event"]

    def __str__(self):
        return self.name


class Event(Model):
    id = fields.IntField(pk=True)
    name = fields.TextField()
    tournament: fields.ForeignKeyRelation[Tournament] = fields.ForeignKeyField(
        "models.Tournament",
        related_name="events",
    )

    def __str__(self):
        return self.name


async def run():
    await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]})
    await Tortoise.generate_schemas()

    tournament_name = "tournament"
    tournament = await Tournament.create(name=tournament_name)
    await Event.create(name="First", tournament=tournament)
    await Event.create(name="Second", tournament=tournament)

    t: Tournament = await Tournament.filter(name=tournament_name).first()
    print(t, t.events)
    # tournament <tortoise.fields.relational.ReverseRelation object at 0x7f7e4b693e50>

    t: Tournament = await Tournament.filter(name=tournament_name).prefetch_related("events").first()
    print(t, t.events, list(t.events))
    # tournament <tortoise.fields.relational.ReverseRelation object at 0x7f7e4b6b3760> [<Event: 1>, <Event: 2>]

    t: Tournament = await Tournament.filter(name=tournament_name).select_related("events").first()
    print(t, t.events, list(t.events))
    # Traceback (most recent call last):
    #   File "/opt/project/api/stuff.py", line 47, in <module>
    #     run_async(run())
    #   File "/usr/local/lib/python3.8/site-packages/tortoise/__init__.py", line 636, in run_async
    #     loop.run_until_complete(coro)
    #   File "/usr/local/lib/python3.8/asyncio/base_events.py", line 616, in run_until_complete
    #     return future.result()
    #   File "/opt/project/api/stuff.py", line 42, in run
    #     t: Tournament = await Tournament.filter(name=tournament_name).select_related("events").first()
    #   File "/usr/local/lib/python3.8/site-packages/tortoise/queryset.py", line 797, in _execute
    #     instance_list = await self._db.executor_class(
    #   File "/usr/local/lib/python3.8/site-packages/tortoise/backends/base/executor.py", line 149, in execute_select
    #     setattr(ins, model_name, obj)
    # AttributeError: can't set attribute

if __name__ == "__main__":
    run_async(run())

relates to #500 and #501

Seems like this issue not actual any more. At list on version 0.19.2 it works well.

Console output:

tournament <tortoise.fields.relational.ReverseRelation object at 0x7f6ff27197f0>
tournament <tortoise.fields.relational.ReverseRelation object at 0x7f6ff27192b0> [<Event: 1>, <Event: 2>]
tournament First [('name', 'First'), ('id', 1), ('tournament_id', 1)]```