repo2/query-builder

Query Builder is a fast compiler for MySQL compatible queries written in PHP 5.4+.

v1.3 2015-01-22 10:45 UTC

README

Build Status Latest Stable Version Total Downloads License Scrutinizer Code Quality Code Coverage

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
  3. Query
  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