README

A simple, flexible, and safe way to dynamically build queries for MySQL or similar databases.

Features

Parameterize inputs by default.
Flexible to handle custom expressions not available out of the box.
Uses object instances to maintain query state, no static methods.
Works with any connection library (PDO or mysqli)

Installation

Composer

composer require zimzat/query-builder-mysql

Usage

Main Entry Points

// new Select(TableReference|string $from);
// new Insert(Table|string $into, ?Select $select = null)
// new Update(Table|string $from)
// new Delete(Table|string $from)

Output

The SqlWriter class can be used to generate the SQL string and Parameter array. It is light-weight and does not contain any state, making it safe to re-use or instantiate on demand.

use Zimzat\QueryBuilder\Select;
use Zimzat\QueryBuilder\SqlWriter;

$writer = new SqlWriter();
[$sql, $parameters] = $writer->write(new Select('SomeTable'));

// [string $sql, array $parameters] = (new SqlWriter())->write(Sql $query)

SELECT

/** @see SelectTest */
/** @see ReadmeTest::testSelect() */

use Zimzat\QueryBuilder\Select;

$select = new Select('SomeTable');
// SELECT * FROM SomeTable
$select->columns()
    ->add($select('id'))
    ->add($select('*'));
// SELECT SomeTable.id, SomeTable.* FROM SomeTable

$otherTable = $select->join('OtherTable', 'someTableId', $select('id'));
// INNER JOIN OtherTable ON (OtherTable.someTableId = SomeTable.id)

$thirdTable = $select->leftJoin('ThirdTable', 'id', $select('thirdTableId'));
$thirdTable->on()
    ->notEqual($thirdTable('field1'), $select('field2'));
// LEFT JOIN ThirdTable ON (ThirdTable.id = SomeTable.thirdTableId AND ThirdTable.field1 != SomeTable.field2)

// WHERE ()
$select->where()
    ->equal($select('someField'), 4)
    ->in($select('type'), ['a', 'b']);
// SomeTable.someField = ? AND SomeTable.type IN (?, ?)

$select->where()
    ->some()
        ->isNull($otherTable('nullableValue'))
        ->lessThanOrEqual($otherTable('nullableValue'), 10);
// AND (OtherTable.nullableValue IS NULL OR OtherTable.nullableValue <= ?)

$select->where()
    ->condition('? <=> ?', $select('f'), $otherTable('y'));
// AND (SomeTable.f <=> OtherTable.y)

// GROUP BY
$select->groupBy()
    ->add($select('id'));
// SomeTable.id ASC

$select->limit(10);
// LIMIT ?

SELECT queries can also be used as part of other statements as a Sub-Query or INSERT ... SELECT.

UNION

/** @see UnionTest */
/** @see ReadmeTest::testUnion() */

use Zimzat\QueryBuilder\Select;
use Zimzat\QueryBuilder\Union;

$select1 = new Select('TableA');
$select1->columns()->add($select1('id'));
$select1->limit(5);
// SELECT TableA.id FROM TableA LIMIT ?
// [5]

$select2 = new Select('TableB');
$select2->columns()->add($select2('id'));
$select2->limit(5);
// SELECT TableB.id FROM TableB LIMIT ?
// [5]

$union = new Union();
$union
    ->unionAll($select1)
    ->unionAll($select2)
    ->limit(10);
// (SELECT TableA.id FROM TableA LIMIT ?)
// UNION ALL
// (SELECT TableB.id FROM TableB LIMIT ?)
// LIMIT ?
// [5, 5, 10]

Sub-Query

/** @see SelectTest::testSubquery() */
/** @see ReadmeTest::testSubQuery() */

use Zimzat\QueryBuilder\Select;

$subselect = new Select('TableB');
$subselect->columns()
    ->add($subselect('id'));
$subselect->where()
    ->equal($subselect('f'), 'a');
$subselect->limit(10);

$select = new Select($subselect->asSubQuery('SubB'));
$tableF = $select->join('TableF', 'id', $select('id'));

// SELECT * FROM (SELECT TableB WHERE TableB.f = ? LIMIT 10) AS SubB JOIN TableF ON (TableF.id = SubB.id

UPDATE

/** @see UpdateTest */
/** @see ReadmeTest::testUpdate() */

use Zimzat\QueryBuilder\Update;

$update = new Update('TableU');
// UPDATE TableU

$update->set()
    ->equal($update('a'), 4);
// SET TableU.a = ?

// WHERE ()
$update->where()
    ->between($update('n'), 3, 5);
// TableU.n BETWEEN (?, ?)

INSERT

The primary use-case for Insert is in conjunction with Select. It does not support multiple rows.

/** @see InsertTest */
/** @see ReadmeTest::testDelete() */

DELETE

/** @see DeleteTest */
/** @see ReadmeTest::testDelete() */

use Zimzat\QueryBuilder\Delete;

$delete = new Delete('SomeTable');
$delete->where()
    ->expr('? = FLOOR(?)', $delete('f'), M_PI);
// DELETE SomeTable FROM SomeTable WHERE SomeTable.f = FLOOR(?)
// [3.141592653589793]

Extensions

Any class implementing the Sql interface can be used in several places.

use Zimzat\QueryBuilder\Sql;
use Zimzat\QueryBuilder\SqlQueryValue;
use Zimzat\QueryBuilder\SqlWriter;

// Normally this would be a standard class declaration, for demonstration purposes this uses `new class` instead
$x = new class implements Sql {
    public function compileSqlQueryValue() : SqlQueryValue
    {
        return new SqlQueryValue('CUSTOM QUERY PART WITH ? PLACEHOLDER', ['123']);
    }
};

(new SqlWriter())->write($x);
// ['CUSTOM QUERY PART WITH ? PLACEHOLDER', ['123']]

Extensions of the Condition or Expr class can be created to handle repeat expressions.

/** @see ReadmeTest::testExtendCondition */

use Zimzat\QueryBuilder\Condition;
use Zimzat\QueryBuilder\Field;
use Zimzat\QueryBuilder\Select;

$select = new Select('SomeTable');

// Normally this would be a standard class declaration, for demonstration purposes this uses `new class` instead
$equalOrNull = new class ($select('x'), 5) extends Condition {
    public function __construct(Field $field, mixed $value)
    {
        parent::__construct('(? IS NULL OR ? = ?)', $field, $field, $value);
    }
};

$select->where()
    ->equal($select('type'), 99)
    ->add($equalOrNull);

$select->where()
    ->equal($select('type'), 99)
    ->add($equalOrNull);
// WHERE (
//     SomeTable.type = ?
//     AND (SomeTable.x IS NULL OR SomeTable.x = ?)
// )

Alternatively, you can create your own wrapper to return a new stance of Condition to the same effect:

use Zimzat\QueryBuilder\Condition;
use Zimzat\QueryBuilder\Field;

public function equalOrNull(Field $field, mixed $value): Condition
{
    return new Condition('(? IS NULL OR ? = ?)', $field, $field, $value);
}

Library Comparisons

See: Query Builder Library Comparison

zimzat / query-builder-mysql

Maintainers

Details