krmgns / oppa Goto Github PK

// manual
$autoload = require('<path to oppa>/src/Autoload.php');
$autoload->register();

// simply for single databases, see wiki for more
$cfg = [
   'agent'    => 'mysql',
   'database' => [
      'host'     => 'localhost',  'name'     => 'test',
      'username' => 'test',       'password' => '********',
      'charset'  => 'utf8',       'timezone' => '+00:00',
   ]
];

$db = new Oppa\Database($cfg);
$db->connect();

$agent = $db->getLink()->getAgent();
$agent->query('select * from `users` where `old` > ?', [25]);
dump $agent->rowsCount();

// raw queries
$result = $agent->query('select * from `users`');
if ($result->hasData())
// if ($result->count() > 0)
   foreach ($result as $user)
      dump $user->name;

// or
if ($agent->rowsCount())
   foreach ($agent->getResult() as $user)
   // or foreach ($agent->getResult()->getData() as $user)
      dump $user->name;

// fetch one
$user = $agent->get('select * from `users` where `old` > ?', [50]);
dump $user->name;
// fetch all
$users = $agent->getAll('select * from `users` where `old` > ?', [50]);
foreach ($users as $user) {
   dump $user->name;
}

// or shorcut methods

// get one user
$result = $agent->select('users');
// get one users if old greater than 50
$result = $agent->select('users', '*', 'old > ?', [50]);
// get many users
$result = $agent->selectAll('users');
// get many users if old greater than 50
$result = $agent->selectAll('users', '*', 'old > ?', [50]);

// insert a user
$result = $agent->insert('user', ['name' => 'Ali', 'old' => 30]); // int: last insert id
// insert many users
$result = $agent->insertAll('user', [['name' => 'Ali', 'old' => 30], ...]); // int[]: last insert ids

// update a user
$result = $agent->update('user', ['old' => 30], 'id = ?', [123]); // int: affected rows
// update many users
$result = $agent->updateAll('user', ['old' => 30], 'id > ?', [123]); // int: affected rows

// delete a user
$result = $agent->delete('user', 'id = ?', [123]); // int: affected rows
// delete many users
$result = $agent->deleteAll('user', 'id > ?', [123]); // int: affected rows

// use and init with exists $db
use Oppa\Query\Builder as Query;

$query = new Query($db->getLink());
// set target table
$query->setTable('users u');

// build query
$query->select('u.*')
    ->aggregate('sum', 'us.score', 'sum_score')
    ->join('users_score us', 'us.user_id=u.id')
        ->selectMore('us.score')
    ->joinLeft('users_login ul', 'ul.user_id=u.id')
        ->selectMore('ul.login')
    ->whereIn('u.id', [1,2,3])
    ->whereBetween('u.old', [30,50])
    ->whereNotNull('ul.login')
    ->groupBy('u.id')
    ->orderBy('u.old')
    ->having('sum_score <= ?', [30])
    ->limit(0,10)
;

// get batch object
$batch = $agent->getBatch();

// set autocommit=0 (not needed for pgsql)
$batch->lock();
try {
    // commit
    $batch->doQuery('insert into `users` values(null,?,?)', ['John', 25]);
} catch (\Throwable $e) {
    // rollback
    $batch->undo();
}
// set autocommit=1 (not needed for pgsql)
$batch->unlock();

// get last insert id if success
$result = $batch->getResult();
if ($result) {
    dump $result->getId();
}

// remove query queue and empty result array
$batch->reset();

// get batch object
$batch = $agent->getBatch();

// set autocommit=0 (not needed for pgsql)
$batch->lock();
try {
    $batch->queue('insert into `users` values(null,?,?)', ['John', 25]);
    $batch->queue('insert into `users` values(null,?,?)', ['Boby', 35]);
    $batch->queue('insert into `uzerz` values(null,?,?)', ['Eric', 15]); // boom!
    // commit
    $batch->do();
} catch (\Throwable $e) {
    // rollback
    $batch->undo();
}
// set autocommit=1 (not needed for pgsql)
$batch->unlock();

// get insert ids if success
foreach ($batch->getResults() as $result) {
    dump $result->getId();
}

// remove query queue and empty result array
$batch->reset();

class Users extends Oppa\ActiveRecord\ActiveRecord {
   protected $table = 'users';
   protected $tablePrimary = 'id';
}

// init active record object
$users = new Users($db);

// find one that id=1
$user = $users->find(1);
dump $user;

// check user found?
if ($user->isFound()) {
   dump $user->name;
}

// find all
$users = $users->findAll();
// find many (id=1,2,3)
$users = $users->findAll([1,2,3]);
$users = $users->findAll('id in(?)', [[1,2,3]]);
$users = $users->findAll('id in(?,?,?)', [1,2,3]);
dump $users;

foreach ($users as $user) {
   dump $user->name;
}

$users = $users->findAll([-1,null,'foo']);
dump $users->hasData(); // false

// insert a user
$user = $users->entity();
$user->name = 'Ali';
$user->old  = 40;
dump $user->save();
// or $user = $users->save($user);
// here we see "id" will be filled with last insert id
dump $user;

// update a user (id=1)
$user = $users->entity();
$user->id  = 1;
$user->old = 55;
dump $user->save();
// or $users->save($user);

// update a user that already exists (id=1)
$user = $users->find(1);
if ($user->isFound()) {
    $user->old = 100;
    dump $user->save();
}

// remove a user (id=1)
$user = $users->entity();
$user->id = 1;
dump $users->remove(1);
// or $users->remove($user);

// remove a user that already exists (id=1)
$user = $users->find(1);
if ($user->isFound()) {
    dump $user->remove();
}

// remove users (id=1,2,3)
dump $users->removeAll([1,2,3]);