⬡ Lacelium
⌘K
Version 2.x ▾

Query Builder

Introduction

Lacelium's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works perfectly on all supported database systems (SQLite, MySQL, MariaDB, and PostgreSQL).

The query builder uses PDO parameter binding to protect your application against SQL injection attacks. There is no need to clean or sanitize strings being passed to the query builder as query bindings.

Running Database Queries

You can begin a fluent query using the table method on the global db() helper or the Illuminate\Support\Facades\DB facade:

Retrieving All Rows From a Table

use Lacelium\Core\Database\DB;

$users = DB::table('users')->get();

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

Retrieving a Single Row / Column

If you just need to retrieve a single row from a database table, you may use the first method:

$user = DB::table('users')->where('name', 'John')->first();

return $user->email;

If you don't need an entire row, you may extract a single value from a record using the value method:

$email = DB::table('users')->where('name', 'John')->value('email');

Retrieving a List of Column Values

To retrieve a collection containing the values of a single column, use the pluck method:

$titles = DB::table('tasks')->pluck('title');

Select Statements

You may not always want to select all columns from a database table. Using the select method, you can specify a custom "select" clause for the query:

$users = DB::table('users')
    ->select('name', 'email as user_email')
    ->get();

The distinct method allows you to force the query to return distinct results:

$users = DB::table('users')->distinct()->get();

Joins

The query builder may also be used to add join clauses to your queries:

Inner Join Clause

$users = DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'contacts.phone', 'orders.price')
    ->get();

Left Join / Cross Join Clauses

$users = DB::table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->get();

Where Clauses

You may use the query builder's where method to add "where" clauses to the query. The most basic call to where requires three arguments: the column, the operator, and the value:

$users = DB::table('users')
    ->where('votes', '=', 100)
    ->where('age', '>', 35)
    ->get();

// For equality, the operator may be omitted:
$users = DB::table('users')->where('votes', 100)->get();

Or Where & Where Between

// Or where
$users = DB::table('users')
    ->where('votes', '>', 100)
    ->orWhere('name', 'John')
    ->get();

// Where between
$users = DB::table('users')
    ->whereBetween('votes', [1, 100])
    ->get();

// Where in
$users = DB::table('users')
    ->whereIn('id', [1, 2, 3])
    ->get();

Ordering, Grouping & Limit

The orderBy method allows you to sort the results of the query by a given column:

$users = DB::table('users')
    ->orderBy('name', 'desc')
    ->get();

$latestUser = DB::table('users')
    ->latest()
    ->first();

To limit the number of results returned or skip a given number of results:

$users = DB::table('users')
    ->offset(10)
    ->limit(5)
    ->get();

Inserts, Updates & Deletes

Inserts

DB::table('users')->insert([
    'email' => 'kayla@example.com',
    'votes' => 0
]);

$id = DB::table('users')->insertGetId([
    'email' => 'john@example.com',
    'votes' => 0
]);

Updates

$affected = DB::table('users')
    ->where('id', 1)
    ->update(['votes' => 1]);

// Increment / Decrement
DB::table('users')->increment('votes');
DB::table('users')->decrement('votes', 5);

Deletes

$deleted = DB::table('users')->where('votes', '<', 50)->delete();

// Truncate table
DB::table('users')->truncate();

Next Steps