Database

The framework uses roolith/database under the hood. The connection is created from the database key in config/config.php, so there is no manual connect step inside the framework.

Inside a Model you get the connection and table instances through Model::raw() and Model::orm(). This page documents the full driver API available on them.

The Connection

use App\Models\User;

$db = User::raw(); // database connection
$users = User::orm(); // table instance for the users table

Set database to null in config/config.php if your application does not need a database.

Raw Query

// Get all users
$users = $db->query("SELECT * FROM users")->get();
print_r($users);

// Get total record of users table
$total = $db->query("SELECT id FROM users")->count();

Select

$db->table('users')->select([
    'field' => ['name', 'email'],
    'condition' => 'WHERE id > 0',
    'limit' => '0, 10',
    'orderBy' => 'name',
    'groupBy' => 'name',
])->get();

Get usernames only.

$usernames = $db->table('users')->select([
    'field' => 'name',
])->get();

Search with the LIKE operator.

$db->table('users')->where('name', '%Hadi%', 'LIKE')->get();

Get a record by primary key.

$db->table('users')->find(1);

Pluck fields from the result.

$db->table('users')->pluck(['name', 'email']);

Insert

$result = $db->table('users')->insert(
    ['name' => 'Brannon Bruen', 'email' => 'bschmeler@pacocha.net']
);

print_r($result->success());

Insert only when the supplied email does not exist in the users table.

$result = $db->table('users')->insert(
    ['name' => 'John doe', 'email' => 'john@email.com'],
    ['email']
);

Response methods:

$result->affectedRow();
$result->insertedId();
$result->isDuplicate();
$result->success();

Update

$result = $db->table('users')->update(
    ['name' => 'Habib Hadi', 'email' => 'john@email.com'],
    ['id' => 1]
);

or with a raw condition.

$result = $db->table('users')->update(
    ['name' => 'Habib Hadi', 'email' => 'john@email.com'],
    'id = 1'
);

Update the username only if nobody else is using it.

$result = $db->table('users')->update(
    ['username' => 'johndoe'],
    ['id' => 4],
    ['username']
);

Response methods:

$result->affectedRow();
$result->isDuplicate();
$result->success();

Delete

$result = $db->table('users')->delete(['id' => 4]);

Response methods:

$result->affectedRow();
$result->success();

Pagination

$total = $db->query("SELECT id FROM users")->count();
$result = $db->query("SELECT * FROM users")->paginate([
    'perPage' => 5,
    'pageUrl' => 'http://domain.com',
    'primaryColumn' => 'id',
    'pageParam' => 'page',
    'total' => $total,
]);

A shorter version, perPage defaults to 20.

$result = $db->query("SELECT * FROM users")->paginate([
    'perPage' => 5,
    'total' => $total,
]);

Get the pagination details.

print_r($result->getDetails());

{
    "total": 50,
    "perPage": 15,
    "currentPage": 1,
    "lastPage": 4,
    "firstPageUrl": "http://domain.com?page=1",
    "lastPageUrl": "http://domain.com?page=4",
    "nextPageUrl": "http://domain.com?page=2",
    "prevPageUrl": null,
    "path": "http://domain.com",
    "from": 1,
    "to": 15,
    "data": [
        // records
    ]
}

Debug Mode

Once debug mode is active, the executed query string is shown.

$db->debugMode()->table('users')->find(1);