StormPHP Queries - Guide

StormPHP Queries is a combination of a query builder and an Object-Relational Mapping (ORM) library designed to fetch data effectively. It’s intuitive, lightweight, and flexible — with zero configuration required.

• Hierarchical models (ORM advantages without over-configuration)
• Fluent query builder (supports criteria-finder pattern for modern architectures)
• Subqueries
• No need to describe the DB schema
• Lightweight, tidy code (no extra dependencies)
• Supports multiple databases (tested with PostgreSQL, MySQL, MariaDB, MSSQL, SQLite)
• Fluent API
• Keep your domain models clean (DDD-friendly)
• Developer-friendly (profiling queries, inspecting generated SQL)

---

Table of Contents

1. Quick Start
2. Basic Queries
3. Select Query
   • Aggregation Functions
   • Join / Left Join
   • Where
   • OrderBy
   • GroupBy
   • Having
4. ORM
5. Subqueries
6. Assembling Queries
7. Insert
8. Update
9. Delete
10. Profiling and Logging
11. Notice
12. Tests
13. Examples
14. Author
15. License

---

Quick Start

Installation

composer require stormmore/queries

Establishing a Connection

StormPHP Queries uses PDO:

use Stormmore\Queries\ConnectionFactory;
use Stormmore\Queries\StormQueries;

$connection = ConnectionFactory::createFromString("dsn", "user", "password");
$queries = new StormQueries($connection);

---

Basic Queries

Find product by ID:

$product = $queries->find('products', ['id' => 5]);

Find all products in a category:

$products = $queries->findAll('products', ['category_id' => 10]);

Insert:

$id = $queries->insert('products', [
'name' => 'Golden watch',
'price' => 756
]);

Update:

$queries->update('products', ['id' => $id], ['name' => 'Renamed product']);

Delete:

$queries->delete('products', ['id' => $id]);

Count:

$count = $queries->count('products', ['in_sale' => true]);

Existence check:

$exists = $queries->exist('products', ['id' => 5]);

Map to class:

use Stormmore\Queries\Mapper\Map;

$product = $queries->find('products', ['id' => 5], Map::select([
'product_id'   => 'id',
'product_name' => 'name'
], UserProduct::class));

---

Select Query

Build a query step by step with a fluent API:

$queries
->select("table", ["column1", "column2"])
->where('id', 2)
->find();

Each method (select, join, leftJoin, where, orderBy, etc.) adds parameters instead of replacing them.

$queries->select('columnA')->select('columnB')->select('columnC');
// SELECT columnA, columnB, columnC

Aggregation Functions

$queries->select('products')->count();
$queries->select('products')->min('price');
$queries->select('products')->max('price');
$queries->select('products')->sum('price');
$queries->select('products')->avg('price');

Join / Left Join

$queries
->select('tableA')
->join('tableB', ['tableB.id' => 'tableA.id')
->find();
//or
$queries
->select('tableA')
->join('tableB', ['tableB.id' => 'tableA.id'])
->find();

$queries
->select('tableA')
->leftJoin('tableB', ['tableB.id' => 'tableA.id'])
->find();
//or
$queries
->select('tableA')
->leftJoin('tableB', ['tableB.id' => 'tableA.id'])
->find();

Where

Supports many operators:

$queries
->select('tableA')
->where('column', 'val1')
->where('column', '=', 'val1')
->where('column', 'IN', ['val2', 'val3'])
->where('column', 'LIKE', '%a%')
->where('column', '<>', 15)
->where('column', 'BETWEEN', 5, 10)
->where('column', 'IS NULL')
->where('columnA = ? and columnB = ?', ['valA', 'valB']);

Default conjunction is AND. Use orWhere for OR.

Nested conditions:

$queries
->select('tableA')
->where('columnA', 'val1')
->where(function($q) {
    $q->where('column', 'val2')->orWhere('column', 'val3');
});

OrderBy

$queries->select('table')->orderByDesc('column1');
$queries->select('table')->orderByAsc('column1');

GroupBy

$queries->select('table')->groupBy('column1', 'column2');

Having

$queries
->select('customers', 'country, city, count(*)')
->groupBy('country, city')
->having('count(*)', '>', 1)
->find();

---

ORM

Define mappings for query results:

$orders = $queries
->select('orders o', Map::select("orders", [
'order_id'   => 'id',
'order_date' => 'date'
]))
->leftJoin('shippers sh', ['sh.shipper_id' => 'o.shipper_id'], Map::one('shipper', [
    'shipper_id'   => 'id',
    'shipper_name' => 'name'
]))
->findAll();

Supports:

• One-to-one
• One-to-many
• Many-to-many
• Nested hierarchical mapping

---

Subqueries

From:

$queries
->select(SubQuery::create($queries->select('products'), 'p'))
->where('p.product_id', 7)
->find();

Left Join:

$queries
->select(SubQuery::create($queries->from('products'), 'p'))
->leftJoin(SubQuery::create($queries->from('suppliers'), 's'), ['s.supplier_id' => 'p.supplier_id'])
->findAll();

Where with subquery:

$queries
->select("products")
->where("category_id", 1)
->where('price', '<=', $queries
    ->select("avg(price)")
    ->from("products")
    ->where("category_id", 1)
)
->findAll();

---

Assembling Queries

$query = $queries
->select('products')
->join('product_photos', ['product_photos.product_id' => 'products.id'])
->where('is_in_sale', true);

if ($criteria->hasCategory()) {
$query->where('category_id', $criteria->getCategoryId());
}

if ($criteria->hasOrder()) {
$query->orderBy($criteria->getOrderField(), $criteria->getOrderDirection());
}

if ($criteria->hasSearchPhrase()) {
$query->where('description', "LIKE", '%' . $criteria->getPhrase() . '%');
}

$products = $query->findAll();

---

Insert

$id = $queries->insert('person', ['name' => 'Michael']);

Insert many:

$queries
->insertMany('person', [
['name' => 'Michael'],
['name' => 'Kevin'],
['name' => 'LeBron']
])
->execute();

---

Update

$queries->update('person', ['id' => 2], ['name' => 'Matthew']);

Or with query builder:

$queries->updateQuery('products')
->where('id', 3)
->set('price = price + 5')
->execute();

---

Delete

$queries->delete('person', ['id' => 1]);

Or with query builder:

$queries->deleteQuery('person')->where('id', 1)->execute();

---

Profiling and Logging

$connection->onSuccess(function(string $sql, DateInterval $interval) {
// log successful queries
});

$connection->onFailure(function(string $sql, DateInterval $interval, Exception $e) {
// log failed queries
});

---

Notice

• Requires PHP 8.0+
• Uses PDO
• Tested with PostgreSQL, MySQL, MariaDB, SQL Server, SQLite

---

Tests

Run tests with Docker:

docker-compose up
./run.mysql.cmd

---

Examples

See the tests directory for more detailed use cases.

---

Author

Michał Czerski

---

License

StormPHP Queries is licensed under the MIT License.

This site is open source. Improve this page.