Reference¶
All the following functions are defined in the functions module.
Criteria¶
field¶
Use field()
when a column is needed in a criteria.
field('users.id').eq(100) # "users".id = ?
field('users.birthday').lt('2000-01-01') # "users"."birthday" > ?
field('users.last_login').between(yesterday, today) # "users"."last_login" BETWEEN ? AND ?
field('users.role').not_in('admin', 'moderator') # "users"."role" NOT IN (?, ?)
field('countries.id').in_(select) # "countries"."id" IN (?)
field('total').gt(9000) # "total" > ?
field('salary').lte(3000) # "salary" <= ?
field('deleted_at').is_null() # "deleted_at" IS NULL
field('parent_id').is_not_null() # "parent_id" IS NOT NULL
search¶
Use search()
for LIKE.
search('username').contains('admin') # "username" LIKE '%admin%'
search('first_name').begins('john') # "firstname" LIKE 'john%'
search('last_name').not_ends('rump') # "last_name" NOT LIKE '%rump'
on¶
on()
can be used in conjunction with join.
on('countries.id', 'users.country_id') # "countries"."id" = "users"."country_id"
group¶
Use group()
to combine criteria
group(
field('username').eq('tom') \
.or_(field('first_name').eq('Tom'))
).and_(
field('is_active').eq(1)
)
# ("username" = ? OR "first_name" = ?) AND "is_active" = ?
Expressions¶
express¶
express()
creates an expression. All expressions are written with the print format.
Any {} will be replaced with anything that implement the StatementInterface (like queries and other expressions).
express('{} <= NOW()', identify('execute_at')) # "execute_at" <= NOW()
express('{} + 1', identify('number_of_views')) # "number_of_views" + 1
Aliases¶
alias¶
alias()
creates an alias for a column or an expression.
alias('users.id', 'uid') # "users"."id" AS "uid"
Functions¶
func¶
Use func()
for functions.
func('COUNT', 'user.id') # COUNT("users"."id")
func('CONCAT', 'first_name', 'last_name') # CONCAT("first_name", "last_name")
By default functions assume identifiers as parameters, use param for scalar values:
func('POINT', param(1), param(2)) # POINT(? , ?)
Identifiers¶
identifier¶
identify()
is used to create an identifier.
identify('users.username') # "users"."username"
identify('country') # "country"
identifier_all¶
identifier_all()
creates a tuple of identifiers.
identify_all('id', 'username') # ("id", "username")
Lists¶
listing¶
listing()
is used to create a list of parameters or identifiers.
listing((1, 1, 2, 3, 5)) # ?, ?, ?, ?, ?
listing(identify_all('id', 'username', 'email')) # "id", "username", "email"