SQL Functions

SQL functions, like COUNT() or SUM(), can be expressed using thefn() helper:

# Get all users and the number of tweets they've authored. Sort the
# results from most tweets -> fewest tweets.
query = (User
         .select(User, fn.COUNT(Tweet.id).alias('tweet_count'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User)
         .order_by(fn.COUNT(Tweet.id).desc()))
for user in query:
    print('%s -- %s tweets' % (user.username, user.tweet_count))

The fn helper exposes any SQL function as if it were a method. Theparameters can be fields, values, subqueries, or even nested functions.

Nesting function calls

Suppose you need to want to get a list of all users whose username begins witha. There are a couple ways to do this, but one method might be to use someSQL functions like LOWER and SUBSTR. To use arbitrary SQL functions, usethe special fn() object to construct queries:

# Select the user's id, username and the first letter of their username, lower-cased
first_letter = fn.LOWER(fn.SUBSTR(User.username, 1, 1))
query = User.select(User, first_letter.alias('first_letter'))
 
# Alternatively we could select only users whose username begins with 'a'
a_users = User.select().where(first_letter == 'a')
 
>>> for user in a_users:
...    print(user.username)