Stars: 400
Forks: 128
Pull Requests: 39
Issues: 90
Watchers: 27
Last Updated: 2022-08-04 13:55:01
An elegant lightweight and efficient SQL Query Builder with fluid interface SQL syntax supporting bindings and complicated query generation.
License: MIT License
Languages: PHP
An elegant lightweight and efficient SQL Query Builder with fluid interface SQL syntax supporting bindings and complicated query generation. Works without establishing a connection to the database.
The recommended way to install the SQL Query Builder is through Composer. Run the following command to install it:
php composer.phar require nilportugues/sql-query-builder
The SQL Query Builder allows to generate complex SQL queries standard using the SQL-2003
dialect (default) and the MySQL
dialect, that extends the SQL-2003
dialect.
The Generic Query Builder is the default builder for this class and writes standard SQL-2003.
All column aliases are escaped using the '
sign by default.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()->setTable('user');
echo $builder->write($query);
SELECT user.* FROM user
The MySQL Query Builder has its own class, that inherits from the SQL-2003 builder. All columns will be wrapped with the tilde ` sign.
All table and column aliases are escaped using the tilde sign by default.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\MySqlBuilder;
$builder = new MySqlBuilder();
$query = $builder->select()->setTable('user');
echo $builder->write($query);
SELECT user.* FROM `user`
Both Generic and MySQL Query Builder can write complex SQL queries.
Every developer out there needs at some point revising the output of a complicated query, the SQL Query Builder includes a human-friendly output method, and therefore the writeFormatted
method is there to aid the developer when need.
Keep in mind writeFormatted
is to be avoided at all cost in production mode as it adds unneeded overhead due to parsing and re-formatting of the generated statement.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()->setTable('user');
echo $builder->writeFormatted($query);
SELECT
user.*
FROM
user
More complicated examples can be found in the documentation.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->setColumns(['user_id','name','email']);
echo $builder->write($query);
SELECT user.user_id, user.name, user.email FROM user
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->setColumns(['userId' => 'user_id', 'username' => 'name', 'email' => 'email']);
echo $builder->write($query);
SELECT user.user_id AS 'userId', user.name AS 'username', user.email AS 'email' FROM user
Default logical operator for filtering using WHERE
conditions is AND
.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->setColumns([
'userId' => 'user_id',
'username' => 'name',
'email' => 'email'
])
->where()
->greaterThan('user_id', 5)
->notLike('username', 'John')
->end();
echo $builder->writeFormatted($query);
SELECT
user.user_id AS 'userId',
user.name AS 'username',
user.email AS 'email'
FROM
user
WHERE
(user.user_id < :v1)
AND (user.username NOT LIKE :v2)
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->where()
->equals('user_id', 1)
->equals('user_id', 2)
->subWhere("OR")
->lessThan($column, 10)
->greaterThan('user_id', 100)
->end();
echo $builder->writeFormatted($query);
SELECT
user.*
FROM
user
WHERE
(user.user_id = :v1)
AND (user.user_id = :v2)
AND (
(user.user_id < :v3)
OR (user.user_id > :v4)
)
Syntax for JOIN
, LEFT JOIN
, RIGHT JOIN
, INNER JOIN
, CROSS JOIN
work the exactly same way.
Here's an example selecting both table and joined table columns and doing sorting using columns from both the table and the joined table.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->setColumns([
'userId' => 'user_id',
'username' => 'name',
'email' => 'email',
'created_at'
])
->orderBy('user_id', OrderBy::DESC)
->leftJoin(
'news', //join table
'user_id', //origin table field used to join
'author_id', //join column
['newsTitle' => 'title', 'body', 'created_at', 'updated_at']
)
->on()
->equals('author_id', 1); //enforcing a condition on the join column
$query
->where()
->greaterThan('user_id', 5)
->notLike('username', 'John')
->end();
$query
->orderBy('created_at', OrderBy::DESC);
echo $builder->writeFormatted($query);
SELECT
user.user_id AS 'userId',
user.name AS 'username',
user.email AS 'email',
user.created_at,
news.title AS 'newsTitle',
news.body,
news.created_at,
news.updated_at
FROM
user
LEFT JOIN
news
ON
(news.author_id = user.user_id)
AND (news.author_id = :v1)
WHERE
(user.user_id < :v2)
AND (user.username NOT LIKE :v3)
ORDER BY
user.user_id DESC,
news.created_at DESC;
Counting rows comes in 3 possible ways, using the ALL selector *
, stating a column or stating a column and its alias.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->count()
echo $builder->write($query);
SELECT COUNT(*) FROM user;
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->count('user_id')
echo $builder->write($query);
SELECT COUNT(user.user_id) FROM user;
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->count('user_id', 'total_users')
echo $builder->write($query);
SELECT COUNT(user.user_id) AS 'total_users' FROM user;
The INSERT
statement is really straightforward.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->insert()
->setTable('user')
->setValues([
'user_id' => 1,
'name' => 'Nil',
'contact' => '[email protected]',
]);
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
INSERT INTO user (user.user_id, user.name, user.contact) VALUES (:v1, :v2, :v3)
[':v1' => 1, ':v2' => 'Nil', ':v3' => '[email protected]'];
The UPDATE
statement works just like expected, set the values and the conditions to match the row and you're set.
Examples provided below.
Important including the the where
statement is critical, or all table rows will be replaced with the provided values if the statement is executed.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->update()
->setTable('user')
->setValues([
'user_id' => 1,
'name' => 'Nil',
'contact' => '[email protected]'
])
->where()
->equals('user_id', 1)
->end();
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
UPDATE
user
SET
user.user_id = :v1,
user.name = :v2,
user.contact = :v3
WHERE
(user.user_id = :v4)
[':v1' => 1, ':v2' => 'Nil', ':v3' => '[email protected]', ':v4' => 1];
<?php
use NilPortugues\Sql\QueryBuilder\Syntax\OrderBy;
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->update()
->setTable('user')
->setValues([
'name' => 'UpdatedName',
]);
$query
->where()
->like('username', '%N')
->between('user_id', 1, 2000)
->end();
$query
->orderBy('user_id', OrderBy::ASC)
->limit(1);
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
UPDATE
user
SET
user.name = :v1
WHERE
(user.username LIKE :v2)
AND (user.user_id BETWEEN :v3 AND :v4)
ORDER BY
user.user_id ASC
LIMIT :v5
The DELETE
statement is used just like UPDATE
, but no values are set.
Examples provided below.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->delete()
->setTable('user');
$sql = $builder->write($query);
DELETE FROM user
Important including the the where
statement is critical, or all table rows will be deleted with the provided values if the statement is executed.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->delete()
->setTable('user');
$query
->where()
->equals('user_id', 100)
->end();
$query
->limit(1);
$sql = $builder->write($query);
$values = $builder->getValues();
DELETE FROM user WHERE (user.user_id = :v1) LIMIT :v2
[':v1' => 100, ':v2' => 1];
<?php
use NilPortugues\Sql\QueryBuilder\Syntax\OrderBy;
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->delete()
->setTable('user');
$query
->where()
->like('username', '%N')
->between('user_id', 1, 2000)
->end();
$query
->orderBy('user_id', OrderBy::ASC)
->limit(1);
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
DELETE FROM
user
WHERE
(user.username LIKE :v1)
AND (user.user_id BETWEEN :v2 AND :v3)
ORDER BY
user.user_id ASC
LIMIT :v4
INTERSECT is not supported by MySQL. Same results can be achieved by using INNER JOIN statement instead.
The INTERSECT
statement is really straightforward.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$select1 = $builder->select()->setTable('user');
$select2 = $builder->select()->setTable('user_emails');
$query = $builder->intersect()
->add($select1)
->add($select2);
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
SELECT user.* FROM user
INTERSECT
SELECT user_email.* FROM user_email
MINUS is not supported by MySQL. Same results can be achieved by using a LEFT JOIN statement in combination with an IS NULL or NOT IN condition instead.
The MINUS
statement is really straightforward.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$select1 = $builder->select()->setTable('user');
$select2 = $builder->select()->setTable('user_emails');
$query = $builder->minus($select1, $select2);
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
SELECT user.* FROM user
MINUS
SELECT user_email.* FROM user_email
The UNION
statement is really straightforward.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$select1 = $builder->select()->setTable('user');
$select2 = $builder->select()->setTable('user_emails');
$query = $builder->union()
->add($select1)
->add($select2);
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
SELECT user.* FROM user
UNION
SELECT user_email.* FROM user_email
The UNION ALL
statement is really straightforward.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$select1 = $builder->select()->setTable('user');
$select2 = $builder->select()->setTable('user_emails');
$query = $builder->unionAll()
->add($select1)
->add($select2);
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
SELECT user.* FROM user
UNION ALL
SELECT user_email.* FROM user_email
The following operators are available for filtering using WHERE conditionals:
public function subWhere($operator = 'OR');
public function equals($column, $value);
public function notEquals($column, $value);
public function greaterThan($column, $value);
public function greaterThanOrEqual($column, $value);
public function lessThan($column, $value);
public function lessThanOrEqual($column, $value);
public function like($column, $value);
public function notLike($column, $value);
public function match(array $columns, array $values);
public function matchBoolean(array $columns, array $values);
public function matchWithQueryExpansion(array $columns, array $values);
public function in($column, array $values);
public function notIn($column, array $values);
public function between($column, $a, $b);
public function notBetween($column, $a, $b);
public function isNull($column);
public function isNotNull($column);
public function exists(Select $select);
public function notExists(Select $select);
public function addBitClause($column, $value);
public function asLiteral($literal);
WHERE
default's operator must be changed passing to the where
method the logical operator OR
.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->where('OR')
->equals('user_id', 1)
->like('name', '%N%')
->end();
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
SELECT user.* FROM user WHERE (user.user_id = :v1) OR (user.name LIKE :v2)
Default logical operator for joining more than one HAVING
condition is AND
.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->setColumns([
'userId' => 'user_id',
'username' => 'name',
'email' => 'email',
'created_at'
])
->groupBy(['user_id', 'name'])
->having()
->equals('user_id', 1)
->equals('user_id', 2);
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
SELECT
user.user_id AS 'userId',
user.name AS 'username',
user.email AS 'email',
user.created_at
FROM
user
GROUP BY
user.user_id, user.name
HAVING
(user.user_id = :v1)
AND (user.user_id = :v2)
Same operators used in the WHERE statement are available for HAVING operations.
HAVING
default's operator must be changed passing to the having
method the logical operator OR
.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->setColumns([
'userId' => 'user_id',
'username' => 'name',
'email' => 'email',
'created_at'
])
->groupBy(['user_id', 'name'])
->having('OR')
->equals('user_id', 1)
->equals('user_id', 2);
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
SELECT
user.user_id AS 'userId',
user.name AS 'username',
user.email AS 'email',
user.created_at
FROM
user
GROUP BY
user.user_id, user.name
HAVING
(user.user_id = :v1)
OR (user.user_id = :v2)
Sometimes, a column needs to be set as a column. SQL Query Builder got you covered on this one too! Check the example below.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$selectRole = $builder->select()
->setTable('role')
->setColumns(array('role_name'))
->limit(1)
->where()
->equals('role_id', 3);
$query = $builder->select()
->setTable('user')
->setColumns(array('user_id', 'username'))
->setSelectAsColumn(array('user_role' => $selectRole))
->setSelectAsColumn(array($selectRole))
->where()
->equals('user_id', 4)
->end();
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
SELECT
user.user_id,
user.username,
(
SELECT
role.role_name
FROM
role
WHERE
(role.role_id = :v1)
LIMIT :v2, :v3
) AS 'user_role',
(
SELECT
role.role_name
FROM
role
WHERE
(role.role_id = :v4)
LIMIT :v5, :v6
) AS 'role'
FROM
user
WHERE
(user.user_id = :v7)
There are time where you need to force the same column structure (eg: UNIONs) even when lacking of a column or value. Forcing column with values gets you covered.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->setColumns(array('user_id', 'username'))
->setValueAsColumn('10', 'priority')
->where()
->equals('user_id', 1)
->end();
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
SELECT
user.user_id,
user.username,
:v1 AS 'priority'
FROM
user
WHERE
(user.user_id = :v2)
Example for MAX function.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->setColumns(array('user_id', 'username'))
->setFunctionAsColumn('MAX', array('user_id'), 'max_id')
->where()
->equals('user_id', 1)
->end();
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
SELECT
user.user_id,
user.username,
MAX(user_id) AS 'max_id'
FROM
user
WHERE
(user.user_id = :v1)
Example for CURRENT_TIMESTAMP function.
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->setColumns(array('user_id', 'username'))
->setFunctionAsColumn('CURRENT_TIMESTAMP', array(), 'server_time')
->where()
->equals('user_id', 1)
->end();
$sql = $builder->writeFormatted($query);
$values = $builder->getValues();
SELECT
user.user_id,
user.username,
CURRENT_TIMESTAMP AS 'server_time'
FROM
user
WHERE
(user.user_id = :v1)
The query builder allows adding comments to all query methods by using the setComment
method.
Some useful use cases examples can be :
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()
->setTable('user')
->setComment('This is a comment');
$sql = $builder->write($query);
-- This is a comment
SELECT user.* FROM user
Testing has been done using PHPUnit and Travis-CI. All code has been tested to be compatible from PHP 5.4 up to PHP 5.6 and HHVM.
To run the test suite, you need Composer:
php composer.phar install --dev
php bin/phpunit
Nil Portugués Calderó
SQL Query Builder is licensed under the MIT license.
Copyright (c) 2014 Nil Portugués Calderó
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.