phossa2/query

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

Support SELECT, INSERT, UPDATE, REPLACE, DELETE.
Complex sql building with expr(), raw(), before() etc.
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());

SELECT

Columns/fields

Columns can be specified in the select($col, ...), col($col, $alias) or col(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 with distinct(...),

// 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) or table($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() or table(),

// 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() and groupRaw($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 and OFFSET 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 from 1,

// 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 and BETWEEN

// 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() or unionAll() 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();

INSERT

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');

UPDATE

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) and decrement($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);

REPLACE

Mysql version of replace,

// REPLACE INTO `Users` (`user_id`, `user_name`) VALUES (3, 'phossa')
$qry = $users->replace(['user_id' => 3, 'user_name' => 'phossa']);

DELETE

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

expr()

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()

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() and option()

Sometimes, non-standard SQL wanted and no methods found. before() and after() 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, and option() 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]);

Parameters

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

Settings can be applied to $builder during instantiation or using setSettings(),
```
// builder instantiation
$users = new Builder('Users', new Mysql(), ['autoQuote' => false]);

// adjust settings
$users->setSettings(['autoQuote' => true]);
```
Or applied when output with getSql() or getStatement(),
```
$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 to null. 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

License

MIT License