SELECT

Use select() of QueryFactory to create a SELECT statement. The return value is an instance of SelectQuery.

factory = QueryFactory(CommonEngine())
query = factory \
    .select() \
    .from_('users') \
    .limit(100) \
    .compile()

print(query.sql)  # SELECT * FROM "users" LIMIT 100
print(query.params)  # ()

Specific columns can be selected:

query = factory \
    .select('id', 'username') \
    .from_('users') \
    .compile()

print(query.sql)  # SELECT "id", "username" FROM "users"
print(query.params)  # ()

additional columns can be added:

query = factory \
    .select('id', 'username') \
    .add_columns('password') \
    .from_('users') \
    .compile()

print(query.sql)  # SELECT "id", "username", "password" FROM "users"
print(query.params)  # ()

As well as additional tables:

query = factory \
    .select('users.username', 'groups.name') \
    .from_('users') \
    .add_from('groups') \
    .compile()

print(query.sql)  # SELECT "users"."username", "groups"."name" FROM "users", "groups"
print(query.params)  # ()

WHERE

Apply criteria to a WHERE condition:

query = factory \
    .select() \
    .from_('countries') \
    .where(field('language')->eq('EN')) \
    .compile()

print(query.sql)  # SELECT * FROM "countries" WHERE "language" = ?
print(query.params)  # ('EN', )

Additional criteria can be added using and_where and or_where

query = factory \
    .select() \
    .from_('users') \
    .where(field('id')->gt(1)) \
    .or_where(field('login_at')->is_null()) \
    .or_where(field('is_inactive')->eq(1)) \
    .compile()

print(query.sql)  # SELECT * FROM "users" WHERE "id" > ? OR "login_at" IS NULL OR "is_inactive" > ?
print(query.params)  # (1, 1)

JOIN

Joins are added in a similar way:

query = factory \
    .select('u.id', 'c.name') \
    .from_(alias('users', 'u')) \
    .join(alias('countries', 'c'), on('u.country_id', 'c.id')) \
    .compile()

print(query.sql)  # SELECT * FROM "users" AS "u" JOIN "countries" AS "c" ON "u"."country_id" = "c"."id"
print(query.params)  # ()

The join type can be specified as third parameter of join or one of these helpers can be used for common types:

• left_join

• right_join

• inner_join

• full_join

ORDER BY

Ordering can be applied:

factory = QueryFactory(CommonEngine())
query = factory \
    .select() \
    .from_('users') \
    .order_by('username', 'asc') \
    .compile()

print(query.sql)  # SELECT * FROM "users" ORDER BY "username" DESC
print(query.params)  # ()

ordering can be reset by omitting the order column:

query.order_by()

LIMIT and OFFSET

Limits and offsets can be applied:

factory = QueryFactory(CommonEngine())
query = factory \
    .select() \
    .from_('posts') \
    .offset(10)
    .limit(10)
    .compile()

print(query.sql)  # SELECT * FROM "posts" OFFSET 10 LIMIT 10
print(query.params)  # ()

Note

When using the SQL Server engine an offset must be defined for the limit to be applied! Use offset(0) when no offset is required.

GROUP BY

Grouping can be applied:

factory = QueryFactory(CommonEngine())
query = factory \
    .select(
        alias(func('COUNT', 'id'), 'total')
    ) \
    .from_('employees') \
    .group_by('department') \
    .compile()

print(query.sql)  # SELECT COUNT("id") AS "total" FROM "employees" GROUP BY "department"
print(query.param)  # ()

And also the having clause can be applied:

factory = QueryFactory(CommonEngine())
salary_sum = func('SUM', 'salary')
query = factory \
    .select(
        'department',
        alias(salary_sum, 'total')
    ) \
    .from_('employees') \
    .group_by('department') \
    .having(field(salary_sum).gt(5000))

    print(query.sql)  # SELECT "department", SUM("salary") AS "total" FROM "employees" GROUP BY "department" HAVING SUM("salary") > ?
    print(query.params)  # (5000, )

CTE

From version 1.1.0, CTE can be used. Create the CTE as a normal select query and use with_() of SelectQuery to use it with another query.

factory = QueryFactory(CommonEngine())
cte = factory.select("id").from_("users").order_by("users.id").limit(10)
select = factory.select() \
    .with_("limit_users", cte) \
    .from_("users") \
    .left_join("limit_users", on("limit_users.id", "users.id"))