SQL Functions

SQL functions, like COUNT() or SUM(), can be expressed using the fn() 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. The parameters 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 with a. There are a couple ways to do this, but one method might be to use some SQL functions like LOWER and SUBSTR. To use arbitrary SQL functions, use the 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)