Performing simple joins

As an exercise in learning how to perform joins with Peewee, let’s write a query to print out all the tweets by “huey”. To do this we’ll select from the Tweet model and join on the User model, so we can then filter on the User.username field:

>>> query = Tweet.select().join(User).where(User.username == 'huey')
>>> for tweet in query:
...     print(tweet.content)
...
meow
hiss
purr

Note

We did not have to explicitly specify the join predicate (the “ON” clause), because Peewee inferred from the models that when we joined from Tweet to User, we were joining on the Tweet.user foreign-key.

The following code is equivalent, but more explicit:

query = (Tweet
         .select()
         .join(User, on=(Tweet.user == User.id))
         .where(User.username == 'huey'))

If we already had a reference to the User object for “huey”, we could use the User.tweets back-reference to list all of huey’s tweets:

>>> huey = User.get(User.username == 'huey')
>>> for tweet in huey.tweets:
...     print(tweet.content)
...
meow
hiss
purr

Taking a closer look at huey.tweets, we can see that it is just a simple pre-filtered SELECT query:

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