README

It's a combination of a query builder and an Object-Relational Mapping (ORM) designed to fetch data most effectively.
Intuitive, easy to learn, light yet powerful. If you work with ORMs, you will notice that you don't have to configure anything, but still you have the possibility to build queries easily and hierarchically retrieve them.

hierarchical models (most significant advantage of ORM without disadvantages of over-configuration, etc.)
query builder supporting assembling queries (support criteria-finder pattern for modern architectures)
subqueries
no configuration needed
no need to describe the DB schema
lightweight and tidy code (no additional dependencies)
supports various databases (tested with PostgreSQL, MySQL, MariaDB, MSSQL, SQLite)
fluent API
no need to change your models (keep DDD aggregates clean)
developer-friendly (profiling queries, overview of generated SQL)
intuitive and flexible

1. Quick start
2. Basic Queries
2. Select Query
3. ORM
4. SubQueries
5. Assembling query
6. Insert Query
7. Update Query
8. Delete Query
9. Profiling and logging queries
10. Notice
11. Tests
12. Examples
13. Author
14. License

Quick start

Installation

To install, you will need a composer.

composer require stormmore/queries

Establishing 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

Finding product by id

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

Finding all products in the category

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

Inserting product

$query->insert('products', ['name' => 'Golden socks', 'price' => 777]);

Updating product

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

Deleting product

$queries->delete('products', 'id = ?', 5);
//or
$queries->delete('products', ['id' => 5]);

Count products

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

Check if the product exists

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

Mapping records to user class

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

Full working example

use Stormmore\Queries\StormQueries;
use Stormmore\Queries\Mapper\Map;
use Stormmore\Queries\ConnectionFactory;

$connection = ConnectionFactory::createFromString(
    "mysql:host=localhost;port=7801;dbname=storm_test", "mysql", "mysql")
$queries = new StormQueries($connection);

$queries
  ->select('customers c',  Map::select([
      'customer_id' => 'id',
      'customer_name' => 'name'
  ]))
  ->leftJoin('orders o', 'o.customer_id = c.customer_id', Map::many("orders", [
          'order_id' => 'id'
  ]))
  ->leftJoin('shippers sh', 'sh.shipper_id = o.shipper_id', Map::one('shipper', [
          'shipper_id' => 'id',
          'shipper_name' => 'name'
  ]))
  ->leftJoin('order_details od', 'od.order_id = o.order_id', Map::many('details', [
      'order_detail_id' => 'id',
      'quantity' => 'quantity'
  ]))
  ->leftJoin('products p', 'p.product_id = od.product_id', Map::one('product', [
      'product_id' => 'id',
      'product_name' => 'name',
      'price' => 'price'
  ]))
  ->findAll();
     
foreach($customers as $customer) {
    print_customer($customer);
    foreach($customer->orders as $order) {
      print_order($order);  
      foreach($order->details as $detail) {
        print_detail($detail);
      }    
    }
}

Select Query

Build a select query with the select method Build it with fluent API

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

StormPHP Queries is made to handle assembling queries step by step in various scenarios, so every time you invoke methods like
select join leftJoin where orWhere having orHaving orderBy orderByAsc orderByDesc
they add parameters instead of replacing them with the final query

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

Builds 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();

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

Where

$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', '>', 1)
    ->where('column', '>=', 1)
    ->where('column', '<', 1)
    ->where('column', '<=', 1)
    ->where('column', 'IS NULL')
    ->where('column', 'IS NOT NULL')
    ->where('columnA = ? and columnB = ? and columnC = ?', ['valA', 'valB', 3])
    ->where([
        'columnA' => 'valA',
        'columnB' => 'valB',
        'columnC' => 3
    ])

The default conjunction is AND. To use OR use orWhere

$queries
    ->select('tableA')
    ->where('columnA', 'val1')
    ->orWhere('column', 'IN', ['val2', 'val3'])

Nested conditions

If you want to use a group of conditions enclosed in parenthesis, use closure

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

OrderBy

$queries->select('table')->orderByDesc('column1');
$queries->select('table')->orderByAsc('column1');
$queries->select('table')->orderBy('column1', -1); //descending 
$queries->select('table')->orderBy('column1', 1) //ascending
$queries->select('table')->orderByDesc('column1')->orderByDesc('column2');

GroupBy

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

Having

Everything that refers to where or orWhere refers to having and orHaving

$queries
    ->select('cutomers', 'country, city, count(*)')
    ->groupBy('country, city')
    ->having('count(*)', '>', 1)
    ->having('city', 'LIKE', '%o%')
    ->find();

ORM

To use StormPHP Queries as ORM, you need to add a map in the from and join clauses.

Map

Map defines

columns and their class mapping properties
class name (by default stdClass)
identity property (by default id)

From

$queries
  ->select('customers', 'customer_id = ?', 28, Map::select([
      'customer_id' => 'id',
      'customer_name' => 'name'
  ]))
  ->find()

Map to user class

$queries
  ->select('customers',  Map::select([
      'customer_id' => 'id',
      'customer_name' => 'name'
  ], Customer::class))
  ->find()

Map as a combination of index and associative array

$queries
  ->select('customers',  Map::select([
      'customer_id' => 'id',
      'customer_name' => 'name',
      'city', 
      'country'
  ], Customer::class))
  ->find()

Joins

One-to-one relationship

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

foreach($orders as $order) {
    print_order($order);
    print_shipper($order->shipper);
}

One-to-one non-hierarchical relationship

$products = $this->queries
  ->select('products p', Map::select([
      'p.product_id' => 'id',
      'p.product_name' => 'name',
      's.supplier_name' => 'supplierName',
      'c.category_name' => 'categoryName',
  ]))
  ->leftJoin('categories c', 'c.category_id = p.category_id')
  ->leftJoin('suppliers s', 's.supplier_id = p.supplier_id')
  ->findAll();

foreach($products as $product) {
    echo "$product->name $product->supplierName $product->categoryName";
}

One-to-many relationship

$queries
  ->select('customers c',  Map::select([
      'customer_id' => 'id',
      'customer_name' => 'name'
  ]))
  ->leftJoin('orders o', 'o.customer_id = c.customer_id', Map::many("orders", [
          'order_id' => 'id'
  ]))

Many-to-many relationship

$queries
  ->select('products p', Map::select([
      'product_id' => 'id',
      'product_name' => 'name'
  ]))
  ->leftJoin('products_tags pt', 'pt.product_id = p.product_id', Map::join())
  ->leftJoin('tags t', 't.tag_id = pt.tag_id', Map::many("tags", [
      'tag_id' => 'id',
      'name' => 'name'
  ]))
  ->where('p.product_id', 'in', [1,2,3,4])
  ->findAll();

Sub queries

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

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

Restrictions

Using joins requires the use of aliases in the from and join clauses.

Assembling query

$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' => 'Micheal']);

If you don't want to invoke getLastInsertedId on PDO pass false to execute method

$queries->insertQuery('person', ['name' => 'Micheal'])->execute(false);

Insert many

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

Update

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

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

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

$queries->updateQuery('person')->where('id', 2)->set(['name' => 'Matthew'])->execute();

Delete

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

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

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

Profiling and logging queries

To track what queries are going to the database, add callback to the IConnection object.

$connection = ConnectionFactory::createFromString("...", "...", "...")

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

$connection->onFailre(function(string $sql, DateInterval $interval, Exception $e) {
    //log here
})

Notice

StormPHP Queries uses PDO and databases that support it.
Tested with PostgreSQL, MySQL, MariaDB, SqlServer, and Sqlite.

Tests

To run tests, use docker composer up and run one of run.*.cmd

Examples

If you're looking for additional use cases, take a look at the tests in the test directory of the project.

Author

Michał Czerski

If you have any questions or ideas you'd like to share with me, please feel free to contact me on GitHub.

License

StormPHP Queries is licensed under MIT license.

stormmore / queries

Maintainers

Details