PHPackages                             dmitryproa/php-advanced-querying - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. dmitryproa/php-advanced-querying

ActiveLibrary[Database & ORM](/categories/database)

dmitryproa/php-advanced-querying
================================

A PHP library for building and formatting complex SQL queries

0.6.6(1y ago)017MITPHPPHP >=7.1.0

Since Jul 18Pushed 1y ago1 watchersCompare

[ Source](https://github.com/dmitryproa/php-advanced-querying)[ Packagist](https://packagist.org/packages/dmitryproa/php-advanced-querying)[ RSS](/packages/dmitryproa-php-advanced-querying/feed)WikiDiscussions main Synced today

READMEChangelogDependenciesVersions (9)Used By (0)

PHP Advanced Querying
=====================

[](#php-advanced-querying)

A PHP library for building and formatting complex SQL queries. Tested on PHP 7.1 and PHP 7.4.

Example
-------

[](#example)

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

Result:

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

Table of contents
=================

[](#table-of-contents)

- [1. Installation](#block1)
- [2. Syntax](#block2)
    - [2.1 Builder](#block2.1)
    - [2.2 Table](#block2.2)
    - [2.3 Select columns](#block2.3)
    - [2.4 Update values](#block2.4)
    - [2.5 Insert fields](#block2.5)
    - [2.6 Insert values](#block2.6)
    - [2.7 Expressions](#block2.7)
        - [2.7.1 Column expression](#block2.7.1)
        - [2.7.2 Literal expression](#block2.7.2)
        - [2.7.3 Select expression](#block2.7.3)
        - [2.7.4 Function expression](#block2.7.4)
        - [2.7.5 Raw expression](#block2.7.5)
    - [2.8 Statements](#block2.8)
        - [2.8.1 Conditional (WHERE) statements](#block2.8.1)
        - [2.8.2 JOIN statements](#block2.8.2)
        - [2.8.3 SELECT statement](#block2.8.3)
        - [2.8.4 UPDATE statement](#block2.8.4)
        - [2.8.5 INSERT and REPLACE statements](#block2.8.5)
        - [2.8.6 INSERT... SELECT and REPLACE... SELECT statements](#block2.8.6)

1. Installation [↑](#index_block)
=================================

[](#1-installation-)

The recommended way to install this library is through Composer. Run the following command to install it: `composer require dmitryproa/php-advanced-querying`

2. Syntax [↑](#index_block)
===========================

[](#2-syntax-)

2.1 Builder [↑](#index_block)
-----------------------------

[](#21-builder-)

Class `QueryBuilder` provides the following methods for building a statement:

```
->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 [↑](#index_block)
---------------------------

[](#22-table-)

`Table` specified as follows:

```
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 Select columns [↑](#index_block)
------------------------------------

[](#23-select-columns-)

Select columns are pairs of alias (optional) and expression, and defined as associative array:

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

2.4 Update values [↑](#index_block)
-----------------------------------

[](#24-update-values-)

Update values are pairs of a column and an expression, and defined the same way as columns:

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

2.5 Insert fields [↑](#index_block)
-----------------------------------

[](#25-insert-fields-)

Insert fields are plain strings:

```
["field1", "field2"]
```

2.6 Insert values [↑](#index_block)
-----------------------------------

[](#26-insert-values-)

One- or two-dimensional array of literals:

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

2.7 Expressions [↑](#index_block)
---------------------------------

[](#27-expressions-)

### 2.7.1 Column expression [↑](#index_block)

[](#271-column-expression-)

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

### 2.7.2 Literal expression [↑](#index_block)

[](#272-literal-expression-)

```
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 expression [↑](#index_block)

[](#273-select-expression-)

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

### 2.7.4 Function expression [↑](#index_block)

[](#274-function-expression-)

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

There are several pre-defined functions:

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

### 2.7.5 Raw Expression

[](#275-raw-expression)

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

2.8 Statements [↑](#index_block)
--------------------------------

[](#28-statements-)

Every statement have a `setTable($table)` method.

### 2.8.1 Conditional (WHERE) statements [↑](#index_block)

[](#281-conditional-where-statements-)

Some statements (such as SELECT, UPDATE and DELETE) can specify WHERE conditions:

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

Conditions can also be defined using helper functions: `true()`, `false()`, `eq()`, `notEq()`, `greater()`, `greaterEquals()`, `less()`, `lessEquals()`, `like()`, `notLike()`, `isNull()`, `isNotNull()`, `in()`, `notIn()`, `and_()` and `or_()`. For example:

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

Conditions can be used as expressions:

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

### 2.8.2 JOIN statements [↑](#index_block)

[](#282-join-statements-)

`Select` and `Update` statements have a `join()` method:

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

Available join types: `Join::OUTER`, `Join::INNER`, `Join::LEFT`, `Join::RIGHT`.

### 2.8.3 SELECT statement [↑](#index_block)

[](#283-select-statement-)

`Select` statement have following methods:

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

`Select` statement can use another `Select` as table, for example:

```
$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` statement can be made by calling an `unionSelect()` functions, which returns a new `SELECT`. For example:

```
$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 statement [↑](#index_block)

[](#284-update-statement-)

`Update` statement have the following methods:

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

### 2.8.5 INSERT and REPLACE statements [↑](#index_block)

[](#285-insert-and-replace-statements-)

These statements have the following methods:

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

`INSERT` statement also have the following methods:

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

### 2.8.6 INSERT... SELECT and REPLACE... SELECT statements [↑](#index_block)

[](#286-insert-select-and-replace-select-statements-)

These statements have the following methods:

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

`InsertSelect` statement also have `ignore()` and `onDuplicateKeyUpdate()` methods.

###  Health Score

22

—

LowBetter than 21% of packages

Maintenance41

Moderate activity, may be stable

Popularity6

Limited adoption so far

Community7

Small or concentrated contributor base

Maturity30

Early-stage or recently created project

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~31 days

Recently: every ~53 days

Total

8

Last Release

492d ago

### Community

Maintainers

![](https://avatars.githubusercontent.com/u/48441095?v=4)[Dmitry Promokhov](/maintainers/dmitryproa)[@dmitryproa](https://github.com/dmitryproa)

---

Top Contributors

[![dmitryproa](https://avatars.githubusercontent.com/u/48441095?v=4)](https://github.com/dmitryproa "dmitryproa (19 commits)")

### Embed Badge

![Health badge](/badges/dmitryproa-php-advanced-querying/health.svg)

```
[![Health](https://phpackages.com/badges/dmitryproa-php-advanced-querying/health.svg)](https://phpackages.com/packages/dmitryproa-php-advanced-querying)
```

###  Alternatives

[jdorn/sql-formatter

a PHP SQL highlighting library

3.9k117.2M118](/packages/jdorn-sql-formatter)[propel/propel1

Propel is an open-source Object-Relational Mapping (ORM) for PHP5.

8351.6M87](/packages/propel-propel1)[jfelder/oracledb

Oracle DB driver for Laravel

11518.4k](/packages/jfelder-oracledb)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)