Consider the following web2py model:
db.define_table('post',
db.Field('title', length=200, requires=IS_NOT_EMPTY()),
db.Field('author', db.auth_user, default=auth.user.id if auth.user else None),
db.Field('body', 'text'),
db.Field('published_at', 'datetime', default=request.now,writable=False),
db.Field('is_draft', 'boolean', default=True),
db.Field('is_deleted', 'boolean', default=False))
Normally a query looks like
db(db.post.is_draft==False).select()
db((db.post.is_draft==False)&(db.post.is_deleted==True)).select()
It is possible to name queries (also called "named scopes"):
published = db.post.is_draft==False
deleted = db.post.is_deleted==True
published_and_deleted = published & deleted
and use them in queries
rows = db(published).select()
rows = db(deleted).select()
rows = db(published & deleted).select()
rows = db(published_and_deleted).select()
rows = db(published & deleted)(db.post.published_at<request.now).select()
rows = db(published & deleted)(db.post.like('%test%')).select()
Named scopes can be complex and involve multiple tables via implicit joins. For example:
authors = db.auth_user.id==db.post.author
and now we can select the first name of all users who have an account (in auth_user), who are authors of a post, and their first name starts with 'A'):
rows = db(authors)(db.auth_user.first_name.like('A%')).select(db.auth_user.first_name)
Named Scopes in web2py have been available since version 1.0 in October 2007.