Joining multiple tables

Let’s take another look at joins by querying the list of users and getting thecount of how many tweet’s they’ve authored that were favorited. This willrequire us to join twice: from user to tweet, and from tweet to favorite. We’lladd the additional requirement that users should be included who have notcreated any tweets, as well as users whose tweets have not been favorited. Thequery, expressed in SQL, would be:

SELECT user.username, COUNT(favorite.id)
FROM user
LEFT OUTER JOIN tweet ON tweet.user_id = user.id
LEFT OUTER JOIN favorite ON favorite.tweet_id = tweet.id
GROUP BY user.username

Note

In the above query both joins are LEFT OUTER, since a user may not have anytweets or, if they have tweets, none of them may have been favorited.

Peewee has a concept of a join context, meaning that whenever we call thejoin() method, we are implicitly joining on thepreviously-joined model (or if this is the first call, the model we areselecting from). Since we are joining straight through, from user to tweet,then from tweet to favorite, we can simply write:

query = (User
         .select(User.username, fn.COUNT(Favorite.id).alias('count'))
         .join(Tweet, JOIN.LEFT_OUTER)  # Joins user -> tweet.
         .join(Favorite, JOIN.LEFT_OUTER)  # Joins tweet -> favorite.
         .group_by(User.username))

Iterating over the results:

>>> for user in query:
...     print(user.username, user.count)
...
huey 3
mickey 1
zaizee 0

For a more complicated example involving multiple joins and switching joincontexts, let’s find all the tweets by Huey and the number of times they’vebeen favorited. To do this we’ll need to perform two joins and we’ll also usean aggregate function to calculate the favorite count.

Here is how we would write this query in SQL:

SELECT tweet.content, COUNT(favorite.id)
FROM tweet
INNER JOIN user ON tweet.user_id = user.id
LEFT OUTER JOIN favorite ON favorite.tweet_id = tweet.id
WHERE user.username = 'huey'
GROUP BY tweet.content;

Note

We use a LEFT OUTER join from tweet to favorite since a tweet may not haveany favorites, yet we still wish to display it’s content (along with acount of zero) in the result set.

With Peewee, the resulting Python code looks very similar to what we wouldwrite in SQL:

query = (Tweet
         .select(Tweet.content, fn.COUNT(Favorite.id).alias('count'))
         .join(User)  # Join from tweet -> user.
         .switch(Tweet)  # Move "join context" back to tweet.
         .join(Favorite, JOIN.LEFT_OUTER)  # Join from tweet -> favorite.
         .where(User.username == 'huey')
         .group_by(Tweet.content))

Note the call to switch() - that instructs Peewee to setthe join context back to Tweet. If we had omitted the explicit call toswitch, Peewee would have used User (the last model we joined) as the joincontext and constructed the join from User to Favorite using theFavorite.user foreign-key, which would have given us incorrect results.

If we wanted to omit the join-context switching we could instead use thejoin_from() method. The following query is equivalent tothe previous one:

query = (Tweet
         .select(Tweet.content, fn.COUNT(Favorite.id).alias('count'))
         .join_from(Tweet, User)  # Join tweet -> user.
         .join_from(Tweet, Favorite, JOIN.LEFT_OUTER)  # Join tweet -> favorite.
         .where(User.username == 'huey')
         .group_by(Tweet.content))

We can iterate over the results of the above query to print the tweet’s contentand the favorite count:

>>> for tweet in query:
...     print('%s favorited %d times' % (tweet.content, tweet.count))
...
meow favorited 1 times
hiss favorited 0 times
purr favorited 2 times