PHPackages                             phossa2/query - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. phossa2/query

ActiveLibrary[Database & ORM](/categories/database)

phossa2/query
=============

A SQL query builder library with concise syntax for PHP.

2.0.0(9y ago)211MITPHPPHP ~5.4|~7.0

Since Sep 18Pushed 9y ago2 watchersCompare

[ Source](https://github.com/phossa2/query)[ Packagist](https://packagist.org/packages/phossa2/query)[ Docs](https://github.com/phossa2/query)[ RSS](/packages/phossa2-query/feed)WikiDiscussions master Synced 2mo ago

READMEChangelog (1)Dependencies (3)Versions (2)Used By (0)

phossa2/query
=============

[](#phossa2query)

[![Build Status](https://camo.githubusercontent.com/50ffdf375c9334b486699754dab9820de17439925f9a21380ee508ed18871f12/68747470733a2f2f7472617669732d63692e6f72672f70686f737361322f71756572792e7376673f6272616e63683d6d6173746572)](https://travis-ci.org/phossa2/query)[![Code Quality](https://camo.githubusercontent.com/efe442730169ef5471849a78b699052dbe508e476af495865a182d765a8ff177/68747470733a2f2f7363727574696e697a65722d63692e636f6d2f672f70686f737361322f71756572792f6261646765732f7175616c6974792d73636f72652e706e673f623d6d6173746572)](https://scrutinizer-ci.com/g/phossa2/query/)[![Code Climate](https://camo.githubusercontent.com/fdbe935049b7a378a8b1e9d8d71ab6287703f8ff66bfaee5a4fd2e37606a1c43/68747470733a2f2f636f6465636c696d6174652e636f6d2f6769746875622f70686f737361322f71756572792f6261646765732f6770612e737667)](https://codeclimate.com/github/phossa2/query)[![PHP 7 ready](https://camo.githubusercontent.com/9f08437340dbc8d0561c9de5961285678e643b8affb85711a23608662bd962c6/687474703a2f2f7068703772656164792e74696d6573706c696e7465722e63682f70686f737361322f71756572792f6d61737465722f62616467652e737667)](https://travis-ci.org/phossa2/query)[![HHVM](https://camo.githubusercontent.com/961da34c925849f6f910d0aa5d635ee88ab785894fba1fa16d9706b04e47df55/68747470733a2f2f696d672e736869656c64732e696f2f6868766d2f70686f737361322f71756572792e7376673f7374796c653d666c6174)](http://hhvm.h4cc.de/package/phossa2/query)[![Latest Stable Version](https://camo.githubusercontent.com/1cf89c96825d7a142909d8cbd19808a4ad73ce490a21f989642cfdccbb981216/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f767072652f70686f737361322f71756572792e7376673f7374796c653d666c6174)](https://packagist.org/packages/phossa2/query)[![License](https://camo.githubusercontent.com/4fc6538ded72843e26a272d300ce4c95da083ce92576e10e4fdd505d579a8125/68747470733a2f2f696d672e736869656c64732e696f2f3a6c6963656e73652d6d69742d626c75652e737667)](http://mit-license.org/)

**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](http://www.php-fig.org/psr/psr-1/ "PSR-1: Basic Coding Standard"), [PSR-2](http://www.php-fig.org/psr/psr-2/ "PSR-2: Coding Style Guide"), [PSR-3](http://www.php-fig.org/psr/psr-3/ "PSR-3: Logger Interface"), [PSR-4](http://www.php-fig.org/psr/psr-4/ "PSR-4: Autoloader"), and the proposed [PSR-5](https://github.com/phpDocumentor/fig-standards/blob/master/proposed/phpdoc.md "PSR-5: PHPDoc").

Features
--------

[](#features)

- Support [SELECT](#select), [INSERT](#insert), [UPDATE](#update), [REPLACE](#replace), [DELETE](#delete).
- Complex sql building with [`expr()`](#expr), [`raw()`](#raw), [`before()`](#before) etc.
- Statement with positioned or named [parameters](#param).
- Beautiful output with different [settings](#settings).
- Ongoing support for different dialects like [`Mysql`](#mysql) and more.

Installation
------------

[](#installation)

Install via the `composer` utility.

```
composer require "phossa2/query"
```

or add the following lines to your `composer.json`

```
{
    "require": {
       "phossa2/query": "2.*"
    }
}
```

Usage
-----

[](#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], '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', '', 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', '' , 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', '