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

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

francerz/sql-builder
====================

SQL Builder

v0.5.27(11mo ago)61.5k3MITPHPCI failing

Since Oct 31Pushed 11mo ago2 watchersCompare

[ Source](https://github.com/francerz/php-sql-builder)[ Packagist](https://packagist.org/packages/francerz/sql-builder)[ RSS](/packages/francerz-sql-builder/feed)WikiDiscussions master Synced 3w ago

READMEChangelog (10)Dependencies (3)Versions (126)Used By (3)

SQL Builder
===========

[](#sql-builder)

[![Packagist](https://camo.githubusercontent.com/122f7ad89e5d2ea2d7d8ba49044b12a996a9902bd3334f9df4c51f1eaefe704a/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f6672616e6365727a2f73716c2d6275696c646572)](https://camo.githubusercontent.com/122f7ad89e5d2ea2d7d8ba49044b12a996a9902bd3334f9df4c51f1eaefe704a/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f6672616e6365727a2f73716c2d6275696c646572)[![License](https://camo.githubusercontent.com/50e634c2211afecd1b11b589245ccda7787f0e8608bf27e2f92d4fd4ca6771a4/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f6c6963656e73652f6672616e6365727a2f7068702d73716c2d6275696c6465723f636f6c6f723d253233303037384430)](https://camo.githubusercontent.com/50e634c2211afecd1b11b589245ccda7787f0e8608bf27e2f92d4fd4ca6771a4/68747470733a2f2f696d672e736869656c64732e696f2f6769746875622f6c6963656e73652f6672616e6365727a2f7068702d73716c2d6275696c6465723f636f6c6f723d253233303037384430)[![Packagist Downloads](https://camo.githubusercontent.com/f932cb897d26ef079ace29fb61b8b6a4eafe1ca11fcc15d3001469b95c3a4ce8/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f64742f6672616e6365727a2f73716c2d6275696c6465723f636f6c6f723d253233453042303030)](https://camo.githubusercontent.com/f932cb897d26ef079ace29fb61b8b6a4eafe1ca11fcc15d3001469b95c3a4ce8/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f64742f6672616e6365727a2f73716c2d6275696c6465723f636f6c6f723d253233453042303030)[![Build Status](https://github.com/francerz/php-sql-builder/workflows/PHP%20Unit%20Tests/badge.svg)](https://github.com/francerz/php-sql-builder/workflows/PHP%20Unit%20Tests/badge.svg)

A PHP SQL query builder that prioritizes readability and optimal performance with object based construction.

Table of contents
-----------------

[](#table-of-contents)

- [SQL Builder](#sql-builder)
    - [Table of contents](#table-of-contents)
    - [Installation ↑](#installation-)
    - [Connect to database ↑](#connect-to-database-)
    - [Basic common usage syntax ↑](#basic-common-usage-syntax-)
        - [Select query ↑](#select-query-)
        - [Insert query ↑](#insert-query-)
        - [Update query ↑](#update-query-)
        - [Delete query ↑](#delete-query-)
    - [Build SELECT with WHERE or HAVING clause ↑](#build-select-with-where-or-having-clause-)
        - [Parentheses syntax](#parentheses-syntax)
        - [List of operators ↑](#list-of-operators-)
    - [Building SELECT with JOIN ↑](#building-select-with-join-)
        - [SUPPORTED JOIN TYPES](#supported-join-types)
        - [Examples](#examples)
    - [SELECT nesting ↑](#select-nesting-)
        - [Nesting a Collection of Result Objects](#nesting-a-collection-of-result-objects)
        - [Nesting the First or Last Result Object](#nesting-the-first-or-last-result-object)
    - [Transactions ↑](#transactions-)
    - [Executing Stored Procedures ↑](#executing-stored-procedures-)

Installation [↑](#table-of-contents)
------------------------------------

[](#installation-)

This package can be installed with composer using following command.

```
composer require francerz/sql-builder
```

Connect to database [↑](#table-of-contents)
-------------------------------------------

[](#connect-to-database-)

Using an URI string

```
$db = DatabaseManager::connect('driver://user:password@host:port/database');
```

Using environment variable

```
putenv('DATABASE_SCHOOL_DRIVER', 'driver');
putenv('DATABASE_SCHOOL_HOST', 'host');
putenv('DATABASE_SCHOOL_INST', 'instanceName');
putenv('DATABASE_SCHOOL_PORT', 'port');
putenv('DATABASE_SCHOOL_USER', 'user');
putenv('DATABASE_SCHOOL_PSWD', 'password');
putenv('DATABASE_SCHOOL_NAME', 'database');

// Support to Docker secrets
putenv('DATABASE_SCHOOL_PSWD_FILE', '/run/secrets/db_school_password');

$db = DatabaseManager::connect('school');
```

Basic common usage syntax [↑](#table-of-contents)
-------------------------------------------------

[](#basic-common-usage-syntax-)

```
class Group {
    public $group_id;
    public $subject;
    public $teacher;
}
```

### Select query [↑](#table-of-contents)

[](#select-query-)

```
// SELECT group_id, subject, teacher FROM groups
$query = Query::selectFrom('groups', ['group_id', 'subject', 'teacher']);

$db = DatabaseManager::connect('school');
$result = $db->executeSelect($query);
$groups = $result->toArray(Group::class);
```

### Insert query [↑](#table-of-contents)

[](#insert-query-)

```
$group = new Group();
$group->subject = 'Database fundamentals';
$group->teacher = 'francerz';

// INSERT INTO groups (subject, teacher) VALUES ('Database fundamentals', 'francerz')
$query = Query::insertInto('groups', $group, ['subject', 'teacher']);

$db = DatabaseManager::connect('school');
$result = $db->executeInsert($query);
$group->group_id = $result->getInsertedId();
```

### Update query [↑](#table-of-contents)

[](#update-query-)

```
$group = new Group();
$group->group_id = 10;
$group->subject = 'Introduction to databases';

// UPDATE groups SET subject = 'Introduction to databases' WHERE group_id = 10
$query = Query::update('groups', $group, ['group_id'], ['subject']);

$db = DatabaseManager::connect('school');
$result = $db->executeUpdate($query);
```

### Delete query [↑](#table-of-contents)

[](#delete-query-)

```
// DELETE FROM groups WHERE group_id = 10
$query = Query::deleteFrom('groups', ['group_id' => 10]);

$db = DatabaseManager::connect('school');
$result = $db->executeDelete($query);
```

---

Build SELECT with WHERE or HAVING clause [↑](#table-of-contents)
----------------------------------------------------------------

[](#build-select-with-where-or-having-clause-)

Bellow are examples of using `WHERE` clause which aplies to `SELECT`, `UPDATE`and `DELETE` queries.

> Selecting all fields from table `groups` when the value of column `group_id` is equal to `10`.

```
SELECT * FROM groups WHERE group_id = 10
```

```
// Explicit syntax
$query = Query::selectFrom('groups')->where()->equals('group_id', 10);

// Implicit syntax
$query = Query::selectFrom('groups')->where('group_id', 10);
```

---

> Selecting all fields from table `groups` when value of column `group_id` is equals to `10`, `20` or `30`.

```
SELECT * FROM groups WHERE group_id IN (10, 20, 30)
```

```
// Explicit syntax
$query = Query::selectFrom('groups')->where()->in('group_id', [10, 20, 30]);

// Implicit syntax
$query = Query::selectFrom('groups')->where('group_id', [10, 20, 30]);
```

---

> Selecting all fields from table `groups`when value of column `teacher` is `NULL`.

```
SELECT * FROM groups WHERE teacher IS NULL
```

```
// Explicit syntax
$query = Query::selectFrom('groups')->where()->null('teacher');

// Implicit compact syntax
$query = Query::selectFrom('groups')->where('teacher', 'NULL');
```

---

> Selecting all fields from table `groups` when value of column `group_id` is less or equals to `10` and value from column `subject` contains the word `"database"`.

```
SELECT * FROM groups WHERE group_id where()->lessEquals('group_id', 10)->andLike('subject', '%database%');

// Implicit compact syntax
$query = Query::selectFrom('groups');
$query->where('group_id', 'nestMany('Students', $studentsQuery, $groupRow, Student::class)
    ->where('students.group_id', $groupRow->group_id);

// Connecting to the database and executing the query
$db = DatabaseManager::connect('school');
$result = $db->executeSelect($groupsQuery);
$groups = $result->toArray(Group::class);
```

**Result:**

```
[
    {
        "group_id": 1,
        "subject": "Programing fundamentals",
        "classroom": "A113",
        "Students": [
            {
                "student_id": 325,
                "first_name": "Charlie",
                "last_name": "Ortega"
            },
            {
                "student_id": 743,
                "first_name": "Beth",
                "last_name": "Wilson"
            }
        ]
    },
    {
        "group_id": 2,
        "subject" : "Object Oriented Programming",
        "classroom": "G7-R5",
        "Students": [
            {
                "student_id": 536,
                "first_name": "Dylan",
                "last_name": "Morrison"
            }
        ]
    }
]
```

### Nesting the First or Last Result Object

[](#nesting-the-first-or-last-result-object)

On the other hand, the `linkFirst` method is employed to link only the first result object from a secondary query with each row of the primary query's result. In the given code snippet, this is used to link the first teacher to each group. This method is beneficial when you want to link a single related record to each main record, prioritizing the first match.

Additionally, there is the `linkLast` method, which is similar to `linkFirst`but instead links the last result object from a secondary query to each row of the primary query's result. This can be useful in scenarios where you want to prioritize the most recent or latest related record for each main record.

```
// Primary Query for Groups
$groupsQuery = Query::selectFrom(
    'groups',
    ['group_id', 'teacher_id', 'subject', 'classroom']
);

// Query for Teachers
$teachersQuery = Query::selectFrom(
    'teachers',
    ['teacher_id', 'first_name', 'last_name']
);

// Linking the first teacher to each group
$groupsQuery
    ->linkFirst('Teacher', $teachersQuery, $groupRow, Teacher::class)
    ->where('teachers.teacher_id', $groupRow->teacher_id);

// Query for Classes
$classesQuery = Query::selectFrom(
    'groups_classes',
    ['class_id', 'group_id', 'topic', 'date']
)->orderBy('date', 'ASC');

// Linking the last class to each group
$groups
    ->linkLast('LastClass', $classesQuery, $groupRow, GroupClass::class)
    ->where('groups_classes.group_id', $groupRow->group_id);

// Connecting to the database and executing the query
$db = DatabaseManager::connect('school');
$result = $db->executeSelect($groupsQuery);
$groups = $result->toArray(Group::class);
```

**Result:**

```
[
    {
        "group_id": 1,
        "teacher_id": 3,
        "subject": "Programming fundamentals",
        "classroom": "A113",
        "Teacher": {
            "teacher_id": 3,
            "first_name": "Rosemary",
            "last_name": "Smith"
        },
        "LastClass": {
            "class_id": 233,
            "group_id": 1,
            "topic": "Algorithms",
            "date": "2024-04-18"
        }
    },
    {
        "group_id": 2,
        "teacher_id": 75,
        "subject" : "Object Oriented Programming",
        "classroom": "G7-R5",
        "Teacher": {
            "teacher_id": 75,
            "first_name": "Steve",
            "last_name": "Johnson"
        },
        "LastClass": null
    }
]
```

By choosing the appropriate nesting mode (`nestMany`, `linkFirst`, or `linkLast`), you can tailor your queries to efficiently handle nested data based on your specific data structure and requirements.

> **Legacy old nest syntax**
>
> There is a legacy nest syntax, that stills working underhood.
>
> ```
> $groupsQuery->nest(['Students' => $studentsQuery], function (NestedSelect $nest, RowProxy $row) {
>     $nest->getSelect()->where('s.group_id', $row->group_id);
> }, NestMode::COLLECTION, Student::class);
> ```

Transactions [↑](#table-of-contents)
------------------------------------

[](#transactions-)

One of the most important features in databases is to keep data consistency across multiple records that might be stored in multiple tables.

```
$db = DatabaseManager::connect('school');
try {
    $db->startTransaction();

    // Perform any needed operation inside this block to keep consistency.

    $db->commit();
} catch (Exception $ex) {
    $db->rollback();
}
```

Executing Stored Procedures [↑](#table-of-contents)
---------------------------------------------------

[](#executing-stored-procedures-)

```
// Connecting to database 'school'.
$db = DatabaseManager::connect('school');

// Calls stored procedure with two argments.
/** @var SelectResult[] */
$results = $db->call('procedure_name', 'arg1', 'arg2');

// Shows how many results obtained from procedure.
echo count($results) . ' results.' . PHP_EOL;

// Iterating procedure result sets.
foreach ($results as $i => $selectResult) {
    echo "Fetched " . $selectResult->getNumRows() . PHP_EOL;
}
```

###  Health Score

39

—

LowBetter than 85% of packages

Maintenance50

Moderate activity, may be stable

Popularity21

Limited adoption so far

Community12

Small or concentrated contributor base

Maturity62

Established project with proven stability

 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 ~14 days

Recently: every ~85 days

Total

124

Last Release

356d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/630263d156ec44e1d55c3d237fe08354442d0cbe7b257032997d0a649baf1854?d=identicon)[francerz](/maintainers/francerz)

---

Top Contributors

[![francerz](https://avatars.githubusercontent.com/u/10836837?v=4)](https://github.com/francerz "francerz (202 commits)")

---

Tags

object-relational-mappingrelational-databases

### Embed Badge

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

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

###  Alternatives

[aws/aws-sdk-php

AWS SDK for PHP - Use Amazon Web Services in your PHP project

6.2k532.1M2.5k](/packages/aws-aws-sdk-php)[symfony/symfony

The Symfony PHP framework

31.4k86.9M2.2k](/packages/symfony-symfony)[guzzlehttp/psr7

PSR-7 message implementation that also provides common utility methods

7.9k1.1B3.7k](/packages/guzzlehttp-psr7)[kreait/firebase-php

Firebase Admin SDK

2.4k42.7M84](/packages/kreait-firebase-php)[neuron-core/neuron-ai

The PHP Agentic Framework.

2.0k496.1k33](/packages/neuron-core-neuron-ai)[getgrav/grav

Modern, Crazy Fast, Ridiculously Easy and Amazingly Powerful Flat-File CMS

15.5k85.4k1](/packages/getgrav-grav)

PHPackages © 2026

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