A simple select will include all columns:
$query = $factory
->select()
->from('users')
->limit(100)
->compile();
$query->sql(); // SELECT * FROM "users" LIMIT 100
$query->params(); // []
Specific columns can be selected:
$query = $factory
->select('id', 'username')
->from('users')
->compile();
$query->sql(); // SELECT "id", "username" FROM "users"
$query->params(); // []
Additional columns can be added:
$query = $factory
->select('id', 'username')
->addColumns('password')
->from('users')
->compile();
$query->sql(); // SELECT "id", "username", "password" FROM "users"
$query->params(); // []
As well as additional tables:
$query = $factory
->select('users.username', 'groups.name')
->from('users')
->addFrom('groups')
->compile();
$query->sql(); // SELECT "users"."username", "groups"."name" FROM "users", "groups"
$query->params(); // []
WHERE
Criteria can be applied to the WHERE
condition:
$query = $factory
->select()
->from('countries')
->where(field('language')->eq('EN'))
->compile();
$query->sql(); // SELECT * FROM "countries" WHERE "language" = ?
$query->params(); // ['EN']
Additional criteria can be added using andWhere()
and orWhere()
:
$query = $factory
->select()
->from('users')
->where(field('id')->gt(1))
->orWhere(field('login_at')->isNull())
->orWhere(field('is_inactive')->eq(1))
->compile();
Would produce:
SELECT *
FROM "users"
WHERE "id" > ?
OR "login_at" IS NULL
OR "is_inactive" = ?
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();
Would produce:
SELECT "u"."id", "c"."name"
FROM "users" AS "u"
JOIN "countries" AS "c" ON "u"."country_id" = "c"."id"
The join type can also be specified as the third parameters or one of the helpers can be used for common types:
leftJoin()
rightJoin()
innerJoin()
fullJoin()
ORDER BY
Ordering can be applied:
$query = $factory
->select()
->from('users')
->orderBy('username', 'asc');
Ordering can be reset:
$query->orderBy(null);
LIMIT and OFFSET
Limits and offsets can be applied:
$query = $factory
->select()
->from('posts')
->offset(10)
->limit(10)
->compile();
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 desired.