Query Builder

Using

// use and init with exists $db
use \Oppa\Query\Builder as QueryBuilder;
$qb = new QueryBuilder($db->getLink());

// set target table
$qb->setTable('users');

// SELECT * FROM users
$qb->select('*');                               
// SELECT * FROM users WHERE id=1
$qb->select('*')->where('id=?', [1]);           
// SELECT * FROM users WHERE id=1 LIMIT 1
$qb->select('*')->where('id=?', [1])->limit(1); 
// SELECT id, name FROM users WHERE name LIKE '%Ker\"em%'
$qb->select('id,name')->whereLike('name LIKE ?', ['%Ker"em%']);
// SELECT id, name FROM users WHERE (id LIKE '2%' OR name LIKE '%Ke\\_rem%')
$qb->select('id,name')->whereLike('(id LIKE ? OR name LIKE ?)', ['2%', '%Ke_rem%']);

Execution Methods

// execute query
$result = $qb->execute();

// get one row
$result = $qb->get();

// get all rows
$result = $qb->getAll();

Modifier Methods

// INSERT INTO users (`name`, `old`) VALUES ('Ali', 25)
$qb->insert(['name' => 'Ali', 'old' => 25]);
// INSERT INTO users (`name`, `old`) VALUES ('Ali', 25), ('Veli', 29)
$qb->insert([['name' => 'Ali', 'old' => 25], ['name' => 'Veli', 'old' => 29]]);

// UPDATE users SET `old` = 100 WHERE id > 30 LIMIT 1
$qb->update(['old' => 100])->where('id > ?', [30])->limit(1);
// UPDATE users SET `old` = 100 WHERE id > 30 ORDER BY id DESC LIMIT 1
$qb->update(['old' => 100])->where('id > ?', [30])->orderBy('id DESC')->limit(1);

// DELETE FROM users WHERE id > 30 LIMIT 1
$qb->delete()->where('id > ?', [30])->limit(1);
// DELETE FROM users WHERE id > 30 ORDER BY id DESC LIMIT 1
$qb->delete()->where('id > ?', [30])->orderBy('id DESC')->limit(1);
// DELETE FROM users WHERE id > 30 ORDER BY id DESC LIMIT 1
$qb->delete()->where('id > ?', [30])->orderBy('id', $qb::OP_DESC)->limit(1);
// DELETE FROM users WHERE id BETWEEN 931 AND 932 LIMIT 10
$qb->delete()->whereBetween('id', [931,932])->limit(10);
// DELETE FROM users WHERE id in(931, 932) LIMIT 10
$qb->delete()->where('id in(?)', [[931,932]])->limit(10);

Let's build some complex query.

Tip: Use joinUsing() or joinLeftUsing() if USING(foo) needed instead of ON (foo=bar).

// set target table
$qb->setTable('users u');

// build query
$qb->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)
;

Gives the result below.

SELECT 
    u.*
    , us.score
    , ul.login
    , sum(us.score) sum_score 
FROM users u 
JOIN users_score us ON us.user_id=u.id 
LEFT JOIN users_login ul ON ul.user_id=u.id 
WHERE u.id IN(1,2,3) AND u.old BETWEEN 30 AND 50 AND ul.login IS NOT NULL 
GROUP BY u.id 
HAVING sum_score <= 30 
ORDER BY old 
LIMIT 0,10