PHP code example of requtize / query-builder

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

requtize / query-builder example snippets

use Requtize\QueryBuilder\Connection;
use Requtize\QueryBuilder\QueryBuilder\QueryBuilderFactory;
use Requtize\QueryBuilder\ConnectionAdapters\PdoBridge;

// Somewhere in our application we have created PDO instance
$pdo = new PDO('dns...');

// Build Connection object with PdoBridge ad Adapter
$conn = new Connection(new PdoBridge($pdo));

// Pass this connection to Factory
$qbf = new QueryBuilderFactory($conn);

// Now we can use the factory as QueryBuilder - it creates QueryBuilder
// object every time we use some of method from QueryBuilder and returns it.
$result = $qbf->from('table')->where('cost', '>', 120)->all();

// Set table to operate on.
$qbf->table('table');
$qbf->table('table', 'next-table');
$qbf->table('table', 'next-table', 'and-another');
$qbf->table([ 'table', 'next-table', 'and-another' ]);
// Alias to table() method.
$qbf->from(...);

// Selects
$qbf->select('*');
$qbf->select('column');
$qbf->select('column1', 'column2', 'column3');
$qbf->select([ 'column1', 'column2', 'column3' ]);
// Select DISTINCT
$qbf->selectDistinct('*');
$qbf->selectDistinct('column');
$qbf->selectDistinct('column1', 'column2', 'column3');
$qbf->selectDistinct([ 'column1', 'column2', 'column3' ]);

$qbf->where('name', 'Adam')
    ->where('name', '=', 'Adam')
    ->orWhere('name', 'Adam')
    ->orWhere('name', '=', 'Adam')
    ->whereNot('name', 'Adam')
    ->whereNot('name', '=', 'Adam')
    ->orWhereNot('name', 'Adam')
    ->orWhereNot('name', '=', 'Adam')
    ->whereIn('name' [ 'Adam', 'Eva' ])
    ->whereNotIn('name' [ 'Adam', 'Eva' ])
    ->orWhereIn('name' [ 'Adam', 'Eva' ])
    ->orWhereNotIn('name' [ 'Adam', 'Eva' ])
    ->whereBetween('age', 10, 20)
    ->orWhereBetween('age', 10, 20)
    ->whereNull('sex')
    ->whereNotNull('sex')
    ->orWhereNull('sex')
    ->orWhereNotNull('sex');

$qbf->where(function ($query) {
    $query->where('id', 1)
        ->whereNot('status', 2);
});

$qbf->where($qbf->raw('name'), 'Adam');
$qbf->where($qbf->raw('name = "Adam"'));

// Simple INNER JOIN
$qbf->join('table', 'name', '=', 'Adam', 'inner')
    // INNER JOIN as Closure with advanced ON criteria
    ->join('table', function ($join) {
        $join->on('name', 'Adam')
             ->on('name', '=', 'Adam')
             ->orOn('name', 'Adam')
             ->orOn('name', '=', 'Adam');
    })
    ->leftJoin('table', 'name', '=', 'Adam')
    ->leftJoin('table', function ($join) {
        // ...
    })
    ->rightJoin('table', 'name', '=', 'Adam')
    ->rightJoin('table', function ($join) {
        // ...
    })
    ->innerJoin('table', 'name', '=', 'Adam')
    ->innerJoin('table', function ($join) {
        // ...
    });

$qbf->all(); // Returns all results.
$qbf->first(); // Returns first result.
$qbf->count($column);
$qbf->max($column);
$qbf->min($column);
$qbf->sum($column);
$qbf->avg($column);

$qbf->from('table')->insert([ 'name' => 'Adam' ]);
$qbf->insert([ 'name' => 'Adam' ], 'table');
$qbf->from('table')->insertIgnore([ 'name' => 'Adam' ]);
$qbf->insertIgnore([ 'name' => 'Adam' ], 'table');
$qbf->from('table')->replace([ 'id' => 12, 'name' => 'Adam' ]);
$qbf->replace([ 'id' => 12, 'name' => 'Adam' ], 'table');

$qbf->getLastId();

$qbf
    ->from('table')
    ->where('name', 'John')
    ->update([ 'name' => 'Adam' ]);
$qbf
    ->where('name', 'John')
    ->update([ 'name' => 'Adam' ], 'table');
$qbf
    ->from('table')
    ->where('name', 'John')
    ->updateOrInsert([ 'name' => 'Adam' ]);
$qbf
    ->where('name', 'John')
    ->updateOrInsert([ 'name' => 'Adam' ], 'table');

$qbf
    ->where('name', 'Adam')
    ->delete('table');
$qbf
    ->from('table')
    ->where('name', 'Adam')
    ->delete();

$qbf->where($qbf->raw('name'), $qbf->raw('Adam'));
$qbf->select($qbf->raw('name'));
$qbf->table($qbf->raw('table'));
// ...and so on...

// SELECT Query.
$rows = $qbf->query('SELECT * FROM table WHERE name = :name', [
    ':name' => 'Adam'
]);
// UPDATE, INSERT, DELETE, etc.
$affectedCount = $qbf->exec('UPDATE table SET id = :id WHERE name = :name', [
    ':id'   => 15,
    ':name' => 'Adam'
]);

$subQuery = $qbf
    ->select('name')
    ->from('persons')
    ->where('id', 15);

$query = $qbf
    ->select('table.*')
    ->from('table')
    ->select($qbf->subQuery($subQuery, 'alias1'));

$nestedQuery = $qbf
    ->select('*')
    ->from($qbf->subQuery($query, 'alias2'));

$qbf->getQuery($type = 'select', array $parameters = []);

// Returns passed PDO object.
$qbf->getPdo();

// Returns all Query Segments created in this instance of Query Builder
$qbf->getQuerySegments();

// Or only selected segment
$qbf->getQuerySegment('where');

// Sets and gets EventDispatcher
$qbf->getEventDispatcher();
$qbf->setEventDispatcher(Requtize\QueryBuilder\Event\EventDispatcherInterface $eventDispatcher);

// Sets FetchMode for PDO. IF PDOs Fetch Mode ies all Query Segments, settings to new object and returns new object. Allows to create new Query, but with earlier defined criterias.
$qbf->forkQuery();

$qb->like('column', 'value');
// WHERE column LIKE '%value%'
$qb->like('column', 'value', 'left|start');
// WHERE column LIKE '%value'
$qb->like('column', 'value', 'right|end');
// WHERE column LIKE 'value%'

$scopes = new ScopesContainer;
$scopes->register('scope-name', function($qb, $arg1, $arg2) {
    if($arg1)
        $qb->where('add_date', '<', 'NOW()');
    if($arg2)
        $qb->where('add_date', '>=', 'NOW()');
});

$qbf->setScopes($scopes);

// ...

$qbf->from('table')->scopeName('arg1', 'arg2')->all();

$qb->where('column', 1)->chunk(30, function (array $chunk) {
    foreach($chunk as $row)
    {
        // Do something with $row...
    }
});

$qb->insert([
    [ 'id' => 1, 'col' => 'val' ],
    [ 'id' => 1, 'col' => 'val' ],
    [ 'id' => 1, 'col' => 'val' ],
    [ 'id' => 1, 'col' => 'val' ]
], true, 'table');