Foreign Keys and Joins

Foreign keys are created using a special field class ForeignKeyField. Each foreign key also creates a back-reference on the related model using the specified backref.

Note

In SQLite, foreign keys are not enabled by default. Most things, including the Peewee foreign-key API, will work fine, but ON DELETE behaviour will be ignored, even if you explicitly specify on_delete to your ForeignKeyField. In conjunction with the default PrimaryKeyField behaviour (where deleted record IDs can be reused), this can lead to surprising (and almost certainly unwanted) behaviour where if you delete a record in table A referenced by a foreign key in table B, and then create a new, unrelated, record in table A, the new record will end up mis-attached to the undeleted record in table B. To avoid the mis-attachment, you can use AutoIncrementField, but it may be better overall to ensure that foreign keys are enabled with pragmas=(('foreign_keys', 'on'),) when you instantiate SqliteDatabase.

Traversing foreign keys

Referring back to the User and Tweet models, note that there is a ForeignKeyField from Tweet to User. The foreign key can be traversed, allowing you access to the associated user instance:

>>> tweet.user.username
'charlie'

Note

Unless the User model was explicitly selected when retrieving the Tweet, an additional query will be required to load the User data. To learn how to avoid the extra query, see the N+1 query documentation.

The reverse is also true, and we can iterate over the tweets associated with a given User instance:

>>> for tweet in user.tweets:
...     print(tweet.message)
...
http://www.youtube.com/watch?v=xdhLQCYQ-nQ

Under the hood, the tweets attribute is just a Select with the WHERE clause pre-populated to point to the given User instance:

>>> user.tweets
<peewee.ModelSelect at 0x7f73db3bafd0>
>>> user.tweets.sql()
('SELECT "t1"."id", "t1"."user_id", "t1"."content", "t1"."timestamp" FROM "tweet" AS "t1" WHERE ("t1"."user_id" = ?)',
 [1])

Joining tables

Use the join() method to JOIN additional tables. When a foreign key exists between the source model and the join model, you do not need to specify any additional parameters:

>>> my_tweets = Tweet.select().join(User).where(User.username == 'charlie')

By default peewee will use an INNER join, but you can use LEFT OUTER, RIGHT OUTER, FULL, or CROSS joins as well:

users = (User
         .select(User, fn.Count(Tweet.id).alias('num_tweets'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User)
         .order_by(fn.Count(Tweet.id).desc()))
for user in users:
    print(user.username, 'has created', user.num_tweets, 'tweet(s).')

Selecting from multiple models

SQL makes it easy to select columns from multiple tables and return it all at once. Peewee makes this possible, too, but since Peewee models form a graph (via foreign-keys), the selected data is returned as a graph of model instances. To see what I mean, consider this query:

SELECT tweet.content, tweet.timestamp, user.username
FROM tweet
INNER JOIN user ON tweet.user_id = user.id
ORDER BY tweet.timestamp DESC;
-- Returns rows like
-- "Meow I'm a tweet" | 2017-01-17 13:37:00 | huey
-- "Woof woof" | 2017-01-17 11:59:00 | mickey
-- "Purr" | 2017-01-17 10:00:00 | huey

With Peewee we would write this query:

query = (Tweet
         .select(Tweet.content, Tweet.timestamp, User.username)
         .join(User)
         .order_by(Tweet.timestamp.desc()))

The question is: where is the “username” attribute to be found? The answer is that Peewee, because there is a foreign-key relationship between Tweet and User, will return each row as a Tweet model with the associated User model, which has it’s username attribute set:

for tweet in query:
    print(tweet.content, tweet.timestamp, tweet.user.username)

When doing complicated joins, joins where no foreign-key exists (for example joining on a subquery), etc., it is necessary to tell Peewee where to place the joined attributes. This is done by putting an alias on the join predicate expression.

For example, let’s say that in the above query we want to put the joined user data in the Tweet.foo attribute:

query = (Tweet
         .select(Tweet.content, Tweet.timestamp, User.username)
         .join(User, on=(Tweet.user == User.id).alias('foo'))
         .order_by(Tweet.timestamp.desc()))
for tweet in query:
    # Joined user data is stored in "tweet.foo":
    print(tweet.content, tweet.timestamp, tweet.foo.username)

For queries with complex joins and selections from several models, constructing this graph can be expensive. If you wish, instead, to have all columns as attributes on a single model, you can use objects() method:

for tweet in query.objects():
    # Now "username" is on the Tweet model itself:
    print(tweet.content, tweet.timestamp, tweet.username)

For additional performance gains, consider using dicts(), tuples() or namedtuples() when iterating large and/or complex result-sets.

Multiple Foreign Keys to the Same Model

When there are multiple foreign keys to the same model, it is good practice to explicitly 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 user follows 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 which field 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, I would instead join on the from_user column and filter on the relationship’s to_user:

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

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).alias('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

By specifying an alias on the join condition, you can control the attribute peewee will assign the joined instance to. In the previous example, we used the following join:

(User.id == ActivityLog.object_id).alias('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)

Joining on Multiple Tables

When calling join(), peewee will use the last joined table as the source table. For example:

User.select().join(Tweet).join(Comment)

This query will result in a join from User to Tweet, and another join from Tweet to Comment.

If you would like to join the same table twice, use the switch() method:

# Join the Artist table on both `Album` and `Genre`.
Artist.select().join(Album).switch(Artist).join(Genre)

Alternatively, you can use join_from():

query = (Artist
         .select()
         .join(Album)
         .join_from(Artist, Genre))