Database Usage (Based on Laravel Database Component)

Fetch All Rows

<?php
namespace app\controller;

use support\Request;
use support\Db;

class UserController
{
    public function all(Request $request)
    {
        $users = Db::table('users')->get();
        return view('user/all', ['users' => $users]);
    }
}

Fetch Specific Columns

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

Fetch One Row

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

Fetch One Column

$titles = Db::table('roles')->pluck('title');

Use the specified id field values as keys

$roles = Db::table('roles')->pluck('title', 'id');

foreach ($roles as $id => $title) {
    echo $title;
}

Fetch Single Value (Field)

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

Distinct

$email = Db::table('user')->select('nickname')->distinct()->get();

Chunk Results

If you need to handle thousands of database records, reading all this data at once can be time-consuming and easily cause memory overflow; at this point, you might consider using the chunkById method. This method fetches a small chunk of the result set at a time and passes it to a closure function for processing. For example, we can chunk all data from the users table into small parts of 100 records at a time:

Db::table('users')->orderBy('id')->chunkById(100, function ($users) {
    foreach ($users as $user) {
        //
    }
});

You can terminate fetching chunks by returning false from the closure.

Db::table('users')->orderBy('id')->chunkById(100, function ($users) {
    // Process the records...

    return false;
});

Note: Do not delete data within the callback, as this may cause some records to be omitted from the result set.

Aggregation

The query builder also provides various aggregation methods, such as count, max, min, avg, sum, etc.

$users = Db::table('users')->count();
$price = Db::table('orders')->max('price');
$price = Db::table('orders')->where('finalized', 1)->avg('price');

Check if Records Exist

return Db::table('orders')->where('finalized', 1)->exists();
return Db::table('orders')->where('finalized', 1)->doesntExist();

Raw Expressions

Prototype

selectRaw($expression, $bindings = [])

Sometimes you may need to use raw expressions in your queries. You can create a raw expression using selectRaw():

$orders = Db::table('orders')
                ->selectRaw('price * ? as price_with_tax', [1.0825])
                ->get();

Similarly, methods like whereRaw(), orWhereRaw(), havingRaw(), orHavingRaw(), orderByRaw(), and groupByRaw() for raw expressions are also provided.

Db::raw($value) is also used to create a raw expression, but it does not support parameter binding, so care must be taken to avoid SQL injection issues.

$orders = Db::table('orders')
                ->select('department', Db::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > ?', [2500])
                ->get();

Join Statements

// join
$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();

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

// rightJoin
$users = Db::table('users')
            ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

// crossJoin    
$users = Db::table('sizes')
            ->crossJoin('colors')
            ->get();

Union Statements

$first = Db::table('users')
            ->whereNull('first_name');

$users = Db::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

Where Statements

Prototype

where($column, $operator = null, $value = null)

The first parameter is the column name, the second is any operator supported by the database system, and the third is the value to compare against the column.

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

// When the operator is equality, it can be omitted, making this expression equivalent to the above
$users = Db::table('users')->where('votes', 100)->get();

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

$users = Db::table('users')
                ->where('votes', '<>', 100)
                ->get();

$users = Db::table('users')
                ->where('name', 'like', 'T%')
                ->get();

You can also pass an array of conditions to the where function:

$users = Db::table('users')->where([
    ['status', '=', '1'],
    ['subscribed', '<>', '1'],
])->get();

The orWhere method accepts the same parameters as the where method:

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

You can pass a closure to the orWhere method as the first parameter:

// SQL: select * from users where votes > 100 or (name = 'Abigail' and votes > 50)
$users = Db::table('users')
            ->where('votes', '>', 100)
            ->orWhere(function($query) {
                $query->where('name', 'Abigail')
                      ->where('votes', '>', 50);
            })
            ->get();

The whereBetween / orWhereBetween methods validate if a field's value is between two given values:

$users = Db::table('users')
           ->whereBetween('votes', [1, 100])
           ->get();

The whereNotBetween / orWhereNotBetween methods verify if a field's value is outside of the given two values:

$users = Db::table('users')
                    ->whereNotBetween('votes', [1, 100])
                    ->get();

The whereIn / whereNotIn / orWhereIn / orWhereNotIn methods check if the field value is in the specified array:

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

The whereNull / whereNotNull / orWhereNull / orWhereNotNull methods verify if the specified field must be NULL:

$users = Db::table('users')
                    ->whereNull('updated_at')
                    ->get();

The whereNotNull method checks that the specified field must not be NULL:

$users = Db::table('users')
                    ->whereNotNull('updated_at')
                    ->get();

The whereDate / whereMonth / whereDay / whereYear / whereTime methods are used to compare field values with given dates:

$users = Db::table('users')
                ->whereDate('created_at', '2016-12-31')
                ->get();

The whereColumn / orWhereColumn methods are used to compare if two column values are equal:

$users = Db::table('users')
                ->whereColumn('first_name', 'last_name')
                ->get();

// You can also pass a comparison operator
$users = Db::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

// The `whereColumn` method can also accept arrays
$users = Db::table('users')
                ->whereColumn([
                    ['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at'],
                ])->get();

Parameter Grouping

// select * from users where name = 'John' and (votes > 100 or title = 'Admin')
$users = Db::table('users')
           ->where('name', '=', 'John')
           ->where(function ($query) {
               $query->where('votes', '>', 100)
                     ->orWhere('title', '=', 'Admin');
           })
           ->get();

Using whereExists

// select * from users where exists ( select 1 from orders where orders.user_id = users.id )
$users = Db::table('users')
           ->whereExists(function ($query) {
               $query->select(Db::raw(1))
                     ->from('orders')
                     ->whereRaw('orders.user_id = users.id');
           })
           ->get();

orderBy

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

Random Order

$randomUser = Db::table('users')
                ->inRandomOrder()
                ->first();

Random ordering can heavily impact server performance and is not recommended.

groupBy / having

$users = Db::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();
// You can pass multiple parameters to the groupBy method
$users = Db::table('users')
                ->groupBy('first_name', 'status')
                ->having('account_id', '>', 100)
                ->get();

offset / limit

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

Insertion

Insert a Single Row

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

Insert Multiple Rows

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

Auto-Increment ID

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

Note: When using PostgreSQL, the insertGetId method defaults to using id as the name of the auto-increment field. If you need to get an ID from another "sequence," you can pass the field name as the second parameter to insertGetId.

Update

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

Update or Insert

Sometimes you might want to update an existing record in the database, or create it if a matching record does not exist:

Db::table('users')
    ->updateOrInsert(
        ['email' => 'john@example.com', 'name' => 'John'],
        ['votes' => '2']
    );

The updateOrInsert method first attempts to locate a matching database record using the key-value pairs from the first parameter. If the record exists, the values in the second parameter are used to update the record. If no record is found, a new record will be inserted, with data being the combination of both arrays.

Increment & Decrement

Both methods accept at least one parameter: the column to be modified. The second parameter is optional, specifying the amount by which to increment or decrement the column:

Db::table('users')->increment('votes');

Db::table('users')->increment('votes', 5);

Db::table('users')->decrement('votes');

Db::table('users')->decrement('votes', 5);

You can also specify which fields to update during the operation:

Db::table('users')->increment('votes', 1, ['name' => 'John']);

Deletion

Db::table('users')->delete();

Db::table('users')->where('votes', '>', 100)->delete();

If you need to clear a table, you can use the truncate method, which removes all rows and resets the auto-increment ID to zero:

Db::table('users')->truncate();

Transactions

See Database Transactions

Pessimistic Locking

The query builder also includes some functions that help you implement "pessimistic locking" in select syntax. To implement a "shared lock" in your query, you can use the sharedLock method. A shared lock prevents the selected data columns from being tampered with until the transaction is committed:

Db::table('users')->where('votes', '>', 100)->sharedLock()->get();

Alternatively, you can use the lockForUpdate method. Using the "update" lock prevents rows from being modified or selected by other shared locks:

Db::table('users')->where('votes', '>', 100)->lockForUpdate()->get();

Debugging

You can use the dd or dump method to output query results or SQL statements. The dd method displays debug information and then halts the request. The dump method also displays debug information but will not stop the request:

Db::table('users')->where('votes', '>', 100)->dd();
Db::table('users')->where('votes', '>', 100)->dump();

Note
Debugging requires the installation of symfony/var-dumper, install it with the command composer require symfony/var-dumper.