phossa / phossa-query
A SQL query builder library for PHP
Requires
- php: >=5.4.0
- phossa/phossa-shared: ~1.0.8
This package is not auto-updated.
Last update: 2024-10-26 20:04:19 UTC
README
phossa-query is a SQL query builder library with concise syntax for PHP. It supports Mysql, SQLite, Postgres, Sql server, Oracle etc.
It requires PHP 5.4 and supports PHP 7.0+, HHVM. It is compliant with PSR-1, PSR-2, PSR-4.
Features
-
Support SELECT, INSERT, UPDATE, REPLACE, DELETE, CREATE TABLE.
-
Complex sql building with expr(), raw(), before(), after() etc.
-
Statement with positioned or named parameters.
-
Beautiful output with different settings.
-
Ongoing support for different dialects like
Mysql
,Sqlite
and more.
Installation
Install via the composer
utility.
composer require "phossa/phossa-query=1.*"
or add the following lines to your composer.json
{ "require": { "phossa/phossa-query": "^1.0.0" } }
Usage
-
Getting started
Start with a query builder first, then the query.
use Phossa\Query\Builder; use Phossa\Query\Dialect\Mysql; // a builder with mysql dialect,default 'users' table $users = new Builder(new Mysql(), 'users'); // SELECT * FROM `users` LIMIT 10 $sql = $users->select()->limit(10)->getStatement(); // INSERT INTO `users` (`usr_name`) VALUES ('phossa') $sql = $users->insert(['usr_name' => 'phossa'])->getStatement(); // A new builder (cloned) with default table 'sales' $sales = $users->table(['sales' => 's']); $query = $sales->select()->where('user_id', 12); // SELECT * FROM `sales` AS `s` WHERE `user_id` = ? $sql = $query->getStatement(['positionedParam' => true]); // value bindings: [12] $val = $query->getBindings();
-
-
Columns
Columns can be specified in the
select()
or incol()
(or with its aliasfield()
).Column with optional alias name,
// SELECT `user_name` AS `n` FROM `users` $query = $users->select('user_name', 'n');
Multiple columns,
// SELECT `id`, `user_name` AS `n` FROM `users` $query = $users->select()->col(['id', 'user_name' => 'n']); // same as above $query = $users->select()->col('id')->field('user_name', 'n');
Raw mode,
// SELECT COUNT(user_id) AS `cnt` FROM `users` $query = $users->select()->colRaw(['COUNT(user_id)' => 'cnt']);
Common functions like
count()
,min()
,max()
,avg()
,sum()
andsumDistinct()
can be used in the columns.// SELECT COUNT(`user_id`) AS `cnt`, MAX(`user_id`) AS `max_id` FROM `users` $query = $users->select()->count('user_id', 'cnt')->max('user_id', 'max_id');
Generic functions by using
func($template, $colName, $colAlias)
,// SELECT CONCAT(`user_name`, "XXX") AS `new_name` FROM `users` $query = $users->select()->func('CONCAT(%s, "XXX")', 'user_name', 'new_name');
-
Distinct
DISTINCT
can be specified withdistinct()
// SELECT DISTINCT `user_alias` FROM `users` $query = $users->select('user_alias')->distinct();
-
From
FROM
can used with builder object or select object.Use
select(false)
to ignore default table from the builder,// SELECT * FROM `sales` AS `s` $query = $users->select(false)->from('sales', 's');
Builder tables are carried over, ```php // SELECT * FROM `users`, `sales` $query = $users->select()->from('sales');
Multiple tables (with aliases) supported,
// SELECT * FROM `users` AS `u`, `accounts` AS `a` $query = $users->select()->from(['users' => 'u', 'accounts' => 'a']);
Subqueries can be used in
from()
,// builder without default table[s] $builder = $users->table(false); // SELECT * FROM (SELECT `user_id` FROM `oldusers`) AS `u` $query = $builder->select()->from( $builder->select('user_id')->from('oldusers'), 'u' );
-
Group by
Single
GROUP BY
,// SELECT `group_id`, COUNT(*) AS `cnt` FROM `users` GROUP BY `group_id` $query = $users->select()->col('group_id')->count('*', 'cnt')->groupBy('group_id');
Multiple
groupBy()
and raw mode can be used,// SELECT `group_id`, `age`, COUNT(*) AS `cnt` FROM `users` GROUP BY `group_id`, age ASC $query = $users->select()->col('group_id')->col('age')->count('*', 'cnt') ->groupBy('group_id')->groupByRaw('age ASC');
-
Join
Join with another table with same column name
// SELECT * FROM `users` INNER JOIN `accounts` ON `users`.`id` = `accounts`.`id` $query = $users->select()->join('accounts', 'id');
Specify alias for the join table,
// SELECT * FROM `users` INNER JOIN `accounts` AS `a` ON `users`.`id` = `a`.`id` $query = $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` $query = $users->select()->join('accounts a', 'id', 'user_id');
Join with operator specified,
// SELECT * FROM `users` INNER JOIN `accounts` AS `a` ON `users`.`id` <> `a`.`user_id` $query = $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 `order` AS `o` ON `users`.`uid` = `o`.`o_uid` $query = $users->select() ->join('sales s', 'uid', '=', 'uid') ->join('order o', 'uid', 'o_uid') ->getStatement();
Subqueries in join,
// SELECT * FROM `users` INNER JOIN (SELECT `uid` FROM `oldusers`) AS `x` // ON `users`.`uid` = `x`.`uid` $query = $users->select()->join( $builder->select('uid')->from('oldusers')->alias('x'), 'uid' );
Other joins
outerJoin()
,leftJoin()
,leftOuterJoin()
,rightJoin()
,rightOuterJoin()
,fullOuterJoin()
,crossJoin()
are supported. If want to use your own join,realJoin()
is handy.// SELECT * FROM `users` OUTER JOIN `accounts` AS `a` ON `users`.`id` = `a`.`id` $query = $users->select()->outerJoin('accounts a', 'id'); // SELECT * FROM `users` NATURAL JOIN `accounts` AS `a` ON `users`.`id` = `a`.`id` $query = $users->select()->realJoin('NATURAL', 'accounts a', 'id');
-
Limit
LIMIT
andOFFSET
are supported,// SELECT * FROM `users` LIMIT 30 OFFSET 10 $query = $users->select()->limit(30, 10); // SELECT * FROM `users` LIMIT 20 OFFSET 15 $query = $users->select()->limit(20)->offset(15);
Or use
page($pageNum, $pageLength)
,// SELECT * FROM `users` LIMIT 30 OFFSET 60 $query = $users->select()->page(3, 30);
-
Order by
Order by ASC or DESC
// SELECT * FROM `users` ORDER BY `age` ASC, `score` DESC $query = $users->select()->orderByAsc('age')->orderByDesc('score');
Or raw mode
// SELECT * FROM `users` ORDER BY age ASC, score DESC $query = $users->select()->orderByRaw('age ASC, score DESC');
-
Where
Simple wheres,
// SELECT * FROM `users` WHERE age > 18 $query = $users->select()->where('age > 18'); // SELECT * FROM `users` WHERE `age` = 18 $query = $users->select()->where('age', 18); // SELECT * FROM `users` WHERE `age` < 18 $query = $users->select()->where('age', '<', 18);
Multiple wheres,
// SELECT * FROM `users` WHERE `age` > 18 AND `gender` = 'male' $query = $users->select()->where(['age' => ['>', 18], 'gender' => 'male']); // same as above $query = $users->select()->where('age', '>', 18)->where('gender','male');
Complex where,
// SELECT * FROM `users` WHERE (`id` = 1 OR (`id` < 20 OR `id` > 100)) // OR `name` = 'Tester' $query = $users->select()->where( $builder->expr()->where('id', 1)->orWhere( $builder->expr()->where('id', '<', 20)->orWhere('id', '>', 100) ) )->orWhere('name', 'Tester');
Raw mode,
// SELECT * FROM `users` WHERE age = 18 OR score > 90 $query = $users->select()->whereRaw('age = 18')->orWhereRaw('score > 90');
with
NOT
,// SELECT * FROM `users` WHERE NOT `age` = 18 OR NOT `score` > 90 $query = $users->select()->whereNot('age', 18)->orWhereNot('score', '>', 90);
Where
IN
andBETWEEN
// SELECT * FROM `users` WHERE `age` IN (10,12,15,18,20) OR `score` NOT BETWEEN 90 AND 100 $query = $users->select()->whereIn('age', [10,12,15,18,20]) ->orWhereNotBetween('score', 90, 100);
Where
IS NULL
andIS NOT NULL
// SELECT * FROM `users` WHERE `age` IS NULL OR `score` IS NOT NULL $query = $users->select()->whereNull('age')->orWhereNotNull('score');
Exists,
$qry1 = $users->select('user_id')->where('age', '>', 60); $sales = $users->table('sales'); // SELECT * FROM `sales` WHERE EXISTS (SELECT `user_id` FROM `users` // WHERE `age` > 60) $sql = $sales->select()->whereExists($qry1)->getStatement();
-
Having
Similar to
WHERE
clause,// SELECT * FROM `users` HAVING `age` = 10 OR `level` > 20 $query = $users->select()->having('age', 10)->orHaving('level', '>', 20);
-
Union
// SELECT * FROM `users` UNION SELECT * FROM `oldusers1` // UNION ALL SELECT `user_id` FROM `oldusers2` $sql = $users->select() ->union() ->select()->from('oldusers1') ->unionAll() ->select('user_id')->from('oldusers2') ->getStatement()
-
-
Single insert statement,
// INSERT INTO `users` (`uid`, `uname`) VALUES (2, 'phossa') $sql = $users->insert()->set('uid', 2)->set('uname', 'phossa') ->getStatement(); // same as above, with array notation $sql = $users->insert()->set(['uid' => 2, 'uname' => 'phossa']) ->getStatement();
Multiple data rows,
// INSERT INTO `users` (`uid`, `uname`) VALUES (2, 'phossa'), (3, 'test') $query = $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') $query = $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 = $query->getStatement(['useNullAsDefault' => true]);
Insert with
SELECT
subquery,// INSERT INTO `users` (`uid`, `uname`) // SELECT `user_id`, `user_name` FROM `oldusers` $query = $users->insert()->set(['uid', 'uname']) ->select(['user_id', 'user_name']) ->from('oldusers');
-
Common update statement,
// UPDATE `users` SET `user_name` = 'phossa' WHERE `user_id` = 3 $query = $users->update(['user_name' => 'phossa'])->where('user_id', 3); // UPDATE `users` SET `user_name` = 'phossa', `user_addr` = 'xxx' // WHERE `user_id` = 3 $query = $users->update()->set('user_name','phossa') ->set('user_addr', 'xxx')->where('user_id', 3);
With
Mysql
extensions,// UPDATE IGNORE `users` SET `user_id` = user_id + 10 ORDER BY `user_id` ASC LIMIT 10 $query = $users->update()->addHint('IGNORE')->set('user_id', $builder->raw('user_id + 10')) ->orderByASC('user_id')->limit(10);
-
Mysql version of replace,
// REPLACE LOW_PRIORITY INTO `users` (`user_id`, `user_name`) VALUES (3, 'phossa') $query = $users->replace(['user_id' => 3, 'user_name' => 'phossa']) ->addHint('low_priority');
Sqlite version of replace,
// INSERT INTO `users` (`user_id`, `user_name`) VALUES (3, 'phossa') // ON CONFLICT REPLACE $query = $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 $query = $users->delete()->where('user_id', '>', 10) ->orderByAsc('user_id')->limit(10);
Multiple tables deletion
// DELETE `users`.* FROM `users` AS `u` INNER JOIN `accounts` AS `a` // ON `u`.`user_id` = `a`.`user_id` WHERE `a`.`total_amount` < 0 $query = $users->delete('users')->from('users', 'u') ->join('accounts a', 'user_id')->where('a.total_amount', '<', 0);
-
Create table is used by most of the ORM libraries.
$builder = new Builder(); $builder->create()->table('new_table') ->temp() ->ifNotExists() ->addCol('id', 'INT') ->notNull() ->autoIncrement() ->addCol('name', 'VARCHAR(20)') ->notNull() ->unique() ->defaultValue('NONAME') ->addCol('alias', 'VARCHAR(10)') ->colConstraint('CHECK ()') ->primaryKey(['id']) ->uniqueKey(['name(4) ASC', 'alias'], 'ON CONFLICT REPLACE') ->uniqueKey(['id', 'alias'], 'ON CONFLICT ROLLBACK') ->constraint('FOREIGN KEY (...)') ->tblOption('DELAY_KEY_WRITE=1') ->tblOption('MAX_ROWS=100') ->getStatement([ 'seperator' => "\n", 'indent' => " ", ]);
With the following output,
CREATE TEMPORARY TABLE IF NOT EXISTS "new_table" ( "id" INT NOT NULL AUTO_INCREMENT, "name" VARCHAR(20) NOT NULL DEFAULT 'NONAME' UNIQUE, "alias" VARCHAR(10) CHECK (), PRIMARY KEY ("id"), UNIQUE ("name"(4) ASC, "alias") ON CONFLICT REPLACE, UNIQUE ("id", "alias") ON CONFLICT ROLLBACK, FOREIGN KEY (...) ) DELAY_KEY_WRITE=1, MAX_ROWS=100
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')) $query = $builder->select()->from('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
,$builder = $users->table(false); // 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') )->getStatement();
-
Raw string bypass the quoting and escaping,
// SELECT id FROM "students" WHERE "time" = NOW() $query = $builder->select()->field($builder->raw("id")) ->from("students")->where("time", $builder->raw('NOW()'));
Raw string with parameters,
// SELECT * FROM "students" WHERE "age" IN RANGE(1, 1.2) $query = $builder->select()->from("students")->where("age", "IN", $builder->raw('RANGE(?, ?)', 1, 1.2));
-
Sometimes, non-standard SQL wanted and no methods found.
before()
andafter()
will come to rescue.// INSERT IGNORE INTO "users" ("id", "name") VALUES (3, 'phossa') // ON DUPLICATE KEY UPDATE id=id+10 $query = $users->insert()->set('id', 3)->set('name', 'phossa') ->before('INTO', 'IGNORE') ->after('VALUES', 'ON DUPLICATE KEY UPDATE id=id+?', 10);
-
phossa-query can return statement for driver to prepare and use the
getBindings()
to get the values to bind.$query = $users->select()->where("user_id", 10); // SELECT * FROM "users" WHERE "user_id" = ? $sql = $query->getPositionedStatement(); // values to bind: [10] $val = $query->getBindings();
Or named parameters,
$query = $users->select()->where("user_name", ':name'); // SELECT * FROM "users" WHERE "user_name" = :name $sql = $query->getNamedStatement();
-
Settings can be applied to
$builder
at instantiation,$users = new Builder(new Mysql(), 'users', ['autoQuote' => false]);
Or applied when output with
getStatement()
,$sql = $users->select()->getStatement(['autoQuote' => false]);
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.
-
Dialects
Dependencies
-
PHP >= 5.4.0
-
phossa/phossa-shared 1.*