Documentation

Database : Query Builder

Configuration

// location file : /:docroot:/config/databases.php

// Pusaka\Database\Manager::on('default')
//--------------------------------------
$config['database']['default']  = [

    'driver'    => 'mysql',
    'hostname'  => 'localhost',
    'username'  => 'root',
    'password'  => '',
    'database'  => '<<yourdb>>',
    'port'      => '3306'

];

// Pusaka\Database\Manager::on('dbdua')
//--------------------------------------
$config['database']['dbdua']   = [

    'driver'    => 'sqlite',
    'database'  => ROOTDIR . 'storage/<<yourdb>>.db',

];

Connection

// use namespace
use Pusaka\Database\Manager;

// change connection from database config
$queryDefault = Manager::on('default')->builder();

$queryDefault->table('users')->select('*');

$queryAnother = Manager::on('another')->builder();

$queryAnother->table('users')->select('*');

Result

Array Record

// select all
$query->select('*')->table('users')->get();
// output: array > table

Single Record

// select all
$query->select('*')->table('users')->first();
// output: array > key-value

Selects

Select All

// select all
$query->select('*')->table('users');

// output :
// SELECT * FROM users;

Select Column

// select column
$query->select('Id', 'Name')->table('users');

// output :
// SELECT Id, Name FROM users;

Select Column Alias

use Pusaka\Database\Blueprint\Column;

// select column aliases
$query->select('Id', 'Name AS NewName', "'SomeString' AS NewString")->table('users');

$query
    ->select(
        'Id',
        $query->alias('NewName', new Column('Name')),
        $query->alias('NewString', 'SomeString'),
        $query->alias('AnotherString', function($query){

            $query->select(new Value('Foo'));

        })
    )
    ->table('users');

// output :
// SELECT Id, Name AS NewName, 'SomeString' AS NewString, ( SELECT 'Foo' ) AS AnotherString FROM users

Select Aggregate : min, max, avg, sum, count

// select aggregate
$query->select('MAX(Score)', 'MIN(Score)', 'AVG(Score)', 'SUM(Score)', 'COUNT(Score)')->from('users');

$query
    ->select(
        $query->min('Score'),
        $query->max('Score'),
        $query->avg('Score'),
        $query->sum('Score'),
        $query->count('Score')
    )
    ->table('users');

// output :
// SELECT MIN(Id), MAX(Id), COUNT(Id), AVG(Id), SUM(Id) FROM users

Select Aggregate with Alias

use Pusaka\Database\Blueprint\Column;

// select aggregate with aliases
$query
    ->select(
        $query
            ->alias('ScoreMin',     new Column($query->min('Score')) ),
        $query
            ->alias('ScoreMax',     new Column($query->max('Score')) ),

        $query
            ->alias('ScoreAvg',     new Column($query->avg('Score')) ),

        $query
            ->alias('ScoreSum',     new Column($query->sum('Score')) ),

        $query
            ->alias('ScoreCount',   new Column($query->count('Score')) )

    )
    ->table('users');

// output :
// SELECT MIN(Score) AS ScoreMin,MAX(Score) AS ScoreMax,AVG(Score) AS ScoreAvg,SUM(Score) AS ScoreSum,COUNT(Score) AS ScoreCount FROM `users`

Nested Select

Simple nested select

// nested select
$query
    ->select(

        $query
            ->alias('NewColumn1', function($query){
                $query
                    ->select('10');
            })

    )
    ->table('users');

// output :
// SELECT (SELECT 10) AS NewColumn1 FROM users

Joins

Inner join

$query
    ->select('*')
    ->table('users')
    ->join('users_detail', function($query) {

        $query->on('users.Id', '=', 'users_detail.Users');

    });

Left join

$query
    ->select('*')
    ->table('users')
    ->joinLeft('users_detail', function($query) {

        $query->on('users.Id', '=', 'users_detail.Users');

    });

Right join

$query
    ->select('*')
    ->table('users')
    ->joinRight('users_detail', function($query) {

        $query->on('users.Id', '=', 'users_detail.Users');

    });

Outer join

$query
    ->select('*')
    ->table('users')
    ->joinFull('users_detail');

Unions

// Basic Union

$query
    ->union(function($query){

        $query
            ->select('*')
            ->table('users')
            ->limit(0, 10);

    })
    ->union(function($query){

        $query
            ->select('*')
            ->table('users')
            ->limit(0, 10);

    });
// Union All

 $query
    ->union(function($query){

        $query
            ->select('*')
            ->table('users')
            ->limit(0, 10);

    })
    ->unionAll(function($query){

        $query
            ->select('*')
            ->table('users')
            ->limit(0, 10);

    });
// Union Distinct

$query
    ->union(function($query){

        $query
            ->select('*')
            ->table('users')
            ->limit(0, 10);

    })
    ->union(function($query){

        $query
            ->select('*')
            ->table('users')
            ->limit(0, 10);

    });

Where Clauses

Simple Where Clause

// select all
$query->select('*')->table('users')->where('Id', '00001');

// output :
// SELECT * FROM users WHERE Id = '00001';

Additional Where Clauses

Additional Where Clause

// raw
$query->select('*')->table('users')
    ->where(" Id = '00001' ");

// equal
$query->select('*')->table('users')
    ->where('Id', '00001');

// operator [ = | >= | <= | > | < | AND | OR | <> ]
$query->select('*')->table('users')
    ->where('Id', '=', '00001');

// output :
// SELECT * FROM users WHERE Id = '00001';

Where - OR

// select all
$query->select('*')->table('users')
    ->where('Id', '00001')
    ->orWhere('Id', '00002');

// output :
// SELECT * FROM users WHERE Id = '00001' OR Id = '00002';

Where with Select Statement

// select all
$query->select('*')->table('users')
    ->whereIn('Id', function($query){
        $query->select('Id')
            ->table('users')
            ->where('Id', '00001');
    });

// output :
// SELECT * FROM users WHERE users.`Id` IN ( SELECT users.Id FROM users WHERE Id='00001');

Where IN

// select all
$query->select('*')->table('users')
    ->whereIn('Id', ['00001', '00002']);

// output :
// SELECT * FROM users WHERE users.`Id` IN ('00001', '00002');

Where NOT IN

// select all
$query->select('*')->table('users')
    ->whereNotIn('Id', ['00001', '00002']);

// output :
// SELECT * FROM users WHERE users.`Id` NOT IN ('00001', '00002');

Where IN with Select Statement

// select all
$query->select('*')->table('users')
    ->whereIn('Id', function($query){
        $query->select('Id')
            ->table('users')
            ->where('Id', '00001');
    });

// output :
// SELECT * FROM users WHERE users.`Id` IN ( SELECT users.Id FROM users WHERE Id='00001');

Where BETWEEN

// select all
$query->select('*')->table('users')
    ->whereBetween('Id', ['00001', '00002']);

// output :
// SELECT * FROM users WHERE ( users.`Id` BETWEEN '00001' AND '00002' );

Where NOT BETWEEN

// select all
$query->select('*')->table('users')
    ->whereNotBetween('Id', ['00001', '00002']);

// output :
// SELECT * FROM users WHERE ( users.`Id` NOT BETWEEN '00001' AND '00002' );

Where DATE / DAY / MONTH / YEAR

// select date
// hold

// $query->select('*')->table('users')
//  ->whereDate('create_at', '2019-01-31');

// output :
// SELECT * FROM users WHERE DATE(create_at) = '2019-01-31';

// select day
// hold

// $query->select('*')->table('users')
//  ->whereDay('create_at', '31');

// output :
// SELECT * FROM users WHERE DAY(create_at) = 31;

// select month
// hold
// $query->select('*')->table('users')
//  ->whereMonth('create_at', '1');

// output :
// SELECT * FROM users WHERE MONTH(create_at) = 1;

// select year
// hold
// $query->select('*')->table('users')
//  ->whereYear('create_at', '2019');

// output :
// SELECT * FROM users WHERE MONTH(create_at) = 1;

Parameter Grouping

Where parameter grouping

// grouping where
$query
    ->select('*')
    ->table('users')
    ->where(function($query) {
        $query->where('Id', '00001');
    });
// output :
// SELECT * FROM users WHERE ( a = 20 );

// grouping where equal to
$query
    ->select('*')
    ->table('users')
    ->where(function($query){
        $query
            ->select('Id')
            ->table('users')
            ->where('Id', '00001');
    }, '00001');
// output :
// SELECT * FROM users WHERE ( SELECT Id FROM users WHERE Id='00001' ) = '00001';

// grouping where (operator) to [ = | >= | <= | > | < | AND | OR | <> ]
$query
    ->select('*')
    ->table('users')
    ->where(function($query){
        $query
            ->select('Id')
            ->table('users')
            ->where('Id', '00001');
    }, '>', '00001');
// output :
// SELECT * FROM users WHERE ( SELECT Id FROM users WHERE Id='00001' ) > '00001';

Having

Having Raw

$query
    ->select('*')
    ->table('users')
    ->having('Score > 10');

// output :
// SELECT * FROM users HAVING score > 10;

Ordering, Grouping, Limit and Offset

Ordering

$query
    ->select('*')
    ->table('users')
    ->orderBy('Name', 'desc');

// output :
// SELECT * FROM users ORDER BY `Name` DESC

Grouping

$query
    ->select('*')
    ->table('users')
    ->groupBy('Name');

// output :
// SELECT * FROM users GROUP BY `Name`

Limit

$query
    ->select('*')
    ->table('users')
    ->limit(0, 100);

// output :
// SELECT * FROM users LIMIT 100

Limit and Offset

$query
    ->select('*')
    ->table('users')
    ->limit(2, 100);

// output :
// SELECT * FROM users LIMIT 1, 100

Insert

// single insert
$result = 
    $query
        ->table('users')
        ->insert(
            [
                'Id'    => '00005',
                'Name'  => 'Name1',
                'Role'  => 'Admin',
                'Score' => 70
            ]
        );
// $result : true | exception

/*

- Query Compiled :
--------------------------------------------------------

INSERT INTO users (Id,Name,Role,Score) 
VALUES ('00005','Name1','Admin',70);

--------------------------------------------------------

*/

Insert Batch


// batch insert

$result = 
    $query
        ->table('users')
        ->insert([
            [
                'Id'    => '00005',
                'Name'  => 'Name1',
                'Role'  => 'Admin',
                'Score' => 70
            ],
            [
                'Id'    => '00006',
                'Name'  => 'Name2',
                'Role'  => 'Admin',
                'Score' => function($query) {
                    // mix select statement
                    $query->select('80');

                }
            ],
        ]);
// $result : true | exception

/*

- Query Compiled :
--------------------------------------------------------

INSERT INTO users (Id,Name,Role,Score) 
VALUES ('00005','Name1','Admin',70),('00006','Name2','Admin',89);

--------------------------------------------------------

*/

Insert Select Statement

// insert with select statement
$select = function($query) {

    $query
        ->select(
            // generate uniqe id
            $query->uuid(), 
            'Name', 
            'Role', 
            'Score', 
            'NOW() AS create_at', 
            'NOW() AS update_at'
        )
        ->table('users');

};

$result =
    $query
        ->table('users (Id, Name, Role, Score, create_at, update_at)')
        ->insert($select);

// $result : true | exception

/*

- Query Compiled :
--------------------------------------------------------

INSERT INTO users (Id,Name,Role,Score,create_at,update_at) 
SELECT 
    REPLACE(UPPER(UUID()), '-', '') AS Id,
    `Name` AS Name,
    `Role` AS Role,
    `Score` AS Score,
    NOW() AS create_at,
    NOW() AS update_at 
FROM users;

--------------------------------------------------------
*/

Update

// Update

$result = 
    $query
        ->where('Id', '00002')
        ->table('users')
        ->update(
            [
                'Id'    => '00001',
                'Name'  => 'Vincents',
                'Role'  => 'Admin',
                'Score' => 89
            ]
        );
// $result : true | exception

/*
- Query Compiled :
--------------------------------------------------------
UPDATE users 
    SET `Id`='00001',`Name`='Vincents',`Role`='Admin',`Score`=89 
    WHERE `Id` = '00002' ;
--------------------------------------------------------

Update Batch


use Pusaka\Database\Blueprint\Column;

$result = 
    $query
        ->table('users')
        ->where('1 = 1')
        ->set('users.Name',     new Column('{join}.Name'))
        ->set('users.Role',     new Column('{join}.Role'))
        ->set('users.Score',    new Column('{join}.Score'))
        ->updateQuery([
            [
                'Id'    => '00006',
                'Name'  => 'Foo',
                'Role'  => 'Admin',
                'Score' => 10
            ],
            [
                'Id'    => '00006',
                'Name'  => 'Foo2',
                'Role'  => 'Admin',
                'Score' => 40
            ],
            [
                'Id'    => '00006',
                'Name'  => 'Foo3',
                'Role'  => 'Admin',
                'Score' => 89
            ]
        ], '{join}.Id = users.Id');
// $result : true | exception

/*
- Query Compiled :
--------------------------------------------------------
UPDATE `users`
       JOIN (
            (SELECT '00006' AS Id,
                    'Foo'   AS NAME,
                    'Admin' AS Role,
                    10      AS Score)
            UNION ALL
            (SELECT '00006' AS Id,
                    'Foo2'  AS NAME,
                    'Admin' AS Role,
                    40      AS Score)
            UNION ALL
            (SELECT '00006' AS Id,
                    'Foo3'  AS NAME,
                    'Admin' AS Role,
                    89      AS Score)
            )temp_table_join_update
         ON temp_table_join_update.id = users.id
--------------------------------------------------------
*/

Delete

$result = 
    $query
        ->where('Id', '00001')
        ->table('users')
        ->delete();
// $result : true | exception

/*
- Query Compiled :
--------------------------------------------------------
DELETE users WHERE Id='00001'
--------------------------------------------------------

Debugging

getQuery

$query
    ->getQuery();

insertQuery

$query
    ->table('users')
    ->insertQuery($data);

updateQuery

$query
    ->table('users')
    ->updateQuery($data);

deleteQuery

$query
    ->table('users')
    ->deleteQuery($data);

Catch Error / Error Handling

try {
    // do some query
    $query
}catch(DatabaseException $e) {
    $e->getMessage();
}

Transactions

// do transactions
try {

    $query->transaction();

    // do query [Insert, Update, Delete]

    $query->commit();   

}catch(DatabaseException $e) {

    $query->rollback();

}