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
If you want to use Latitude and do not have access to PHP7, PHP 5.6 compatible
versions are tagged in 0.x
:
composer require latitude/latitude '^0.1'
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 users
The columns can also be passed at construction:
$select = SelectQuery::make(
'id',
'username'
)
->from('users');
echo $select->sql();
// SELECT id, username FROM users
columns(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 limit)
offset(int offset)
distinct(bool or 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 id
use 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_at
use 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 id
To 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 Object
By 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 Object
When 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 Object
The 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 before and after the
value automatically:
echo like::any('John');
// "%John%"
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 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.id
Expressions 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 Object
In 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
IN
conditions. 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!