Joining on arbitrary fields

If a foreign key does not exist between two tables you can still perform a join, but you must manually specify the join predicate.

In the following example, there is no explicit foreign-key between User and ActivityLog, but there is an implied relationship between the ActivityLog.object_id field and User.id. Rather than joining on a specific Field, we will join using an Expression.

user_log = (User
            .select(User, ActivityLog)
            .join(ActivityLog, on=(User.id == ActivityLog.object_id), attr='log')
            .where(
                (ActivityLog.activity_type == 'user_activity') &
                (User.username == 'charlie')))
for user in user_log:
    print(user.username, user.log.description)
#### Print something like ####
charlie logged in
charlie posted a tweet
charlie retweeted
charlie posted a tweet
charlie logged out

Note

Recall that we can control the attribute Peewee will assign the joined instance to by specifying the attr parameter in the join() method. In the previous example, we used the following join:

join(ActivityLog, on=(User.id == ActivityLog.object_id), attr='log')

Then when iterating over the query, we were able to directly access the joined ActivityLog without incurring an additional query:

for user in user_log:
    print(user.username, user.log.description)