Database Query Builder

One of the coolest features of almost all frameworks is the query builder, but most of those query builders in other frameworks are not so easy to use or troubleshoot. Using query builder in Koldy, you'll finally see how easy it can be.

As you already know, every query builder should support basic database operations, also known as CRUD operations.

Select

Select statements can have really a lot of variations and it is really hard to cover all possible cases. So, lets start from basics.


$select Db::select(); // returns instance of \Koldy\Db\Select
$select->from('users');
echo 
$select;

Or, even shorter:


$select Db::select('users');
echo 
$select;

Both examples will print your query:


SELECT FROM users

What if you want to select only ID and username from the table, but just for users who are active and their ID is greater then 10 and their birthday is between 01/01/1980 and 01/01/1990:


$select Db::select('users');
$select
    
->field('id')
    ->
field('username')
    ->
where('status''active')
    ->
where('id''>'10)
    ->
whereBetween('birthday''1980-01-01''1990-01-01');

Beside ID and username, what if you want to get the user's last login time that is stored in another table?


$select Db::select();
$select
    
->from('users u')
    ->
field('u.id')
    ->
field('u.username')
    ->
where('u.status''active')
    ->
where('u.id''>'10)
    ->
whereBetween('u.birthday''1980-01-01''1990-01-01')

    ->
leftJoin('login_history lh''lh.user_id''=''u.id')
    ->
field('lh.login_time''time');

echo 
$select;

The example above will print this:


SELECT
    u
.id,
    
u.username,
    
lh.login_time as time
FROM
    users u
    LEFT JOIN login_history lh ON lh
.user_id u.id
WHERE
    
(u.status = :fu_status5)
    AND (
u.id > :fu_id6)
    AND (
u.birthday BETWEEN :fu_birthday7 AND :fu_birthday8)

Please notice that query you got above is query prepared for PDO object where query values are separated. If you want to see how query would look like with populated values, then please use debug() method.


echo $select->debug();

And you'll get this:


SELECT
    u
.id,
    
u.username,
    
lh.login_time as time
FROM
    users u
    LEFT JOIN login_history lh ON lh
.user_id u.id
WHERE
    
(u.status 'active')
    AND (
u.id 10)
    AND (
u.birthday BETWEEN '1980-01-01' AND '1990-01-01')

All queries will look nice if you build them with query builder. It is made that way so you can troubleshoot faster. If for any reason you want your query to be displayed in one line, then just pass true to debug() method: echo $select->debug(true):


SELECT u.idu.usernamelh.login_time as time FROM users u LEFT JOIN login_history lh ON lh.user_id u.id WHERE (u.status 'active') AND (u.id 10) AND (u.birthday BETWEEN '1980-01-01' AND '1990-01-01')

Aggregation

What if your users table contains users with three different statuses (e.g., active, inactive and unconfirmed) and you want to know the number of users per each status. You'll need to group them and count them.


$select Db::select('users');
$select
    
->field('status')
    ->
field('COUNT(*)''total')
    ->
groupBy('status')
    ->
orderBy('status''ASC');

SELECT
    status
,
    
COUNT(*) as total
FROM
    users
GROUP BY status
ORDER BY
    status ASC

Other aggregation functions can be used by the same way as COUNT example above.

More complex example:


$select Db::select('users');
$select
    
->field('status')
    ->
field('COUNT(*)''total')
    ->
field("SUM(IF(status != 'unconfirmed', 1, 0))"'ok_count')
    ->
groupBy('status')
    ->
having('ok_count''>'1)
    ->
having('COUNT(*)''>'0)
    ->
orderBy('status''ASC');

SELECT
    status
,
    
COUNT(*) as total,
    
SUM(IF(status != 'unconfirmed'10)) as ok_count
FROM
    users
GROUP BY status
HAVING
    ok_count 
1
    
AND COUNT(*) > '0'
ORDER BY
    status ASC

Insert

Simple insert:


$insert Db::insert('users');
$insert->add(array(
    
'username' => 'new_user',
    
'first_name' => 'John',
    
'last_name' => 'Doe',
    
'birthday' => '1990-01-01',
    
'status' => 'unconfirmed'
));
$insert->exec();

INSERT INTO users (username,first_name,last_name,birthday,status)
VALUES
    
('new_user','John','Doe','1990-01-01','unconfirmed')

Multiple insert in single query:


$insert Db::insert('users');

$insert->add(array(
    
'username' => 'new_user1',
    
'first_name' => 'John 1',
    
'middle_name' => null,
    
'last_name' => 'Doe 1',
    
'birthday' => '1990-01-01',
    
'status' => 'unconfirmed'
));

$insert->add(array(
    
'username' => 'new_user2',
    
'first_name' => 'John 2',
    
'middle_name' => 'the second',
    
'birthday' => '1990-01-01',
    
'status' => 'unconfirmed'
));

$insert->add(array(
    
'username' => 'new_user3',
    
'first_name' => 'John 2',
    
'birthday' => '1990-01-01',
    
'status' => 'unconfirmed'
));

$insert->exec();

INSERT INTO users (username,first_name,middle_name,last_name,birthday,status)
VALUES
    
('new_user1','John 1',NULL,'Doe 1','1990-01-01','unconfirmed'),
    (
'new_user2','John 2','the second',NULL,'1990-01-01','unconfirmed'),
    (
'new_user3','John 2',NULL,NULL,'1990-01-01','unconfirmed')

Copying (insert into select) statement:


$insert Db::insert('users_backup');
$insert->selectFrom(Db::select('users'));
$insert->exec();

INSERT INTO users_backup SELECT FROM users )

Copying (insert into select) statement with given fields to copy:


$insert Db::insert('users_backup');
$insert->fields(array('id''first_name''last_name'))
$insert->selectFrom(
    
Db::select('users')
    ->
fields(array('id''first_name''last_name'))
    ->
whereBetween('id'010000)
    ->
whereNotBetween('id'5001000)
);
$insert->exec();

INSERT INTO users_backup (id,first_name,last_name)
(
    
SELECT
        id
,
        
first_name,
        
last_name
    FROM
        users
    WHERE
        
(id BETWEEN '0' AND 10000)
        AND (
id NOT BETWEEN 500 AND 1000)
)

Update

Simple update:


$update Db::update('users');
$update->setValues(array(
    
'last_login' => Db::expr('NULL'),
    
'login_count' => 0
));
$update->whereBetween('id'100200)
    ->
orWhereBetween('id'500600);
$update->exec();

UPDATE users
SET
    last_login 
NULL,
    
login_count '0'
WHERE (id BETWEEN 100 AND 200)
    OR (
id BETWEEN 500 AND 600)

Delete

Simple delete:


$delete Db::delete('users');
$delete->whereBetween('id'100200)
    ->
orWhereBetween('id'500600);
$delete->exec();

DELETE FROM users
WHERE 
(id BETWEEN 100 AND 200)
    OR (
id BETWEEN 500 AND 600)
 
Heads up!
This page doesn't contain the examples of all methods. All methods are documented so feel free to explore the framework's code.
DB: BasicsDB: Models