[Question] join Not unique table/alias: 't2'

[fruitoiz]

Hello, how can I make this request?

SELECT chat, u1.user_name as user_name_1, u2.user_name as user_name_2
FROM mard
JOIN user u1 ON user_id_1 = u1.user_id
JOIN user u2 ON user_id_2 = u2.user_id;

SELECT `t1`.`id`, `t1`.`chat_id`, `t1`.`user_id_1`, `t1`.`user_id_2`, `t2`.`user_name` AS `user_name1`, `t2`.`user_name` AS `user_name2` 
FROM `mard` AS `t1` 
INNER JOIN `user` AS `t2` ON (`t2`.`user_id` = `t1`.`user_id_1`) 
INNER JOIN `user` AS `t2` ON (`t2`.`user_id` = `t1`.`user_id_2`)

tried this code:

Mard.select(Mard, User.user_name.alias("user_name1"), User.user_name.alias("user_name2"))
.join(User, on=(User.user_id == Mard.user_id_1))
.switch(User)
.join(User, on=(User.user_id == Mard.user_id_2))

but it didn't work out: peewee.OperationalError: (1066, "Not unique table/alias: 't2'")

[coleifer]

You'll want to use http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.alias

So:

User2 = User.alias('u2')

q = (Mard
     .select(Mard, User.user_name, User2.user_name)
     .join_from(Mard, User, on=(Mard.user_id_1 == User.user_id))
     .join_from(Mard, User2, on=(Mard.user_id_2 == User2.user_id)))
for mard in q:
    print(mard.user_id_1.user_name, mard.user_id_2.user_name)

Something like that.

[fruitoiz]

    print(mard.user_id_1.user_name, mard.user_id_2.user_name)
          ^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'NoneType' object has no attribute 'user_name'

Interesting 🧐

[coleifer]

You didn't provide model definitions so I'm not sure how you've named your foreign keys. At any rate, use User.alias() to reference the same table twice.

[fruitoiz]

class Mard(BaseModel):
    chat_id = BigIntegerField()
    user_id_1 = BigIntegerField()
    user_id_2 =  BigIntegerField()

class User(BaseModel):
    user_id = BigIntegerField()
    user_name = TextField()

by foreign keys do you mean ForeignKeyField?

[coleifer]

Yes I assumed you represented your relations as foreign keys, and used ForeignKeyField.

[fruitoiz]

Can ForeignKeyField store an arbitrary number, not just an id?

[coleifer]

No, they would need to refer to a row in the related table.

[fruitoiz]

I decided to go a different route, but I came across a bug or something was wrong with me. I tried to use the sql code provided by the library, it works fine.

db = SqliteDatabase(':memory:')

class BaseModel(Model):
    class Meta:
        database = db

class User(BaseModel):
    user_id = BigIntegerField()
    user_name = TextField()

class MarD(BaseModel):
    chat_id = BigIntegerField()

    user_1 = BigIntegerField()
    user_2 =  BigIntegerField()

db.create_tables([
    MarD,
    User,
])

User.create(user_id=322, user_name="урааа")
User.create(user_id=3221, user_name="урааа2")
MarD.create(user_1=322, user_2=3221, chat_id=-100)

User1 = User.alias('u1')
User2 = User.alias('u2')
ff = (MarD.select(MarD.chat_id, User1.user_name.alias("user_name_1"), User2.user_name.alias("user_name_2"))
        .join_from(MarD, User1, on=(MarD.user_1 == User1.user_id))
        .join_from(MarD, User2, on=(MarD.user_2 == User2.user_id))
        .where(MarD.id==1)).execute()
for i in ff:
    print(i.user.user_name_1)

error:

    print(i.user.user_name_1)
          ^^^^^^^^^^^^^^^^^^
AttributeError: 'User' object has no attribute 'user_name_1'. Did you mean: 'user_name_2'?

sorry for the poor quality of the code, it was done in a hurry

[coleifer]

Since those are not actually foreign keys then they will not be handled quite as nicely, but you can try this:

ff = (MarD.select(MarD.chat_id, User1.user_name, User2.user_name)
        .join_from(MarD, User1, on=(MarD.user_1 == User1.user_id), attr='user_1')
        .join_from(MarD, User2, on=(MarD.user_2 == User2.user_id), attr='user_2')
        .where(MarD.id==1)).execute()
for i in ff:
    print(i.user_1.user_name)
    print(i.user_2.user_name)

If you just want everything on the MarD object, you can instead:

ff = (MarD.select(MarD.chat_id, User1.user_name.alias('u1'), User2.user_name.alias('u2'))
        .join_from(MarD, User1, on=(MarD.user_1 == User1.user_id))
        .join_from(MarD, User2, on=(MarD.user_2 == User2.user_id))
        .where(MarD.id==1)
        .objects())  # .objects() tells Peewee to just put everything on the MarD instance.
for i in ff:
    print(i.u1)
    print(i.u2)

[fruitoiz]

Thank you!