Tortoise-orm: `select_related` doesn't work: raises AttributeError

Created on 26 Sep 2020  ·  6Comments  ·  Source: tortoise/tortoise-orm

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

picture mahenzon
😕1

Most helpful comment

@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 |

picture mahenzon on 16 Oct 2020
👍3

All 6 comments

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

long2ice picture long2ice on 26 Sep 2020

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

mahenzon picture mahenzon on 26 Sep 2020

@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.

DrJackilD picture DrJackilD on 15 Oct 2020
👎1

@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 |

mahenzon picture mahenzon on 16 Oct 2020
👍3

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.

metakot picture metakot on 30 Oct 2020
👍1

Any news?

Olegt0rr picture Olegt0rr on 18 Dec 2020
😄1
Was this page helpful?
0 / 5 - 0 ratings

Related issues

grigi picture grigi  ·  6Comments
mheppner picture mheppner  ·  4Comments
usernein picture usernein  ·  4Comments
mengyyy picture mengyyy  ·  6Comments
lntuition picture lntuition  ·  4Comments