Joining on arbitrary fields

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

In the following example, there is no explicit foreign-key between User andActivityLog, but there is an implied relationship between theActivityLog.object_id field and User.id. Rather than joining on a specificField, 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 joinedinstance 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 thejoined ActivityLog without incurring an additional query:

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