Selecting from multiple sources

If we wished to list all the tweets in the database, along with the username oftheir author, you might try writing this:

>>> for tweet in Tweet.select():
...     print(tweet.user.username, '->', tweet.content)
...
huey -> meow
huey -> hiss
huey -> purr
mickey -> woof
mickey -> whine

There is a big problem with the above loop: it executes an additional query forevery tweet to look up the tweet.user foreign-key. For our small table theperformance penalty isn’t obvious, but we would find the delays grew as thenumber of rows increased.

If you’re familiar with SQL, you might remember that it’s possible to SELECTfrom multiple tables, allowing us to get the tweet content and the usernamein a single query:

SELECT tweet.content, user.username
FROM tweet
INNER JOIN user ON tweet.user_id = user.id;

Peewee makes this quite easy. In fact, we only need to modify our query alittle bit. We tell Peewee we wish to select Tweet.content as well asthe User.username field, then we include a join from tweet to user.To make it a bit more obvious that it’s doing the correct thing, we can askPeewee to return the rows as dictionaries.

>>> for row in Tweet.select(Tweet.content, User.username).join(User).dicts():
...     print(row)
...
{'content': 'meow', 'username': 'huey'}
{'content': 'hiss', 'username': 'huey'}
{'content': 'purr', 'username': 'huey'}
{'content': 'woof', 'username': 'mickey'}
{'content': 'whine', 'username': 'mickey'}

Now we’ll leave off the call to “.dicts()” and return the rows as Tweetobjects. Notice that Peewee assigns the username value totweet.user.username – NOT tweet.username! Because there is aforeign-key from tweet to user, and we have selected fields from both models,Peewee will reconstruct the model-graph for us:

>>> for tweet in Tweet.select(Tweet.content, User.username).join(User):
...     print(tweet.user.username, '->', tweet.content)
...
huey -> meow
huey -> hiss
huey -> purr
mickey -> woof
mickey -> whine

If we wish to, we can control where Peewee puts the joined User instance inthe above query, by specifying an attr in the join() method:

>>> query = Tweet.select(Tweet.content, User.username).join(User, attr='author')
>>> for tweet in query:
...     print(tweet.author.username, '->', tweet.content)
...
huey -> meow
huey -> hiss
huey -> purr
mickey -> woof
mickey -> whine

Conversely, if we simply wish all attributes we select to be attributes ofthe Tweet instance, we can add a call to objects() atthe end of our query (similar to how we called dicts()):

>>> for tweet in query.objects():
...     print(tweet.username, '->', tweet.content)
...
huey -> meow
(etc)

More complex example

As a more complex example, in this query, we will write a single query thatselects all the favorites, along with the user who created the favorite, thetweet that was favorited, and that tweet’s author.

In SQL we would write:

SELECT owner.username, tweet.content, author.username AS author
FROM favorite
INNER JOIN user AS owner ON (favorite.user_id = owner.id)
INNER JOIN tweet ON (favorite.tweet_id = tweet.id)
INNER JOIN user AS author ON (tweet.user_id = author.id);

Note that we are selecting from the user table twice - once in the context ofthe user who created the favorite, and again as the author of the tweet.

With Peewee, we use Model.alias() to alias a model class so it can bereferenced twice in a single query:

Owner = User.alias()
query = (Favorite
         .select(Favorite, Tweet.content, User.username, Owner.username)
         .join(Owner)  # Join favorite -> user (owner of favorite).
         .switch(Favorite)
         .join(Tweet)  # Join favorite -> tweet
         .join(User))   # Join tweet -> user

We can iterate over the results and access the joined values in the followingway. Note how Peewee has resolved the fields from the various models weselected and reconstructed the model graph:

>>> for fav in query:
...     print(fav.user.username, 'liked', fav.tweet.content, 'by', fav.tweet.user.username)
...
huey liked whine by mickey
mickey liked purr by huey
zaizee liked meow by huey
zaizee liked purr by huey