There is an entry for table "table", but it cannot be referenced from this part of the query

[pavel-vorobyev]

Describe the bug I have two models. They are related via OneToOneField.

class User(Model):
    class Meta:
        table = "users"

    id = fields.UUIDField(primary_key=True)
    internal_id = fields.IntField(unique=True)
    info: fields.ReverseRelation["UserInfo"]

class UserInfo(Model):
    class Meta:
        table = "user_infos"

    id = fields.UUIDField(primary_key=True)
    rank = fields.IntField()
    user = fields.OneToOneField("app.User", related_name="info")

When I try to update UserInfo by user's internal_id I got an error:

await UserInfo.filter(user__internal_id=SOME_ID).update(rank=5)

tortoise.exceptions.OperationalError: invalid reference to FROM-clause entry for table "user_infos"
HINT:  There is an entry for table "user_infos", but it cannot be referenced from this part of the query.

SQL Query:

UPDATE "user_infos" SET "rank"=$1 FROM "user_infos" "user_infos_" LEFT OUTER JOIN "users" "user_infos__user" ON "user_infos__user"."id"="user_infos"."user_id" WHERE "user_infos__user"."internal_id"=893706004;

If I call just filter without update it works fine. It returns UserInfo object:

await UserInfo.filter(user__internal_id=internal_id)

Expected behavior Update request executes successfully.

[abondar]

Yeah, there is some problem with query generation, this alias doesn't seem right, I'll try to look into it later

[abondar]

For now you can do the same with subquery

    user = await User.create(internal_id=2)
    await UserInfo.create(rank=1, user=user)
    await UserInfo.filter(
        user_id__in=Subquery(User.filter(internal_id=2).values("id"))
    ).update(rank=5)