PHP code example of dmitryproa / php-advanced-querying

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

dmitryproa / php-advanced-querying example snippets

$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($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

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

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

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

["field1", "field2"]

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

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

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

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

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)
groupconcat("column")->orderBy("orderColumn", OrderBy::DESC) //-> GROUP_CONCAT(`column` ORDER BY `orderColumn` DESC)

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`)

raw("BETWEEN", literal(1), "AND", 4) //-> BETWEEN :v1 AND 4
raw("json->`field`::bool = true") //-> json->`field`::bool = true

$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...)

$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) ...

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

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

$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`

$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;

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

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

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

->setFields($fields)
->setSelect($select)