README

一个用于构建和格式化复杂SQL查询的PHP库。已在PHP 7.1和PHP 7.4上测试。

示例

$builder = new QueryBuilder();
$select = $builder->select(table("users", "u"))->distinct()->setColumns([
        "id", 
        "registered" => "registrationDate",
        "fullName" => func("CONCAT", "name", " ", "surname"),
        "avatar" => "up.image",
        "status" => func("IF", greater("premiumExpireDate", func("NOW")), literal("VIP"), literal("peasant")),
        "isBanned" => isNotNull("ub.id"),
        "totalPosts" => select("posts", [
                count_()
            ])->where()->eq("u.id", "posts.userId")->end()
    ])->join("user_pictures as up", Join::INNER)->eq("up.id", "u.id")->end()
    ->join("user_banlist as ub", Join::LEFT)->eq("ub.id", "u.id")->end()
    ->where()->true("enabled")->end()
    ->orderBy("totalPosts", OrderBy::DESC)->orderBy("fullName")->limit(10)->offset(1);

$formatter = new MysqlFormatter();
echo $formatter->format($select, $parameters);
//$parameters -> ["v1" => " ", "v2" => "VIP", "v3" => "peasant"]

结果

SELECT DISTINCT
    `id`,
    `registrationDate` AS `registered`,
    CONCAT(`name`, :v1, `surname`) AS `fullName`,
    `up`.`image` AS `avatar`,
    IF(`premiumExpireDate` > NOW(), :v2, :v3) AS `status`,
    (`ub`.`id` IS NOT NULL) AS `isBanned`,
    (SELECT
        COUNT(*)
    FROM `posts`
    WHERE
        `u`.`id` = `posts`.`userId`) AS `totalPosts`
FROM `users` AS `u`
INNER JOIN `user_pictures` AS `up` ON (`up`.`id` = `u`.`id`)
LEFT JOIN `user_banlist` AS `ub` ON (`ub`.`id` = `u`.`id`)
WHERE
    `enabled`
ORDER BY `totalPosts` DESC, `fullName` ASC
LIMIT 10 OFFSET 1;

1. 安装 ↑

安装此库的推荐方法是使用Composer。运行以下命令进行安装：composer require dmitryproa/php-advanced-querying

2. 语法 ↑

2.1 构建器 ↑

类QueryBuilder提供了以下方法来构建语句

->select($table = null, $columns = []) //SELECT statement
->update($table = null, $values = []) //UPDATE statement
->insert($table = null, $fields = [], $values = []) //INSERT INTO... VALUES statement
->replace($table = null, $fields = [], $values = []) //REPLACE INTO... VALUES statement
->insertSelect($table = null, $select = null) //INSERT INTO... SELECT statement
->replaceSelect($table = null, $select = null) //REPLACE INTO... SELECT statement

2.2 表 ↑

Table指定如下

table($name) //-> `$name`
table($name, $alias) //-> `$name` as `$alias`
"name" => //-> same as table("name")
"name as alias" => //-> same as table("name", "alias"), case-insensitive

2.3 选择列 ↑

选择列是别名（可选）和表达式的对，定义为关联数组

["alias" => $expr, $expr2, ...]

2.4 更新值 ↑

更新值是列和表达式的对，定义方式与列相同

["column" => $expr, "table.column" => $expr2, ...]

2.5 插入字段 ↑

插入字段是纯字符串

["field1", "field2"]

2.6 插入值 ↑

一维或二维的文字数组

[1, "string", null]
[
    [1, "a"],
    [2, "b"]
]

2.7 表达式 ↑

2.7.1 列表达式 ↑

column($name) //-> `$column`
column($name, $table) => //-> `$table`.`$column`
"name" //-> same as column("name")
"table.name" //-> same as column("name", "table")

2.7.2 文字表达式 ↑

literal($value) //will be translated to the PDO parameter (:v1, :v2 etc.)
123 //same as literal(123)
null //same as literal(null)
"," //same as literal(","), if not matches the column format

2.7.3 选择表达式 ↑

select($table = null, $columns = []) //-> (SELECT ...)
select()->setTable(...)->setColumns(...)->where(...)

2.7.4 函数表达式 ↑

func($name, ...$args)//-> $name($arg1, $arg2, ...)
func("CONCAT", "column1", ":", "table.column2") //-> CONCAT(`column1`, :v1, `table`.`column2`)

有一些预定义的函数

count_($distinct = false, ...$columns) //COUNT() function
count_() //-> COUNT(*)
count_(false, "column1", "column2") //-> COUNT(`column1`, `column2`)
count_(true, "column1", "column2") //-> COUNT(DISTINCT `column1`, `column2`)

groupconcat($expression, $distinct = false, $separator = ",") //GROUP_CONCAT() function
groupconcat("column") //-> GROUP_CONCAT(`column`)
groupconcat("column", true, ";") //-> GROUP_CONCAT(DISTINCT `column` SEPARATOR :v1)

cast($expression, $type) //CAST($expression AS $type)
cast("column", CastExpression::SIGNED) //-> CAST(`column` AS SIGNED)

over($function, $partitionExpr = null) //$function OVER ([PARTITION BY $partitionExpr])
over(...)->orderBy($expr, $direction = OrderBy::ASC)->orderBy(...) //$function OVER (... ORDER BY $expr, ...)
over("row_number") //-> ROW_NUMBER() OVER()
over("row_number", "column")->orderBy("orderColumn", OrderBy::DESC) //-> ROW_NUMBER() OVER (PARTITION BY `column` ORDER BY `orderColumn` DESC)
over(func("first_value", "valueColumn"), "column") //-> FIRST_VALUE(`valueColumn`) OVER (PARTITION BY `column`)

2.8 语句 ↑

每个语句都有一个setTable($table)方法。

2.8.1 条件（WHERE）语句 ↑

一些语句（如SELECT、UPDATE和DELETE）可以指定WHERE条件

$statement->where()->...conditions...->end()

->true($expr) //-> $expr
->false($expr) //-> NOT $expr

->eq($expr1, $expr2) //-> $expr1 = $expr2
->notEq($expr1, $expr2) //-> $expr1 != $expr2
->greater($expr1, $expr2) //-> $expr1 > $expr2
->greaterEquals($expr1, $expr2) //-> $expr1 >= $expr2
->less($expr1, $expr2) //-> $expr1 < $expr2
->lessEquals($expr1, $expr2) //-> $expr1 <= $expr2
->like($expr1, $expr2) //-> $expr1 LIKE $expr2
->notLike($expr1, $expr2) //-> $expr1 NOT LIKE $expr2

//!!! $expr2 is treated as literal, unless Expression is passed


->isNull($expr) //-> $expr IS NULL
->isNotNull($expr) //-> $expr IS NOT NULL
->in($expr, ...$literals) //-> $expr IN ($literal1, $literal2, ...)
->notIn($expr, ...$literals) //-> $expr NOT IN ($literal1, $literal2, ...)
->and(...$conditions) //-> $condition1 AND $condition2 AND...
->or(...$conditions) //-> ($condition1 OR $condition2 OR...)

条件也可以使用辅助函数定义：true()、false()、eq()、notEq()、greater()、greaterEquals()、less()、lessEquals()、like()、notLike()、isNull()、isNotNull()、in()、notIn()、and_()和or_()。例如

$statement()->where()->or(
        eq("column1", "column2"), 
        isNull("column3"), 
        and_(true("column4"), in("column5", 1, 2)))
    ->end();

条件可以用作表达式

$select->setColumn(func("IF", greater("column1", "column2"), "column1", null)); // -> SELECT IF(`column1` > `column2`, `column`, NULL) ...

2.8.2 JOIN语句 ↑

Select和Update语句都有一个join()方法

$statement
    ->join($table, $joinType = Join::OUTER)->...conditions...->end()
    ->join(select(...))
    ->join(table(select(...), $joinTableAlias))
    ->join...

可用的JOIN类型：Join::OUTER、Join::INNER、Join::LEFT、Join::RIGHT。

2.8.3 SELECT 语句 ↑

Select 语句有以下方法

->setColumn($expr, $alias = '')
->setColumns($columns)
->orderBy($expr, $direction = OrderBy::ASC) // avaliable directions: OrderBy::ASC, OrderBy::DESC
->limit($count)
->offset($amount)

Select 语句可以使用另一个 Select 作为表，例如

$inner = $builder->select("table", ["type", "count" => count_()])->groupBy("type");
$select = $builder->select($select)->orderBy("type");
// -> SELECT * FROM (SELECT `type`, COUNT(*) as `count` FROM `table` GROUP BY `type`) ORDER BY `type`;
$select = $builder->select(table($select, "selectAlias")); // -> SELECT * FROM (SELECT ...) as `selectAlias`

UNION SELECT 语句可以通过调用 unionSelect() 函数来创建，该函数返回一个新的 SELECT。例如

$select->unionSelect("anotherTable", ["column"], true); //-> SELECT ... UNION ALL SELECT `column` FROM `anotherTable`;
$builder->select(null, ["id" => 123])
    ->unionSelect(null, [456])
    ->unionSelect(null, [589]);
//-> SELECT :v1 as `id` UNION SELECT :v2 UNION SELECT :v3;

2.8.4 UPDATE 语句 ↑

Update 语句有以下方法

->setValue($field, $value)
->setValues($values)

2.8.5 INSERT 和 REPLACE 语句 ↑

这些语句有以下方法

->setFields($fields) // -> INSERT INTO ($field1, $field2, ...)
->setValues($values) // -> INSERT INTO ... VALUES (...)

INSERT 语句也有以下方法

->ignore() // -> INSERT IGNORE...
->onDuplicateKeyUpdate($updateValues) // -> INSERT INTO... ON DUPLICATE KEY UPDATE $field1 => $value1, $field2 => $value2...

2.8.6 INSERT...SELECT 和 REPLACE...SELECT 语句 ↑