Multiple foreign-keys to the same Model

When there are multiple foreign keys to the same model, it is good practice toexplicitly specify which field you are joining on.

Referring back to the example app’s models,consider the Relationship model, which is used to denote when one userfollows another. Here is the model definition:

class Relationship(BaseModel):
    from_user = ForeignKeyField(User, backref='relationships')
    to_user = ForeignKeyField(User, backref='related_to')
 
    class Meta:
        indexes = (
            # Specify a unique multi-column index on from/to-user.
            (('from_user', 'to_user'), True),
        )

Since there are two foreign keys to User, we should always specify whichfield we are using in a join.

For example, to determine which users I am following, I would write:

(User
 .select()
 .join(Relationship, on=Relationship.to_user)
 .where(Relationship.from_user == charlie))

On the other hand, if I wanted to determine which users are following me, Iwould instead join on the from_user column and filter on the relationship’sto_user:

(User
 .select()
 .join(Relationship, on=Relationship.from_user)
 .where(Relationship.to_user == charlie))