Peewee select with circular dependency

Question

I have two models Room and User. Every user is assigned to exactly one room and one of them is the owner.

DeferredUser = DeferredRelation()

class Room(Model):
    owner = ForeignKeyField(DeferredUser)

class User(Model):
    sessid = CharField()
    room = ForeignKeyField(Room, related_name='users')

DeferredUser.set_model(User)

Now, having sessid of the owner I'd like to select his room and all assigned users. But when I do:

(Room.select(Room, User)
     .join(User, on=Room.owner)
     .where(User.sessid==sessid)
     .switch(Room)
     .join(User, on=User.room))

It evaluates to:

SELECT "t1".*, "t2".* # skipped column names
FROM "room" AS t1
INNER JOIN "user" AS t2 ON ("t1"."owner_id" = "t2"."id")
INNER JOIN "user" AS t2 ON ("t1"."id" = "t2"."room_id")
WHERE ("t2"."sessid" = ?) [<sessid>]

and throws peewee.OperationalError: ambiguous column name: t2.id as t2 is defined twice.
What I actually need to do is:

room = (Room.select(Room)
            .join(User.sessid=sessid)
            .get())
users = room.users.execute()

But this is N+1 query and I'd like to resolve it in a single query like:

SELECT t1.*, t3.* FROM room AS t1
INNER JOIN user AS t2 ON t1.owner_id = t2.id
INNER JOIN user as t3 ON t3.room_id = t1.id
WHERE t2.sessid = ?;

Is there a peewee way of doing this or I need to enter this SQL query by hand?

Answer 1

You need to use a model alias when you are using the same table in two different contexts. So:

Owner = User.alias()  # Create a model alias.

(Room.select(Room, Owner, User)
 .join(Owner, on=(Room.owner == Owner.id))
 .where(Owner.sessid == sessid)
 .switch(Room)
 .join(User, on=User.room))

http://docs.peewee-orm.com/en/latest/peewee/api.html?#Model.alias