README

Query Builder is a fast compiler for MySQL compatible queries written in PHP 5.4+. A syntax is more declarative and a simple than in another known libraries. The compiler is a micro framework that useful for development in PHP only without writing pure SQL.

use Repo2\QueryBuilder;

// build a query
$query = QueryBuilder\select('user', [
    'id',
    QueryBuilder\alias('timezone', 'tz')
])
->where(
    QueryBuilder\greater('age', 21),
    QueryBuilder\orWhere(
        QueryBuilder\equal('city', 'New York'),
        QueryBuilder\isNotNull('country')
    )
)
->orderBy(
    QueryBuilder\orderByDesc('created_at'),
    QueryBuilder\orderByAsc('age')
)
->limit(20);

// compile & run sql
$conn = new \mysqli('localhost', 'root', '', 'test');
$sql = $query->compile(new Driver\Mysqli($conn));
$conn->query($sql);

The query will be compiled into:

SELECT `id`, `timezone` AS `tz`
FROM `user`
WHERE (`age` > "21" AND (`city` = "New York" OR `country` IS NOT NULL))
ORDER BY `created_at` DESC, `age` ASC
LIMIT 20

The heart of the framework is extensibility. You can create own query. All you need is implement ExpressionInterface.

Table of contents

1. Installation
2. DDL
   • create
   • drop
3. Query
   • insert
   • select
   • update
   • delete
4. Restrictions

Installation

Install it with Composer:

{
    "require": {
        "repo2/query-builder": "*"
    }
}

DDL

DDL wrappers are located in Repo2\QueryBuilder\DDL namespace.

create

function DDL\create(string $table, array $columns)

returns \Repo2\QueryBuilder\DDL\CreateTable.

Example

The code:

use Repo2\QueryBuilder\DDL;

$query = DDL\create('user', [
    DDL\column('id')->integer()->primary(),
    DDL\column('name')->varchar(50)->required(),
    DDL\column('timezone')->varchar(100)->defval('UTC')
]);

will be compiled into:

CREATE TABLE `user`(
    `id` INTEGER PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `timezone` VARCHAR(100) DEFAULT "UTC"
)

drop

function DDL\drop(string $table)

returns \Repo2\QueryBuilder\DDL\DropTable.

Example

The code:

use Repo2\QueryBuilder\DDL;

$query = DDL\drop('user');

will be compiled into:

DROP TABLE `user`

Query

CRUD wrappers are located in Repo2\QueryBuilder namespace.

insert

function QueryBuilder\insert(string $table, array $firstRow = [])

returns \Repo2\QueryBuilder\Query\InsertQuery.

Example

The code:

use Repo2\QueryBuilder;

$query = QueryBuilder\insert('something', ['foo' => 1, 'bar' => 2]);

will be compiled into:

INSERT INTO `something`(`foo`, `bar`) VALUES ("1", "2")

You can create multi insert statement also. The code:

use Repo2\QueryBuilder;

$query = QueryBuilder\insert('something')
    ->pipe(['foo' => 1, 'bar' => 2])
    ->pipe(['foo' => 3, 'bar' => 4]);

will be compiled into:

INSERT INTO `something`(`foo`, `bar`) VALUES ("1", "2"), ("3", "4")

select

function QueryBuilder\select(string $table, array $columns)

returns \Repo2\QueryBuilder\Query\SelectQuery.

The SelectQuery inherits FilterQuery. See filter.

Example

The code:

use Repo2\QueryBuilder;

$query = QueryBuilder\select('something', ['foo']);

will be compiled into:

SELECT `foo` FROM `something`

You can create aliases in columns list. The code:

use Repo2\QueryBuilder;

$query = QueryBuilder\select(
    'something',
    [
       'foo',
       QueryBuilder\alias('timezone', 'tz')
    ]
);

will be compiled into:

SELECT `foo`, `timezone` AS `tz` FROM `something`

update

function QueryBuilder\update(string $table, array $values)

returns \Repo2\QueryBuilder\Query\UpdateQuery.

The UpdateQuery inherits FilterQuery. See filter.

Example

The code:

use Repo2\QueryBuilder;

$query = QueryBuilder\update('something', ['foo' => 1, 'bar' => 'baz']);

will be compiled into:

UPDATE `something` SET `foo` = "1", `bar` = "baz"

You can use values incrementations for update. The code:

use Repo2\QueryBuilder;

$query = QueryBuilder\update(
    'something',
    [
        QueryBuilder\inc('foo', 10),
        QueryBuilder\inc('bar', -10)
    ]
);

will be compiled into:

UPDATE `something` SET `foo` = `foo` + 10, `bar` = `bar` - 10

delete

function QueryBuilder\delete(string $table)

returns \Repo2\QueryBuilder\Query\DeleteQuery.

The DeleteQuery inherits FilterQuery. See filter.

Example

The code:

use Repo2\QueryBuilder;

$query = QueryBuilder\delete('something');

will be compiled into:

DELETE FROM `something`

filter

The abstract class FilterQuery implements data filtering, sorting and slicing.

where

FilterQuery::where(ExpressionInterface ...$conditions)

You can use expressions for the method:

• equal
• notEqual
• greater
• greaterOrEqual
• less
• lessOrEqual
• isNull
• isNotNull
• inArray
• notInArray
• orWhere
• andWhere

orderBy

FilterQuery::orderBy(ExpressionInterface ...$conditions)

You can use expressions for the method:

• orderByAsc
• orderByDesc

limit

FilterQuery::limit(int $limit, int $offset = 0)

Restrictions

The framework has some restrictions:

• No prepared statements.
• No joins.
• No subqueries.
• The query SELECT * FROM is not allowed.
• mysqli driver implementation only.

Source: https://github.com/Repo2/query-builder