README

QB 是一个通用的查询构建器，目前支持 MySQL 和 PostgreSQL 的基本命令。

• QB 旨在支持超过 95% 的用例，而不是 100%
• 对于 PostgreSQL 的支持还有一点要改进，MySQL 应该已经具备。
• 欢迎为支持其他数据库和命令的拉取请求。
• 编写此工具是因为大多数其他项目不支持在跨多个连接的表上进行表连接。（多对多支持）

示例

MySQL 使用联合的 SELECT

use QB\Generic\Clause\Column;
use QB\Generic\Clause\QueryAsTable;
use QB\Generic\Clause\Table;
use QB\Generic\Expr\Expr;
use QB\MySQL\Clause\CombiningQuery;
use QB\MySQL\Clause\Lock;
use QB\MySQL\Statement\Select;

$columnQuery = (new Select())
    ->from('quix')
    ->columns('b')
    ->where(new Expr('id = ?', [7]));

$columnExpr = new Expr('NOW()');

$joinQuery = (new Select())->from(new Table('quix', 'q2'))->where('q2.foo_id = foo.id');

$unionQuery = (new Select('b', 'f'))->from('baz');

$sql = (string)(new Select('COUNT(DISTINCT baz) AS baz_count', new Column($columnQuery, 'quix_b')))
    ->from('foo', 'bar')
    ->modifier('DISTINCT')
    ->columns(new Column($columnExpr, 'now'))
    ->columns(new Column('bar.id', 'bar_id'))
    ->innerJoin(new Table('quix', 'q'), 'foo.id = q.foo_id')
    ->innerJoin(new QueryAsTable($joinQuery, 'q2'))
    ->where('foo.bar = "foo-bar"', new Expr('bar.foo = ?', ['bar-foo']))
    ->where(new Expr('bar.foo IN (?)', [['bar', 'foo']]))
    ->groupBy('q.foo_id', new Expr('q.bar.id'))
    ->groupWithRollup()
    ->having('baz_count > 0')
    ->orderBy('baz_count', 'ASC')
    ->limit(10)
    ->offset(20)
    ->lock(new Lock(Lock::FOR_UPDATE, ['foo'], Lock::MODIFIER_NOWAIT))
    ->union($unionQuery, CombiningQuery::MODIFIER_DISTINCT);

// SELECT DISTINCT COUNT(DISTINCT baz) AS baz_count, (SELECT b FROM quix WHERE id = ?) AS quix_b, NOW() AS now, bar.id AS bar_id
// FROM foo, bar
// INNER JOIN quix AS q ON foo.id = q.foo_id
// INNER JOIN (SELECT * FROM quix AS q2 WHERE foo.id = q2.foo_id) AS q2
// WHERE foo.bar = "foo-bar" AND bar.foo = ? AND bar.foo IN (?, ?)
// GROUP BY q.foo_id, q.bar.id WITH ROLLUP
// HAVING baz_count > 0
// ORDER BY baz_count ASC
// LIMIT 20, 10
// FOR UPDATE OF foo NOWAIT
// UNION DISTINCT
// SELECT b, f
// FROM baz

PostgreSQL 使用 UPDATE ON CONFLICT AND RETURNING 的 INSERT

use QB\Generic\Clause\Table;
use QB\PostgreSQL\Statement\Insert;

$sql = (string)(new Insert())
    ->into(new Table('offices'))
    ->columns('officeCode', 'city', 'phone', 'addressLine1', 'country', 'postalCode', 'territory')
    ->values('abc', 'Berlin', '+49 101 123 4567', '', 'Germany', '10111', 'NA')
    ->values('bcd', 'Budapest', '+36 70 101 1234', '', 'Hungary', '1011', 'NA')
    ->onConflict('officeCode', 'city')
    ->doUpdate('officeCode = EXCLUDED.officeCode', 'city = EXCLUDED.city')
    ->returning('*');
    
// INSERT INTO offices (officeCode, city, phone, addressLine1, country, postalCode, territory)
// VALUES (?, ?, ?, ?, ?, ?, ?),
// (?, ?, ?, ?, ?, ?, ?)
// ON CONFLICT (officeCode, city) DO UPDATE
// SET officeCode = EXCLUDED.officeCode, city = EXCLUDED.city
// RETURNING *

自定义命令

use QB\Generic\Statement\Select;
use QB\Generic\Statement\Command;

$select = (new Select())
    ->from('quix')
    ->columns('b');

$sql = (string)(new Command('EXPLAIN %s', $select));
    
// EXPLAIN SELECT b FROM quix