Query Builder

Laravel's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operation in your application and works perfectly with all of Laravel's supported database systems.

Retrieving

All Rows From a Table

To retrieve all rows from a table using Laravel's Query Builder, you can use the table() method followed by the get() method. Here's how you can do it"

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

This retrieves all rows from the users table. The get() method returns a collection (Illuminate\Support\Collection) containing all the rows retrieved from the database.

You can then iterate over the $users collection to access individual rows and their attributes:

foreach ($users as $user) {
    echo $user->id . ' ' . $user->name . ' ' . $user->email;
}

This loop will iterate over each user in the $users collection and echo out their id, name, and email attributes.

Single Row / Column From a Table

To retrieve a single row or column from a table using Laravel's Query Builder, you can use the first() method to retrieve the first matching record. Here's how you can do it:

# Retrieving a Single Row:

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

// or for the id specific
$user = DB::table('users')->find(3);

This retrieves the first row from the users table where the id column equals 1. The second one is for id column only.

# Retrieving a Single Column:

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

This retrieves the value of the name column from the first row in the users table where the id column equals 1.

If the query does not return any results, null will be returned. Therefore it's good practice to check if the result is not null before accessing its properties or methods.

ex:

if ($user !== null) {
    echo $user->id . ' ' . $user->name . ' ' . $user->email;
} else {
    echo 'User not found.';
}

Retrieving a List of Column Values

To retrieve a list of column values form a table using Laravel's Query Builder, you can use the pluck() method. This method retrieves all the values of specified column from the table. Here's how you can do it:

$emails = DB::table('users')->pluck('email');

This retrieves all the values from the email column in the users table and returns them as a collection. If you want to retrieve the values as an array, you can call the toArray() method:

$emailArray = $emails->toArray();

Now, $emailArray contains all the email values from the users table.

If you want to limit the results or add conditions, you can chain additional methods before calling pluck(). For example, to retrieve emails only for users with a specific role:

$emails = DB::table('users')->where('role', 'admin')->pluck('email');

This retrieves the email values only for users with the role admin from the users table.

You can then iterate over the collection or array to access each value:

foreach ($emails as $email) {
    echo $email . '<br>';
}

This loop will output each email value retrieved from the users table.

Chunking

Chunking results in Laravel allows you to process a large number of database records in smaller, more manageable chunks, which can help prevent memory issues and improve performance. Laravel's Query Builder provides the chunk() method to facilitate this.

Here's how you can use chunking with Laravel's Query Builder:

DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    foreach ($users as $user) {
        // Process each user record
        echo $user->id . ' ' . $user->name . ' ' . $user->email . '<br>';
    }
});

In this example:

• We are querying the users table and ordering the results by the id column.
• The chunk() method is used to retrieve records in chunks of 100. You can adjust the chunk size as needed for your application.
• Inside the callback function, each chunk of user records is passed as an argument. We iterate over each user record and process it accordingly.

Aggregates

Aggregates are functions used to perform calculations on sets of data in a database table.

Here's how you can use aggregates in Laravel:

Count:

• Counts the number of rows in a table.

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

Sum:

• Calculates the sum of a numeric column in a table.

$totalAmount = DB::table('orders')->sum('amount');

Avg:

• Calculates the average value of a numeric column in a table.

$averagePrice = DB::table('products')->avg('price');

Min:

• Retrieves the minimum value of a column in a table.

$minAge = DB::table('users')->min('age');

Max:

• Retrieves the maximum value of a column in a table.

$maxSalary = DB::table('employees')->max('salary');

We can also combine aggregate functions with other query builder methods, such as where, groupBy, and having to perform more complex aggregate queries:

$totalSales = DB::table('orders')
                ->whereYear('order_date', 2023)
                ->sum('total_amount');

This example calculates the total sales amount for the year 2023 by summing the total_amount column from the orders table where the order_date column matches the year 2023.

Determining if Records Exist

In Laravel, you can determine if records exist in a database table using the exists() method provided by the Query Builder. This method checks if any records match the specified conditions and returns a boolean value indicates whether records exists or not.

Here's how you can use the exists() method:

if (DB::table('users')->where('email', 'singh@example.com')->exists()) {
    // Records exist
    echo "User with email 'singh@example.com' exists.";
} else {
    // No records found
    echo "User with email 'singh@example.com' does not exist.";
}

In this example:

• We are using theexists() method to check if there are any records in the users table where the email column matches singh@example.com.
• If records exist with the specified condition, it will return true, and the corresponding message will be echoed.
• If no records match the condition, it will return false, and the corresponding message will be echoed.

Select Statements

In Laravel, the Query Builder provides a variety of method for constructing SELECT statements to retrieve data from a database table.

Basic Select: We have seen this one above.

• Retrieves all columns from a table.

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

Select Specific Columns:

• Retrieves specific columns from a table.

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

Aliasing Columns:

• Aliases columns in the result set.

$users = DB::table('users')->select('id as user_id', 'name as user_name')->get();

Distinct Rows:

• Retrieves distinct rows from a table.

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

Adding Selects:

• Adds additional columns to the select statement.

$query = DB::table('users')->select('name');
$users = $query->addSelect('email')->get();

Conditional Clause

Conditional clauses allow you to build dynamic queries by adding conditions based on certain criteria. These clauses help construct SQL statements with flexibility, enabling you to tailor your queries to specific requirement.

Basic where Clause:

The where clause filters rows based on specific conditions.

$users = DB::table('users')->where('status', 'active')->get();

orWhere Clause:

The orWhere clause adds an alternative condition to the query.

$users = DB::table('users')->where('role', 'admin')->orWhere('role', 'superadmin')->get();

Insert Statements

You can use the Query Builder to perform INSERT operations into your database tables. The query Builder provides a convenient way to construct and execute INSERT statements. Here's how you can use it:

Inserting a Single Record:

DB::table('users')->insert([
    'name' => 'John Doe',
    'email' => 'john@example.com',
    'created_at' => now(),
    'updated_at' => now()
]);

This code snipped inserts a single record into the users table with the specified values for the name, email, created_at, and updated_at columns.

Inserting Multiple Records:

DB::table('users')->insert([
    ['name' => 'Jane Doe', 'email' => 'jane@example.com'],
    ['name' => 'Bob Smith', 'email' => 'bob@example.com']
]);

Here, we insert multiple records into the users table at once. Each array represents a separate record to be inserted, with values for the name and email columns.