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

SELECT

Columns

Columns can be specified in the select() or in col() (or with its alias field()).

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() and sumDistinct() 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 with distinct()

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

// 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 and IS 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()

INSERT

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

UPDATE

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

REPLACE

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

DELETE

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

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

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

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

before() and after()

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

Parameters

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

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 to null. Function used to quote and escape values.
- useNullAsDefault: boolean.

Dialects

Mysql

Dependencies

PHP >= 5.4.0
phossa/phossa-shared 1.*

License

MIT License

phossa / phossa-query

Maintainers

Details