How to control field name for related model fetching in Peewee?

I have two models: User and Message. Each Message has two references to User (as sender and as receiver). Also I have defined an other_user hybrid method for Message which returns "user other than specific one" - see below:

from peewee import *
from playhouse.hybrid import hybrid_method
from playhouse.shortcuts import case

class User(Model):
    name = CharField()

class Message(Model):
    sender = ForeignKeyField(User, related_name='messages_sent')
    receiver = ForeignKeyField(User, related_name='messages_received')
    text = TextField()

    @hybrid_method
    def other_user(self, user):
        if user == self.sender:
            return self.receiver
        elif user == self.receiver:
            return self.sender
        else:
            raise ValueError

    @other_user.expression
    def other_user(cls, user):
        return case(user.id, (
            (cls.sender, cls.receiver),
            (cls.receiver, cls.sender)))

Now I want to make a composite query which will retrieve all messages for current user and also retrieve information about "other" user than current. Here is how I do it:

current_user = request.user  # don't matter how I retrieve it
query = (Message.select(Message, User)
         .where(
             (Message.sender == current_user) |
             (Message.receiver == current_user))
         .join(User, on=(User.id == Message.other_user(current_user))))

This query works well - i.e. it retrieves the exact information I need. But here is the problem: "other user" information is always saved as sender field. If I use this with models which have no direct ForeignKey reference then peewee creates a new field (in this case it would be named user) for additional requested model. But if there is at least one ForeignKey relationship from primary model to secondary requested model then it uses first such relationship.

Is it possible to somehow override this behaviour? I tried Model.alias() method, but it (unlike Node.alias) doesn't allow to specify name.

Solution

I'm not completely sure what you want, so I'll provide a snippet that will likely work for your specific scenario, and will hopefully also allow you to learn how to do what you want, if this isn't it:

SenderUser = User.alias()
ReceiverUser = User.alias()
OtherUser = User.alias()

query = (Message.select(Message, SenderUser, ReceiverUser)
        .where(
            (Message.sender == current_user) |
            (Message.receiver == current_user))

        .join(SenderUser, on = (Message.sender == SenderUser.id).alias('sender'))

        .switch(Message)
        .join(ReceiverUser, on = (Message.receiver == ReceiverUser.id).alias('receiver'))

        .switch(Message)
        .join(OtherUser, on = (Message.other_user(current_user) == OtherUser.id).alias('other_user'))

Notes:

You don't really need to create all those aliases (SenderUser/ReceiverUser/OtherUser), just two, and use User for the other. I just find that the query becomes more readable like this.

When you define an alias in the on clause, you basically tell peewee in which variable to store the joined table. I'm sending them directly to the already existing properties (sender/receiver). Also, I'm creating an extra property in the model with the value of the other user, which you can access as usual with self.other_user.

That switch method switches the current context to Message, so you can join a new table to Message instead of the SenderUser/ReceiverUser contexts where you end up after the two first joins.

If for some reason you're joining something that might be undefined (which doesn't seem to be the case here as both users are likely mandatory), you would probably want to add that you want a left outer join, like this:

.join(ReceiverUser, JOIN.LEFT_OUTER, on = (Message.receiver == ReceiverUser.id).alias('receiver'))

Don't forget to from peewee import JOIN

Something else I just noticed, is that you likely want to change that other_user method you have to compare ids instead of the model variables. If self.sender is not filled when you access it, peewee will trigger a database select to get it, so your other_user method possibly triggers 2 select queries. I would do it like:

@hybrid_method
def other_user_id(self, user):
    if user.id == self.sender_id:
        return self.receiver_id
    elif user.id == self.receiver_id:
        return self.sender_id
    else:
        raise ValueError

You can see that I use sender_id instead of sender.id. That uses the ids for each foreign key that are already set in the message model. If you did self.receiver.id you would likely trigger that select anyway, to then access the id property (I'm not 100% sure here though).