phossa2 / query
A SQL query builder library with concise syntax for PHP.
Requires
- php: ~5.4|~7.0
- phossa2/shared: ^2.0.21
Requires (Dev)
- phpunit/phpunit: 4.*
- squizlabs/php_codesniffer: 2.*
Replaces
This package is not auto-updated.
Last update: 2024-10-26 20:20:52 UTC
README
phossa2/query is a SQL query builder library with concise syntax for PHP. It supports Mysql dialect and more coming.
It requires PHP 5.4, supports PHP 7.0+ and HHVM. It is compliant with PSR-1, PSR-2, PSR-3, PSR-4, and the proposed PSR-5.
Features
-
Statement with positioned or named parameters.
-
Beautiful output with different settings.
-
Ongoing support for different dialects like
Mysql
and more.
Installation
Install via the composer
utility.
composer require "phossa2/query"
or add the following lines to your composer.json
{ "require": { "phossa2/query": "2.*" } }
Usage
-
Getting started
Start with a query builder first, then query.
use Phossa2\Query\Builder; // a builder default to table 'Users' and Mysql as default dialect $builder = new Builder(); $users = $builder->table('Users'); // SELECT * FROM `Users` LIMIT 10 $sql = $users->select()->limit(10)->getSql(); // INSERT INTO `Users` (`usr_name`) VALUES ('phossa') $sql = $users->insert(['usr_name' => 'phossa'])->getSql(); // reset builder to table 'Sales' as 's' $sales = $users->table('Sales', 's'); // SELECT * FROM `Sales` AS `s` WHERE `user_id` = 12 $qry = $sales->select()->where('user_id', 12); // SELECT * FROM `Sales` AS `s` WHERE `user_id` = ? $sql = $qry->getStatement(); // with positioned parameters // [12] var_dump($qry->getBindings());
-
-
Columns/fields
Columns can be specified in the
select($col, ...)
,col($col, $alias)
orcol(array $cols)
.// SELECT * FROM `Users` $qry = $users->select(); // SELECT `user_id`, `user_name` FROM `Users` $qry = $users->select('user_id', 'user_name'); // SELECT `user_id`, `user_name` AS `n` FROM `Users` $qry = $users->select()->col('user_id')->col('user_name', 'n'); // same as above $qry = $users->select()->col(['user_id', 'user_name' => 'n']);
Raw string can be provided using
colRaw($string, array $parameters)
// SELECT COUNT(user_id) AS cnt FROM `Users` $qry = $users->select()->colRaw('COUNT(user_id) AS cnt'); // SELECT CONCAT(user_name, 'x') AS con FROM `Users` $qry = $users->select()->colRaw('CONCAT(user_name, ?) AS con', ['x']);
Common functions like
cnt($col, $alias)
,min($col, $alias)
,max($col, $alias)
,avg($col, $alias)
,sum($col, $alias)
can also be used directly.// SELECT MAX(`user_id`) AS `maxId` FROM `Users` $qry = $users->select()->max('user_id', 'maxId');
Generic column template by using
colTpl($template, $cols, $alias)
,// SELECT SUM(DISTINCT `score`) AS `s` FROM `Users` $qry = $users->select()->colTpl('SUM(DISTINCT %s)', 'score', 's'); // SELECT CONCAT(`fname`, ' ', `lname`) AS `fullName` FROM `Users` $qry = $users->select()->colTpl("CONCAT(%s, ' ', %s)", ['fname', 'lname'], 'fullName');
Subquery can also be use in
col()
,// SELECT (SELECT MAX(`user_id`) FROM `oldUsers`) AS `maxId` FROM `Users` $qry = $users->select()->col( $users->select()->max('user_id')->table('oldUsers'), 'maxId' );
-
Distinct
DISTINCT
can be specified withdistinct(...)
,// SELECT DISTINCT `user_alias` FROM `Users` $qry = $users->select()->distinct('user_alias'); // SELECT DISTINCT `user_alias` AS `a` FROM `Users` $qry = $users->select()->distinct()->col('user_alias', 'a');
-
From
from($table, $alias)
ortable($table, $alias)
can be used with$builder
object or query object such as$builder->select()
.Using
table()
to replace any existing tables,// $sales is a clone of builder $users with table replaced $sales = $users->table('Sales'); // or replace table in the select query object $select = $users->select()->table('Sales', 's'); // SELECT * FROM `Users` AS `u`, `Accounts` AS `a` $qry = $users->select()->table(['Users' => 'u', 'Accounts' => 'a']);
Using
from()
to append to any existing tables,// SELECT * FROM `Users`, `Sales` AS `s` $select = $users->select()->from('Sales', 's'); // builder has two tables now $usersAndSales = $users->from('Sales', 's');
Subqueries can be used in
from()
ortable()
,// SELECT * FROM (SELECT `user_id` FROM `oldUsers`) AS `u` $qry = $users->select()->table( $users->select('user_id')->table('oldUsers'), 'u' );
-
Group
Group result with
group($col, ...)
,// SELECT `grp_id`, COUNT(*) AS `cnt` FROM `Users` GROUP BY `grp_id` $qry = $users->select()->col('grp_id')->cnt('*', 'cnt')->group('grp_id');
Multiple
group()
andgroupRaw($str, array $params)
,// SELECT `grp_id`, `age`, COUNT(*) AS `cnt` FROM `Users` GROUP BY `grp_id`, age ASC $qry = $users->select('grp_id', 'age')->cnt('*', 'cnt') ->group('grp_id')->groupRaw('age ASC');
Template can also be used with
groupTpl($template, $cols)
,// GROUP BY `year` WITH ROLLUP $users->select()->groupTpl('%s WITH ROLLUP', 'year')
-
Join
Join using
join($table, $col)
,// SELECT * FROM `Users` INNER JOIN `Accounts` $qry = $users->select()->join('Accounts'); // SELECT * FROM `Users` INNER JOIN `Accounts` ON `Users`.`id` = `Accounts`.`id` $qry = $users->select()->join('Accounts', 'id');
Specify alias for the joined table,
// SELECT * FROM `Users` INNER JOIN `Accounts` AS `a` ON `Users`.`id` = `a`.`id` $qry = $users->select()->join(['Accounts', 'a'], 'id');
Join table with different column name,
// SELECT * FROM `Users` INNER JOIN `Accounts` AS `a` ON `Users`.`id` = `a`.`user_id` $qry = $users->select()->join(['Accounts'], 'a'], ['id', 'user_id']); // same as above $qry = $users->select()->join(['Accounts'], 'a'], ['Users.id', 'a.user_id']);
Join with operator specified,
// SELECT * FROM `Users` INNER JOIN `Accounts` AS `a` ON `Users`.`id` <> `a`.`user_id` $qry = $users->select()->join(['Accounts', 'a'], ['id', '<>', 'user_id']);
Multiple joins,
// SELECT * FROM `Users` // INNER JOIN `Sales` AS `s` ON `Users`.`uid` = `s`.`uid` // INNER JOIN `Orders` AS `o` ON `Users`.`uid` = `o`.`oid` $qry = $users->select() ->join(['Sales', 's'], ['uid', '=', 'uid']) ->join(['Orders', 'o'], ['uid', 'o.oid']);
Subqueries in join,
// SELECT * FROM `Users` INNER JOIN (SELECT `uid` FROM `oldUsers`) AS `x` ON `Users`.`uid` = `x`.`uid` $qry = $users->select()->join( [$builder->select('uid')->from('oldUsers'), 'x'], 'uid' );
Other joins
leftJoin()
,rightJoin()
,outerJoin()
,leftOuterJoin()
,rightOuterJoin()
,crossJoin()
are supported. If want to use your own join,joinRaw()
is handy.// SELECT * FROM `Users` OUTER JOIN `Accounts` AS `a` ON `Users`.`id` = `a`.`id` $qry = $users->select()->outerJoin(['Accounts', 'a'], 'id'); // SELECT * FROM `Users` NATURAL JOIN Accounts AS a ON Users.id = a.id $qry = $users->select()->joinRaw('NATURAL JOIN', 'Accounts AS a ON Users.id = a.id');
-
Limit
LIMIT
andOFFSET
are supported,// SELECT * FROM `Users` LIMIT 30 OFFSET 10 $qry = $users->select()->limit(30, 10); // SELECT * FROM `Users` LIMIT 20 OFFSET 15 $qry = $users->select()->limit(20)->offset(15);
Or use
page($pageNum, $pageLength)
where$pageNum
starts from1
,// SELECT * FROM `Users` LIMIT 30 OFFSET 60 $qry = $users->select()->page(3, 30);
-
Order
Order by ASC or DESC
// SELECT * FROM `Users` ORDER BY `age` ASC, `score` DESC $qry = $users->select()->order('age')->orderDesc('score');
Or raw mode
// SELECT * FROM `Users` ORDER BY age ASC, score DESC $qry = $users->select()->orderRaw('age ASC, score DESC');
-
Where
Simple where clauses,
// SELECT * FROM `Users` WHERE age > 18 $qry = $users->select()->where('age > 18'); // SELECT * FROM `Users` WHERE `age` = 18 $qry = $users->select()->where('age', 18); // SELECT * FROM `Users` WHERE `age` < 18 $qry = $users->select()->where('age', '<', 18);
Multiple wheres,
// SELECT * FROM `Users` WHERE `age` > 18 AND `gender` = 'male' $qry = $users->select()->where(['age' => ['>', 18], 'gender' => 'male']); // same as above $qry = $users->select()->where('age', '>', 18)->andWhere('gender','male');
Complex where,
// SELECT * FROM `Users` WHERE (`id` = 1 OR (`id` < 20 OR `id` > 100)) OR `name` = 'Tester' $qry = $users->select()->where( $users->expr()->where('id', 1)->orWhere( $users->expr()->where('id', '<', 20)->orWhere('id', '>', 100) ) )->orWhere('name', 'Tester');
Raw mode,
// SELECT * FROM `Users` WHERE age = 18 OR score > 90 $qry = $users->select()->whereRaw('age = 18')->orWhereRaw('score > 90');
with
NOT
,// SELECT * FROM `Users` WHERE NOT `age` = 18 OR NOT `score` > 90 $qry = $users->select()->whereNot('age', 18)->orWhereNot('score', '>', 90);
Where
IN
andBETWEEN
// SELECT * FROM `Users` WHERE `age` IN (10,12,15,18,20) $qry = $users->select()->where('age', 'IN', [10,12,15,18,20]); // SELECT * FROM `Users` WHERE `age` NOT BETWEEN 10 AND 20 $qry = $users->select()->where('age', 'NOT BETWEEN', [10,20]);
IS NULL
,// SELECT * FROM `Users` WHERE `age` IS NULL $qry = $users->select()->where('age', 'IS', NULL);
EXISTS
,// SELECT * FROM `Sales` WHERE EXISTS (SELECT `user_id` FROM `Users`) $sql = $sales->select()->where('', 'EXISTS', $users->select('user_id'))->getSql();
-
Having
Similar to
WHERE
clause,// SELECT * FROM `Users` HAVING `age` = 10 OR `level` > 20 $qry = $users->select()->having('age', 10)->orHaving('level', '>', 20);
-
Union
union()
orunionAll()
can be used with builder or query object,// SELECT * FROM `Users` // UNION // SELECT * FROM `oldUsers1` // UNION ALL // SELECT `user_id` FROM `oldUsers2` $sql = $users->select() ->union() ->select()->table('oldUsers1') ->unionAll() ->select('user_id')->table('oldUsers2') ->getSql() // (SELECT * FROM `Users`) UNION (SELECT * FROM `oldUesrs`) ORDER BY `user_id` ASC LIMIT 10 $sql = $builder->union( $builder->select()->table('Users'), $builder->select()->table('oldUsers') )->order('user_id')->limit(10)->getSql();
-
-
Single insert statement,
// INSERT INTO `users` (`uid`, `uname`) VALUES (2, 'phossa') $sql = $users->insert(['uid' => 2, 'uname' => 'phossa'])->getSql(); // same as above $sql = $users->insert()->set('uid', 2)->set('uname', 'phossa')->getSql(); // same as above $sql = $users->insert()->set(['uid' => 2, 'uname' => 'phossa'])->getSql();
Multiple data rows,
// INSERT INTO `Users` (`uid`, `uname`) VALUES (2, 'phossa'), (3, 'test') $qry = $users->insert() ->set(['uid' => 2, 'uname' => 'phossa']) ->set(['uid' => 3, 'uname' => 'test']);
Insert with
DEFAULT
values// INSERT INTO `Users` (`uid`, `uname`, `phone`) VALUES (2, 'phossa', DEFAULT), (3, 'test', '1234') $qry = $users->insert([ ['uid' => 2, 'uname' => 'phossa'], ['uid' => 3, 'uname' => 'test', 'phone' => '1234'] ]);
Insert
NULL
instead of default values,// INSERT INTO `Users` (`uid`, `uname`, `phone`) VALUES (2, 'phossa', NULL), (3, 'test', '1234') $sql = $qry->getSql(['useNullAsDefault' => true]);
Insert with
SELECT
subquery,// INSERT INTO `Users` (`uid`, `uname`) SELECT `user_id`, `user_name` FROM `oldUsers` $qry = $users->insert()->set(['uid', 'uname']) ->select('user_id', 'user_name')->table('oldUsers');
-
Common update statement,
// UPDATE `Users` SET age = age + 1 $qry = $users->update()->set('age = age + 1'); // UPDATE `Users` SET `user_name` = 'phossa' WHERE `user_id` = 3 $qry = $users->update(['user_name' => 'phossa'])->where('user_id', 3); // UPDATE `Users` SET `user_name` = 'phossa', `user_addr` = 'xxx' WHERE `user_id` = 3 $qry = $users->update()->set('user_name','phossa') ->set('user_addr', 'xxx')->where('user_id', 3);
increment($col, $step)
anddecrement($col, $step)
,// UPDATE `Users` SET `age` = `age` + 2 WHERE `user_id` = 2 $qry = $users->update()->increment('age', 2)->where('user_id', 2);
With
Mysql
extensions,// UPDATE IGNORE `Users` SET `user_id` = `user_id` + 10, `user_status` = user_status | 2 ORDER BY `user_id` ASC LIMIT 10 $qry = $users->update()->hint('IGNORE') ->setTpl('user_id', '%s + ?', 'user_id', [10]) ->setRaw('user_status', 'user_status | 2') ->order('user_id')->limit(10);
-
Mysql version of replace,
// REPLACE INTO `Users` (`user_id`, `user_name`) VALUES (3, 'phossa') $qry = $users->replace(['user_id' => 3, 'user_name' => 'phossa']);
-
Single table deletion,
// DELETE FROM `Users` WHERE `user_id` > 10 ORDER BY `user_id` ASC LIMIT 10 $qry = $users->delete()->where('user_id', '>', 10) ->order('user_id')->limit(10);
Multiple tables deletion
// DELETE `u`, `a` FROM `Users` AS `u` INNER JOIN `Accounts` AS `a` // ON `u`.`user_id` = `a`.`user_id` WHERE `a`.`total_amount` < 10 $qry = $builder->delete('u', 'a')->table('Users', 'u') ->join(['Accounts', 'a'], 'user_id')->where('a.total_amount', '<', 10);
Advanced topics
-
Expression can be used to construct complex
WHERE
// SELECT // * // FROM // "Users" // WHERE // ("age" < 18 OR "gender" = 'female') // OR ("age" > 60 OR ("age" > 55 AND "gender" = 'female')) $qry = $builder->select()->table('Users')->where( $builder->expr()->where('age', '<', 18)->orWhere('gender', 'female') )->orWhere( $builder->expr()->where('age', '>' , 60)->orWhere( $builder->expr()->where('age', '>', 55)->where('gender', 'female') ) );
Join with complex
ON
,// SELECT * FROM `Users` INNER JOIN `Sales` // (ON `Users`.`uid` = `Sales`.`s_uid` OR `Users`.`uid` = `Sales`.`puid`) $sql = $users->select()->join('Sales', $builder->expr()->on('Users.uid', 'Sales.s_uid')->orOn('Users.uid', 'Sales.puid') )->getSql();
-
Raw string to bypass the quoting and escaping,
// SELECT score + 10 FROM `Students` WHERE `time` < NOW() $qry = $builder->select()->colRaw('score + 10') ->from("Students")->where('time', '<', $builder->raw('NOW()')); // SELECT `grp_id`, COUNT(*) AS `cnt` FROM `Users` GROUP BY grp_id ASC $qry = $users->select()->col('grp_id')->cnt('*', 'cnt')->groupRaw('grp_id ASC');
Raw string with positioned parameters,
// SELECT * FROM `Students` WHERE `age` IN RANGE(1, 1.2) $qry = $builder->select()->from("Students")->where("age", "IN", $builder->raw('RANGE(?, ?)', [1, 1.2])); // same as above $qry = $builder->select()->from("Students") ->whereRaw("`age` IN RANGE(?, ?)", [1, 1.2]);
-
Template with
colTpl()
,groupTpl()
etc.Using template will make quotation of db names possible,
// SELECT MAX(`score`) AS max FROM `Users` $sql = $users->select()->colTpl('MAX(%s)', 'score', 'max')->getSql();
-
before()
,after()
,hint()
andoption()
Sometimes, non-standard SQL wanted and no methods found.
before()
andafter()
will come to rescue.// INSERT INTO "users" ("id", "name") VALUES (3, 'phossa') ON DUPLICATE KEY UPDATE id=id+10 $qry = $users->insert()->set('id', 3)->set('name', 'phossa') ->after('VALUES', 'ON DUPLICATE KEY UPDATE id=id+?', [10]);
hint()
add hints right after the statement word, andoption()
will append to the end of sql,// INSERT IGNORE INTO "users" ("id", "name") VALUES (3, 'phossa') ON DUPLICATE KEY UPDATE id=id+10 $qry = $users->insert()->hint('IGNORE') ->set('id', 3)->set('name', 'phossa') ->option('ON DUPLICATE KEY UPDATE id=id+?', [10]);
-
phossa2/query can return statement for driver to prepare and use the
getBindings()
to get the values to bind.$qry = $users->select()->where("user_id", 10); // SELECT * FROM `Users` WHERE `user_id` = ? $sql = $qry->getStatement(); // values to bind: [10] $val = $qry->getBindings();
Or named parameters,
$qry = $users->select()->where("user_name", ':name'); // SELECT * FROM `Users` WHERE `user_name` = :name $sql = $query->getNamedStatement();
Parameters can be applied to raw or template methods,
// SELECT * FROM `Users` GROUP BY `year` + 10 $sql = $users->select()->groupRaw('`year` + ?', [10])->getSql(); // same as above $sql = $users->select()->groupTpl('%s + ?', 'year', [10])->getSql();
-
Settings can be applied to
$builder
during instantiation or usingsetSettings()
,// builder instantiation $users = new Builder('Users', new Mysql(), ['autoQuote' => false]); // adjust settings $users->setSettings(['autoQuote' => true]);
Or applied when output with
getSql()
orgetStatement()
,$sql = $users->select()->getSql(['autoQuote' => false]);
Indented sql,
// SELECT // * // FROM // `Users` $sql = $users->select()->getSql(['seperator' => "\n", 'indent' => " "]);
List of settings,
-
autoQuote
: boolean. Quote db identifier or not. -
positionedParam
: boolean. Output with positioned parameter or not. -
namedParam
: boolean. Output with named parameter or not. -
seperator
: string, default to ' '. Seperator between clauses. -
indent
: string, default to ''. Indent prefix for clauses. -
escapeFunction
: callabel, default tonull
. Function used to quote and escape values. -
useNullAsDefault
: boolean.
-
Change log
Please see CHANGELOG from more information.
Testing
$ composer test
Contributing
Please see CONTRIBUTE for more information.
Dependencies
-
PHP >= 5.4.0
-
phossa2/shared >= 2.0.21