// 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',
];
// 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('*');
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
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`
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
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');
// 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);
});
Simple Where Clause
// select all
$query->select('*')->table('users')->where('Id', '00001');
// output :
// SELECT * FROM users WHERE Id = '00001';
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;
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 Raw
$query
->select('*')
->table('users')
->having('Score > 10');
// output :
// SELECT * FROM users HAVING score > 10;
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
// 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);
--------------------------------------------------------
*/
// 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 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
$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' ;
--------------------------------------------------------
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
--------------------------------------------------------
*/
$result =
$query
->where('Id', '00001')
->table('users')
->delete();
// $result : true | exception
/*
- Query Compiled :
--------------------------------------------------------
DELETE users WHERE Id='00001'
--------------------------------------------------------
getQuery
$query
->getQuery();
insertQuery
$query
->table('users')
->insertQuery($data);
updateQuery
$query
->table('users')
->updateQuery($data);
deleteQuery
$query
->table('users')
->deleteQuery($data);
try {
// do some query
$query
}catch(DatabaseException $e) {
$e->getMessage();
}
// do transactions
try {
$query->transaction();
// do query [Insert, Update, Delete]
$query->commit();
}catch(DatabaseException $e) {
$query->rollback();
}