QuerySet build sql error When multi ForeignKeyField ref to one model

[zengsun]

Describe the bug

class User(Model):
    username = CharField(max_length=20, unique=True, description='用户名')
    nickname = CharField(description='昵称', max_length=20)
    email = CharField(description='EMail', max_length=50, null=True)
    mobile_tel = CharField(description='手机', max_length=15, null=True)

class OperationLog(Model):
    added_quantity = IntField(description='新增数量')
    changed_quantity = IntField(description='更新数量')
    operated_at = DateTimeField(auto_now_add=True, description='更新时间')
    operated_by: ForeignKeyRelation[User] = ForeignKeyField('models.User',
                                                            description='更新人')

class History(Model):
    num = CharField(max_length=14, description='设备编码')
    log: ForeignKeyRelation[OperationLog] = ForeignKeyField(
        'models.OperationLog',
        related_name='created_histories',
        description='操作记录')
   changed_log: ForeignKeyRelation[OperationLog] = ForeignKeyField(
        'models.OperationLog',
        related_name='changed_histories',
        description='变更操作')

qset = History.all().values('id', 'num', 'log__operated_at', 'log__operated_by__nickname', 'changed_log__operated_at', 'changed_log__operated_by__nickname')

query = await qset

qset.query.get_sql()

SELECT "history"."id" "id","history"."num" "num","operationlog"."operated_at" "logoperated_at","user"."nickname" "log__operated_bynickname","operationlog"."operated_at" "changed_logoperated_at","user"."nickname" "changed_logoperated_by__nickname" FROM "history" LEFT OUTER JOIN "operationlog" ON "operationlog"."id"="history"."log_id" LEFT OUTER JOIN "user" ON "user"."id"="operationlog"."operated_by_id"

The generated SQL statement is obviously wrong !

Correct SQL statement:

SELECT
  "a"."id" "id",
  "a"."num" "num",
  "b"."operated_at" "log__operated_at",
  "c"."nickname" "log__operated_by__nickname",
  "d"."operated_at" "changed_log__operated_at",
  "e"."nickname" "changed_log__operated_by__nickname" 
FROM "history" as a
LEFT OUTER JOIN "operationlog" as b ON b."id"=a."log_id" 
LEFT OUTER JOIN "user" as c ON "c"."id"="b"."operated_by_id"
LEFT OUTER JOIN "operationlog" as d ON d."id"=a."changed_log_id" 
LEFT OUTER JOIN "user" as e ON "e"."id"="d"."operated_by_id";

[grigi]

Thank you for reporting this, this is definitely not the desired behaviour. :pensive:

@abondar Could you give me a hand with this? We need to likely alias each join to avoid namespace collisions?

[zengsun]

I think this is a bug that needs to be fixed urgently ...

[grigi]

Yes, I agree.

The issue is that Tortoise doesn't alias tables, and since Q was originally written by @abondar I am hoping he can help me implement it. We need aliasing support to support this use case.

Two different FK's to the same model was never tested, so consider it an oversight.

[grigi]

Epic fail :rofl:

class DoubleFK(Model):
    name = fields.CharField(max_length=50)
    left = fields.ForeignKeyField("models.DoubleFK", null=True, related_name="left_rel")
    right = fields.ForeignKeyField("models.DoubleFK", null=True, related_name="right_rel")

one = await DoubleFK.create(name="one")
two = await DoubleFK.create(name="two")
middle = await DoubleFK.create(name="middle", left=one, right=two)

# Query to get middle:
DoubleFK.filter(left__name="one", right__name="two").values('name')

It generates:

SELECT "doublefk2"."name" "name"
FROM "doublefk"
LEFT OUTER JOIN "doublefk" "doublefk2" ON "doublefk2"."id"="doublefk2"."left_id"
WHERE "doublefk2"."name"='one' AND "doublefk2"."name"='two'

It should generate:

SELECT "doublefk"."name" "name"
FROM "doublefk"
LEFT OUTER JOIN "doublefk" "doublefk2" ON "doublefk"."id"="doublefk2"."left_id"
LEFT OUTER JOIN "doublefk" "doublefk3" ON "doublefk"."id"="doublefk3"."right_id"
WHERE "doublefk2"."name"='one' AND "doublefk3"."name"='two'

[grigi]

Hi, please see if #277 resolves it for you? an easy way to test would be pip install https://github.com/tortoise/tortoise-orm/archive/aliased_tables.zip

[zengsun]

It's OK ! The problem has been solved.

[grigi]

Released as v0.15.9