A SQL query builder with zero dependencies. Attempts to be PSR-1, PSR-2, and PSR-4 compliant.
Latitude is heavily influenced by the design of Aura.SqlQuery.
composer require latitude/latitude
Latitude includes both a query builder and a powerful set of escaping helpers.
The query builder allows the fluent generation of SELECT, INSERT, UPDATE,
and DELETE statements. The escaping helpers assist in protecting against SQL
injection and identifier quoting for MySQL, SQL Server, Postgres, and other
databases that follow SQL standards.
Query Types
Helpers
use Latitude\QueryBuilder\SelectQuery;
$select = SelectQuery::make()
->from('users');
echo $select->sql();
// SELECT * FROM usersThe columns can also be passed at construction:
$select = SelectQuery::make(
'id',
'username'
)
->from('users');
echo $select->sql();
// SELECT id, username FROM userscolumns(string|Expression ...column)from(string ...table)join(string table, conditions)innerJoin(...)outerJoin(...)leftJoin(...)leftOuterJoin(...)rightJoin(...)rightOuterJoin(...)fullJoin(...)fullOuterJoin(...)where(conditions)groupBy(string ...columns)having(conditions)orderBy(array ...pairs)either[column]or[column, direction]limit(int|null limit)offset(int|null offset)distinct(bool|null)defaults tofalse
Refer the source for more details. It aims to be easy to read!
use Latitude\QueryBuilder\InsertQuery;
$insert = InsertQuery::make('users', [
'username' => 'jsmith',
]);
echo $insert->sql();
// INSERT INTO users (username) VALUES (?)
print_r($insert->params());
// ["jsmith"]Multiple sets of values can be added to the insert:
$insert = InsertQuery::make('tokens')->columns('token');
$insert->values('token-one');
$insert->values('token-two');
echo $insert->sql();
// INSERT INTO tokens (token) VALUES (?), (?)There is also a Postgres extension that allows the use of the RETURNING statement:
use Latitude\QueryBuilder\Postgres\InsertQuery;
$insert = InsertQuery::make(...)
->returning([
'id',
]);
echo $insert->sql();
// INSERT INTO users (username) VALUES (?) RETURNING iduse Latitude\QueryBuilder\UpdateQuery;
use Latitude\QueryBuilder\Conditions;
$update = UpdateQuery::make('users', [
'username' => 'mr-smith',
])
->where(
Conditions::make('id = ?', 5)
);
echo $update->sql();
// UPDATE users SET username = ? WHERE id = ?
print_r($update->params());
// ["mr-smith", 5]There is also a Postgres extension that allows the use of the RETURNING statement:
use Latitude\QueryBuilder\Postgres\UpdateQuery;
$update = UpdateQuery::make(...)
->returning([
'updated_at',
]);
echo $update->sql();
// UPDATE users SET username = ? WHERE id = ? RETURNING updated_atThe MySQL extension supports ORDER BY and LIMIT to limit the number of rows updated:
use Latitude\QueryBuilder\MySQL\UpdateQuery;
$update = UpdateQuery::make(...)
->orderBy(['username', 'DESC'])
->limit(5);
echo $update->sql();
// UPDATE users SET updated_at = ? ORDER BY username DESC LIMIT 5use Latitude\QueryBuilder\DeleteQuery;
use Latitude\QueryBuilder\Conditions;
$delete = DeleteQuery::make('users')
->where(
Conditions::make('last_login IS NULL')
);
echo $select->sql();
// DELETE FROM users WHERE last_login IS NULL
print_r($delete->params());
// []There is also a Postgres extension that allows the use of the RETURNING statement:
use Latitude\QueryBuilder\Postgres\DeleteQuery;
$delete = DeleteQuery::make(...)
->returning([
'id',
]);
echo $delete->sql();
// DELETE FROM users WHERE last_login IS NULL RETURNING idThe MySQL extension supports ORDER BY and LIMIT to limit the number of rows deleted:
use Latitude\QueryBuilder\MySQL\DeleteQuery;
$delete = DeleteQuery::make(...)
->orderBy(['username', 'DESC'])
->limit(5);
echo $delete->sql();
// DELETE FROM users ORDER BY username DESC LIMIT 5To simplify dependency injection, a factory class exists that can used that will always return the most specific type of builder or helper for the given database.
use Latitude\QueryBuilder\QueryFactory;
$factory = new QueryFactory('pgsql');
$insert = $factory->insert(...);
// Latitude\QueryBuilder\Postgres\InsertQuery Object
$delete = $factory->delete(...);
// Latitude\QueryBuilder\Postgres\DeleteQuery Object
$select = $factory->select(...);
// Latitude\QueryBuilder\SelectQuery ObjectBy default, the factory will also set the default identifier for the selected database engine. To disable setting the default, set the second parameter:
$factory = new QueryFactory('pgsql', false);
$identifier = Identifier::getDefault();
// Latitude\QueryBuilder\Identifier ObjectWhen the default identifier is not enabled, the specific identifier can be fetched
using the identifier() method:
$factory = new QueryFactory('mysql', false);
$identifier = $factory->identifier();
// Latitude\QueryBuilder\MySQL\Identifier ObjectThe conditions builder acts as both a dynamic condition builder and a parameter holder.
use Latitude\QueryBuilder\Conditions;
$statement = Conditions::make('id = ?', 5)
->andWith('last_login IS NULL');
echo $statement->sql();
// id = ? AND last_login IS NULL
print_r($statement->params());
// [5]Conditions are used for JOIN, WHERE, and HAVING clauses. They can also be used independently for custom query constructions.
Conditions can also produce groupings:
$statement = Conditions::make()
->group()
->with('subtotal > ?')
->andWith('taxes > 0')
->end()
->orGroup()
->with('cost > ?')
->andWith('cancelled = true')
->end();
echo $statement->sql();
// (subtotal > ? AND taxes > 0) OR (cost > ? AND cancelled = true)Note: Be sure to call end() to close the group, or you may get unexpected
query results!
Because PDO does not have an easy way to handle array values for IN conditions,
the ValueList wrapper can be used to expand the ? placeholder in the condition.
use Latitude\QueryBuilder\Conditions;
use Latitude\QueryBuilder\ValueList as in;
$statement = Conditions::make('role IN ?', in::make([1, 12, 5]))
echo $statement->sql();
// role IN (?, ?, ?)
print_r($statement->params());
// [1, 12, 5]Because LIKE conditions allow for "wildcard" expansion using % or _,
a special LikeValue helper exists that will escape existing wildcards in
the value. This helps protect against SQL query hijacking.
use Latitude\QueryBuilder\Conditions;
use Latitude\QueryBuilder\LikeValue as like;
$statement = Conditions::make()
->with('name LIKE ?', like::escape('%%hijack'));
print_r($statement->params());
// ["\%\%hijack"];The LikeValue helper also supports adding wildcards value automatically:
echo like::any('John'); // "%John%"
echo like::starts('Joh'); // "Joh%"
echo like::ends('ohn'); // "%ohn"There is also a MSSQL extension that will escape character ranges:
use Latitude\QueryBuilder\SqlServer\LikeValue as like;
echo like::escape('[range]');
// "\[range\]"Sometimes it is more efficient to use a sub-query as part of a condition, rather than executing a query to get values that will be used as conditions. For example:
use Latitude\QueryBuilder\SelectQuery;
use Latitude\QueryBuilder\Conditions as c;
$user_ids_from_orders = SelectQuery::make('user_id')
->from('orders')
->where(c::make('placed_at BETWEEN ? AND ?', '2017-01-01', '2017-12-31'));
$select = SelectQuery::make()
->from('users')
->where(
c::make('id IN (?)', $user_ids_from_orders)
);
echo $select->sql();
// SELECT * FROM users WHERE id IN (
// SELECT user_id FROM orders WHERE placed_at BETWEEN ? AND ?
// )
print_r($select->params());
// ['2017-01-01', '2017-12-31']The builder includes a wrapper for SQL aliases which can be used for column names
in SELECT statements:
use Latitude\QueryBuilder\Alias as a;
$select = SelectQuery::make(
a::make('id', 'user_id')
)
->from('users');
echo $select->sql();
// SELECT id AS user_id FROM usersNote: Aliases are automatically generated when using 'column alias' as
a column name. The same is true for 'table alias' as a table alias.
Aliases can also be used for complete queries:
$select = SelectQuery::make('id')->from('users');
$alias = a::make($select, 'u');
echo $alias->sql();
// (SELECT id FROM users) AS uThis is generally used to create a temporary table for a JOIN expression.
The builder includes a simple wrapper for database expressions which can be used
for column names in SELECT statements and values in other statements:
use Latitude\QueryBuilder\Expression as e;
use Latitude\QueryBuilder\Conditions as c;
$select = SelectQuery::make(...[
'u.id',
e::make('COUNT(%s) AS %s', 'r.id', 'total'),
])
->from('users u')
->join('roles r', c::make('r.id = u.role_id'))
->groupBy('u.id');
echo $select->sql();
// SELECT u.id, COUNT(r.id) AS total FROM users AS u JOIN roles AS r ON r.id = u.role_id GROUP BY u.idExpressions can also be used as values in INSERT and UPDATE statements:
use Latitude\QueryBuilder\Expression as e;
$insert = InsertQuery::make('users', [
'username' => 'ada.love',
'created_at' => e::make('NOW()'),
]);
echo $insert->sql();
// INSERT INTO users (username, created_at) VALUES (?, NOW())
print_r($insert->params());
// ["ada.love"]By default all table and column (identifier) references will be validated.
Any aliases in the form identifier alias or identifier as alias will be
changed to the canonical form identifier AS alias.
Note: All identifiers in Expression objects will also be escaped when
SQL is generated by query builders.
To enable database specific identifier escaping, pass an instance of Identifier
to any sql() method. Most databases can use the Common extension:
use Latitude\QueryBuilder\Conditions;
use Latitude\QueryBuilder\Common\Identifier;
use Latitude\QueryBuilder\SelectQuery;
$select = SelectQuery::make()
->from('users u')
->where(Conditions::with('u.id = ?'));
echo $select->sql(Identifier::make());
// SELECT * FROM "users" AS "u" WHERE "u"."id" = ?
There is an SQL Server extension that will escape using brackets:
use Latitude\QueryBuilder\SqlServer\Identifier;
echo $select->sql(Identifier::make());
// SELECT * FROM [users] AS [u] WHERE [u].[id] = ?
As well as a MySQL extension that will escape using backticks:
use Latitude\QueryBuilder\MySQL\Identifier;
echo $select->sql(Identifier::make());
// SELECT * FROM `users` AS `u` WHERE `u`.`id` = ?
If only one database type is used in your application, you can set the global default identifier:
use Latitude\QueryBuilder\MySQL\Identifier as MySqlIdentifier;
use Latitude\QueryBuilder\Identifier;
Identifier::setDefault(MySqlIdentifier::make());Now all queries will use the MySQL Identifier by default.
The default can be fetched using the getDefault() method:
$identifier = Identifier::getDefault();
// Latitude\QueryBuilder\MySQL\Identifier ObjectIn INSERT and UPDATE queries, boolean and null values will be added directly
the query, rather than as placeholders. This is due to the fact that
PDOStatement::execute($params) will attempt to cast all parameters to strings,
which does not work correctly with booleans or nulls.
See PDOStatement::execute documentation
for more information.
Many query builders depend directly on PDO or use complicated condition syntax that is, in my opinion, less than ideal. Very few require PHP 7 strict type hinting.
A couple of query builders require specific mention, as they are quite good.
The external interface of Aura.SqlQuery is fantastic and Latitude borrows heavily on the ergonomics of it. However, there are two very distinct flaws in SqlQuery that I am unhappy with:
- It does not allow for sequential
?placeholders. While this is a relatively minor thing, it forces the parameters to be bound in a very specific way. - It defers handling of array values for
INconditions. This isn't a problem when using the Aura PDO wrapper Aura.Sql, which unpacks array values into a list of values. If you choose not use Aura.Sql, it becomes much more complicated.
Due to these two issues that cannot be easily patched out, and because there is no sign of a PHP7 version of Aura components, I decided to write my own.
Latitude is licensed under MIT and can be used for any personal or commercial project. If you really like it, feel free to buy me a beer sometime!