PHPackages                             atk14/sql-builder - 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. atk14/sql-builder

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

atk14/sql-builder
=================

Library for building SQL queries

v4.4.5(3w ago)018.1k↓35.3%1MITPHPPHP >=7.1.0CI passing

Since May 13Pushed 3w ago3 watchersCompare

[ Source](https://github.com/atk14/SqlBuilder)[ Packagist](https://packagist.org/packages/atk14/sql-builder)[ Docs](https://github.com/atk14/SqlBuilder)[ RSS](/packages/atk14-sql-builder/feed)WikiDiscussions master Synced 2w ago

READMEChangelogDependencies (4)Versions (36)Used By (1)

SqlBuilder
==========

[](#sqlbuilder)

[![Tests](https://github.com/atk14/SqlBuilder/actions/workflows/tests.yml/badge.svg)](https://github.com/atk14/SqlBuilder/actions/workflows/tests.yml)

PHP library for building parameterized SQL queries programmatically. Designed for PostgreSQL and integrates with [dbmole](https://github.com/atk14/DbMole).

Usage
-----

[](#usage)

### Basic query

[](#basic-query)

```
use SqlBuilder\SqlTable;

$sql = new SqlTable('cards');
$sql->where('visible = :visible', ':visible', true);
$sql->where('title LIKE :q')->bind(':q', '%Soap%');

$result = $sql->result();
// SELECT * FROM cards WHERE (visible = :visible) AND (title LIKE :q)

$dbmole->selectRows($result->select('id, title'), $result->bind);
$dbmole->selectInt($result->count());
```

`result()` returns a `SqlResult` object with the following query methods, each returning a `BindedSql`:

MethodSQL produced`select($fields = '*')``SELECT … FROM …``count($field = '*')``SELECT COUNT(…) FROM …``exists()``SELECT EXISTS(SELECT … FROM …)``distinctOnSelect($field, $distinct_on)``SELECT DISTINCT ON (…) …`### Named where conditions

[](#named-where-conditions)

Named conditions can be toggled or negated when building the result:

```
$sql = new SqlTable('cards');
$sql->where('deleted = false');                          // anonymous — always included
$sql->namedWhere('visible', 'visible = :visible')->bind(':visible', true);

$sql->result()->select();
// SELECT * FROM cards WHERE (deleted = false) AND (visible = :visible)

$sql->result(['disable_where' => 'visible'])->select();
// SELECT * FROM cards WHERE (deleted = false)

$sql->result(['not_where' => 'visible'])->select();
// SELECT * FROM cards WHERE (deleted = false) AND (NOT (visible = :visible))

$sql->result(['named_where_only' => true])->select();
// SELECT * FROM cards WHERE (visible = :visible)
```

Pass `null` to `namedWhere` to remove the condition entirely:

```
$sql->namedWhere('visible', null);
```

### SQL options (ORDER BY, LIMIT, OFFSET, …)

[](#sql-options-order-by-limit-offset-)

```
// Set on the table — applied to every result()
$sql->setSqlOptions(['order' => 'title', 'limit' => 20]);

// Or pass per-query
$result->select('id', ['order' => 'title ASC', 'limit' => 10, 'offset' => 20]);
$result->select('id', ['group' => 'brand_id', 'having' => 'COUNT(*) > 1']);
```

### Joins

[](#joins)

```
$cards = new SqlTable('cards');
$products = $cards->join('products');
$products->where('products.card_id = cards.id');
$products->where('products.active = true');

$cards->result()->select('cards.id, products.name');
// SELECT cards.id, products.name
//   FROM cards
//   JOIN products ON ((products.card_id = cards.id) AND (products.active = true))
```

Second argument to `join()` is a shorthand for adding a where condition on the joined table:

```
$cards->join('products', 'products.card_id = cards.id');
```

Third argument sets join options, e.g. `LEFT JOIN`:

```
$cards->join('price_items pi', 'products.id = pi.product_id', ['join' => 'LEFT JOIN']);
```

Alternatively, use `setJoinBy('exists')` to turn the join into a correlated EXISTS subquery:

```
$join = $cards->join('products', 'cards.id = products.card_id');
$join->setJoinBy('exists');
// SELECT * FROM cards WHERE EXISTS(SELECT * FROM products WHERE (cards.id = products.card_id))
```

### Autojoins

[](#autojoins)

With `autojoins` enabled a joined table is only included in the SQL when it has at least one named where condition or is explicitly activated:

```
$sql = new SqlTable('cards', [], [], ['autojoins' => true]);
$prods = $sql->join('products')->where('cards.id = products.card_id');

$sql->result()->select();
// SELECT * FROM cards   ← products not included, no named condition

$prods->namedWhere('active', 'products.active = true');
$sql->result()->select();
// SELECT * FROM cards JOIN products ON (…) WHERE (products.active = true)

// Force-include a join by name
$sql->result(['active_join' => 'products'])->select();
```

Control activation explicitly with `setActive()`:

```
$prods->setActive(true);   // always included
$prods->setActive(false);  // never included
$prods->setActive('auto'); // included only when it has named conditions (autojoins behaviour)
```

### Reusing a query as a pattern

[](#reusing-a-query-as-a-pattern)

`copy()` creates an independent clone; subsequent changes to either object do not affect the other:

```
$base = new SqlTable('cards');
$base->where('deleted = false');

$visible = $base->copy();
$visible->where('visible = true');

// $base query is unchanged
```

### SqlJoinOrder — ordering by a joined column

[](#sqljoinorder--ordering-by-a-joined-column)

When the `ORDER BY` field requires an extra join, wrap it in `SqlJoinOrder`:

```
use SqlBuilder\SqlJoinOrder;

$order = new SqlJoinOrder(
    'rank',
    'JOIN (SELECT id, rank FROM ranking WHERE …) r ON r.id = cards.id'
);

$result->select('id', ['order' => $order]);
// SELECT id FROM cards JOIN (…) r ON r.id = cards.id … ORDER BY rank
```

`SqlJoinOrder` supports reversal for pagination:

```
$reversed = $order->reversed(); // flips ASC↔DESC, NULLS FIRST↔LAST
```

### BindedSql

[](#bindedsql)

`select()` and related methods return a `BindedSql` object pairing the SQL string with its bind array. It can be used directly with dbmole in several ways:

```
$query = $result->select('id');

// Destructure
[$sql, $bind] = $query;
$dbmole->selectIntoArray($sql, $bind);

// Delegate to dbmole via magic __call
$query->selectIntoArray($dbmole);

// Execute a write query
$query->doQuery($dbmole);

// Inline bind values (for logging/debugging)
echo $query->escaped($dbmole);
```

Concatenate two `BindedSql` instances:

```
$a = new BindedSql('WHERE id = :id', [':id' => 1]);
$b = new BindedSql(' AND active = :active', [':active' => true]);
$c = $a->concat($b);
```

### SqlValues — bulk inserts and CTEs

[](#sqlvalues--bulk-inserts-and-ctes)

```
use SqlBuilder\SqlValues;

$values = new SqlValues(['id', 'name']);
$values->add([4, 'Jan']);
$values->add(6, 'Petr');

$values->sql();   // VALUES (4,:name_0),(6,:name_1)
$values->bind();  // [':name_0' => 'Jan', ':name_1' => 'Petr']

// Use as a CTE
$dbmole->selectRows(
    "WITH data(id,name) AS ({$values->sql()}) SELECT * FROM data",
    $values->bind()
);

// Or via withSql()
$values->withSql('data'); // data(id,name) AS (VALUES (4,:name_0),(6,:name_1))

// Create a temporary table
$dbmole->doQuery($values->createTemporaryTableSql('tmp_data'), $values->bind());

// PostgreSQL ARRAY literal with type casting
$values->sqlArray('integer'); // ARRAY[(4,:name_0),(6,:name_1)]::integer[]
```

Column types can be declared for casting:

```
$values = new SqlValues(['id::integer', 'name']);
// or
$values = new SqlValues(['id', 'name'], ['types' => ['integer', 'varchar']]);
```

### MaterializedSqlTable — lazy temporary table

[](#materializedsqltable--lazy-temporary-table)

`MaterializedSqlTable` defers materialization of a query into a PostgreSQL temporary table until the result is first used, avoiding repeated execution of expensive subqueries:

```
use SqlBuilder\MaterializedSqlTable;

$mt = new MaterializedSqlTable($sqlTable, $dbmole, ['fields' => 'id, title']);

// Temporary table is created on the first call to result()
$dbmole->selectInt($mt->result()->count());
$dbmole->selectRows($mt->result()->select('id, title'));
```

Or materialize directly via `SqlTable`:

```
$mat = $sql->materialize($dbmole, 'id, title');
$dbmole->selectRows($mat->result()->select());
```

Installation
------------

[](#installation)

```
composer require atk14/sql-builder

```

Testing
-------

[](#testing)

```
composer update --dev

```

Prepare the PostgreSQL test database (once):

```
psql -c "CREATE USER test WITH PASSWORD 'test';"
psql -c "CREATE DATABASE test OWNER test;"

```

Run tests:

```
cd test
../vendor/bin/run_unit_tests

```

Run a single test file:

```
cd test
../vendor/bin/run_unit_tests tc_sql_table.php

```

License
-------

[](#license)

SqlBuilder is free software distributed [under the terms of the MIT license](http://www.opensource.org/licenses/mit-license)

###  Health Score

52

—

FairBetter than 96% of packages

Maintenance95

Actively maintained with recent releases

Popularity26

Limited adoption so far

Community15

Small or concentrated contributor base

Maturity59

Maturing project, gaining track record

 Bus Factor1

Top contributor holds 53% 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 ~56 days

Recently: every ~223 days

Total

35

Last Release

23d ago

Major Versions

v1.1 → v2.02021-05-24

v2.3.1 → v3.0.02021-05-25

v3.2.7 → v4.0.02022-05-13

v3.x-dev → v4.2.02023-03-28

PHP version history (2 changes)v1.0PHP >=5.5.0

v4.0.0PHP >=7.1.0

### Community

Maintainers

![](https://avatars.githubusercontent.com/u/974278?v=4)[Jaromir Tomek](/maintainers/yarri)[@yarri](https://github.com/yarri)

---

Top Contributors

[![lokik](https://avatars.githubusercontent.com/u/1487957?v=4)](https://github.com/lokik "lokik (44 commits)")[![yarri](https://avatars.githubusercontent.com/u/974278?v=4)](https://github.com/yarri "yarri (39 commits)")

### Embed Badge

![Health badge](/badges/atk14-sql-builder/health.svg)

```
[![Health](https://phpackages.com/badges/atk14-sql-builder/health.svg)](https://phpackages.com/packages/atk14-sql-builder)
```

###  Alternatives

[jdorn/sql-formatter

a PHP SQL highlighting library

3.8k117.8M121](/packages/jdorn-sql-formatter)[backup-manager/backup-manager

A framework agnostic database backup manager with user-definable procedures and support for S3, Dropbox, FTP, SFTP, and more with drivers for popular frameworks.

1.7k1.6M11](/packages/backup-manager-backup-manager)[propel/propel1

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

8351.6M88](/packages/propel-propel1)[insolita/yii2-migration-generator

Set of gii tools for generating files for migration by schema of table , phpdoc or table data

108508.0k5](/packages/insolita-yii2-migration-generator)[xpdo/xpdo

A PDO-based Object/Relational Bridge Library

7088.4k4](/packages/xpdo-xpdo)[voku/session2db

A PHP library acting as a wrapper for PHP's default session handling functions which stores data in a MySQL database, providing both better performance and better security and protection against session fixation and session hijacking.

2920.0k](/packages/voku-session2db)

PHPackages © 2026

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