README

It's combination of query builder and ORM made to fetch data in most effective way.
Intuitive, easy to learn, light yet powerful. If you work with ORMs you will notice that you don't have to configure nothing but still you have possibility to build queries easily and retrieve in hierarchical way.

• hierarchical models (biggest 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 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
  • Aggregation functions
  • Join/Left join
  • Where
  • OrderBy
  • GroupBy
  • Having
• 3. ORM
• 4. SubQueries
• 5. Assembling query
• 6. Insert Query
• 7. Update Query
• 8. Delete Query
• 9. Profiling nad logging queries
• 10. Notice
• 11. Tests
• 12. Examples
• 13. Author
• 14. License

Quick start

Installation

You will need composer or use Storm PHP Framework (Storm PHP Queries are part of it)

composer require stormmore/queries

Establishing connection

StormQueries 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 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 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 select query with select method Build it with fluent API

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

StormQueries 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 replacing them to 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
    ])

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 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 what 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 StormQueries as ORM you need add map in from and join clause.

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 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 using aliases in from and join clause.

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

or

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

or

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

or

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

Delete

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

or

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

or

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

Profiling nad logging queries

To track what queries are going to database add callback to 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

StormQueries uses PDO and databases which 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 are looking for more use cases take a look at tests in test directory of project.

Author

Michał Czerski

If you have any question or ideas you want share with me contact me on GitHub.

License

StormQueries is licensed under MIT licence.