Laravel Database Conditional Clauses – The Laravel Database Conditional Clauses is used to apply a query only when something else is true.

Laravel Database Conditional Clauses.

Let us understand how to use Laravel Database Conditional Clauses.

Function:-

There are some followings function available in Laravel Database Conditional Clauses.

Conditional Clauses

Sometimes you want clauses to apply a query only when something else is true. Like, you can only want to apply where statement if a given input value is present on the incoming request.

$role = $request->input('role');

$users = DB::table('users')
                ->when($role, function ($query) use ($role) {
                    return $query->where('role_id', $role);
                })
                ->get();

The when method is only execute when the first parameter is true. If the first parameter is false, the Closure will not be executed.

$sortBy = null;

$users = DB::table('users')
                ->when($sortBy, function ($query) use ($sortBy) {
                    return $query->orderBy($sortBy);
                }, function ($query) {
                    return $query->orderBy('name');
                })
                ->get();

Laravel Database Ordering, Grouping, Limit, & Offset – The Laravel Database Ordering, Grouping, Limit, & Offset is mostly used to sort the result to the query.

Laravel Database Ordering, Grouping, Limit, & Offset.

Let us understand how to use Laravel Database Ordering, Grouping, Limit, & Offset.

Function:-

There are some followings function available in Laravel Database Ordering, Grouping, Limit, & Offset.

orderBy

The orderBy method allows you to sort the result. The first argument to the orderBy method should be the column which you want to sort. The second argument is asc and desc order.

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

latest / oldest

The latest and oldest method allow you to order result by date. Result will be ordered by the created_at column.

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

inRandomOrder

The inRandomOrder method can be used to sort the query result randomly.

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

groupBy / having / havingRaw

The groupBy and having method can be used to group the query result. The having method’s signature is similar to that of the where method.

$users = DB::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();

The havingRaw method can be used to set a raw string as the value of the having clause.

$users = DB::table('orders')
                ->select('department', DB::raw('SUM(price) as total_sales'))
                ->groupBy('department')
                ->havingRaw('SUM(price) > 2500')
                ->get();

skip / take

To set limit the number of result return from the query or to skip a given number of result in the query. You can use skip and take method.

$users = DB::table('users')->skip(10)->take(5)->get();

You can also use the limit and offset method.

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

Laravel Database Where Clauses – The Laravel Database Where Clauses is used to compare that a column is equal to given value.

Laravel Database Where Clauses.

Let us understand how to use Laravel Database Where Clauses.

Function:-

There are some followings methods available in Laravel Database Where Clauses.

Simple Where Clauses

You can use the where method on a query builder instance to add where clauses to the query. The where method required three arguments. First is name of the column. Second argument is an operator. finally third argument is the value to evaluate against the column.

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

If you want simply to verify that a column is equal to given value we can use like this:-

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

You can also use like this:-

$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 condition to the where method.

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

Or Statements

The orWhere method accept the same arguments as the where method.

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

Additional Where Clauses

whereBetween

The whereBetween method verify that a column value is between two values.

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

whereNotBetween

The whereNotBetween verify that a column value lies outside of two values.

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

whereIn / whereNotIn

The whereIn method verify that a given column values is contained within the given array.

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

The whereNotIn method verify that a given column values is not contained within the given array.

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

whereNull / whereNotNull

The whereNull method verify that the value of given column is NULL

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

The whereNotNull method verify that the value of given column is not NULL

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

whereDate / whereMonth / whereDay / whereYear

The whereDate method can be used to compare column value against a date.

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

The whereMonth method can be used to compare column value against a month of the year.

$users = DB::table('users')
                ->whereMonth('created_at', '12')
                ->get();

The whereDay method can be used to compare column value against a specific day of a month.

$users = DB::table('users')
                ->whereDay('created_at', '31')
                ->get();

The whereYear method can be used to compare column value against a specific year.

$users = DB::table('users')
                ->whereYear('created_at', '2016')
                ->get();

whereColumn

The whereColumn method can be used to verify that two column are equals.

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

You can also pass by using comparison operator.

$users = DB::table('users')
                ->whereColumn('updated_at', '>', 'created_at')
                ->get();

The whereColumn method can also be passed an array of multiple condition.

$users = DB::table('users')
                ->whereColumn([
                    ['first_name', '=', 'last_name'],
                    ['updated_at', '>', 'created_at']
                ])->get();

Parameter Grouping

Sometimes you may need to create more advanced where clauses such as “where exists” clauses or nested parameter groupings.

DB::table('users')
            ->where('name', '=', 'John')
            ->orWhere(function ($query) {
                $query->where('votes', '>', 100)
                      ->where('title', '<>', 'Admin');
            })
            ->get();

As we can see, passing the closure into the orWhere method instructs the query builder to begin a constraint group.

select * from users where name = 'John' or (votes > 100 and title <> 'Admin')

Where Exists Clauses

This method allow you to write where exists SQL clauses. The whereExists
method accepts a closure argument, which will recieve a query builder instance allowing you to define the query that should be placed inside of the “exists” clause.

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

This query will be produce the following SQL.

select * from users
where exists (
    select 1 from orders where orders.user_id = users.id
)

JSON Where Clauses

Laravel supports querying JSON column types on databases which provide support for JSON column types. Currently, this includes MySQL 5.7 and Postgres.

$users = DB::table('users')
                ->where('options->language', 'en')
                ->get();

$users = DB::table('users')
                ->where('preferences->dining->meal', 'salad')
                ->get();

Laravel Database Joins And Unions – The Laravel Database Joins And Unions is used to combine the two table in the database.

Laravel Database Joins And Unions.

Let us understand how to use Laravel Database Joins And Unions.

Function:-

There are some followings function available in Laravel Database Joins And Unions.

• 1. Joins.
• 2. Unions.

1. Joins.

Inner Join Clause

The query builder can also be used to write join statements. To perform basic inner join, you can use the join method on query builder. The first argument describe to the join method is the name of the table you need to join. The remaining argument specify column restriction for the join. Then you can see, you can join multiple table in a single query.

$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 Clause

If you want to perform a Left join altenate of the inner join, you can use leftJoin method.

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

Cross Join Clause

To perform cross join in two table, You can use crossJoin method with the name of the table you wnat to cross to. Cross join generate the relayion between the first table and the join table.

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

Advanced Join Clauses

You can also use more advanced join clauses like this:-

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
        })
        ->get();

If you want to use a where style clause on your joins, you can use the where
and orWhere methods on a join.

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')
                 ->where('contacts.user_id', '>', 5);
        })
        ->get();

2. Unions.

The query builder also provides a quick way to union two query together. you can create an initial query and use the union method to union it with a second query.

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

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

Laravel Database Selects And Raw Expressions – The Laravel Database Selects And Raw Expressions both are difference select method is used to select particular column from the database and raw is used to inject expression into the query as string.

Laravel Database Selects And Raw Expressions.

Let us understand how to use Laravel Database Selects And Raw Expressions.

Function:-

There are some followings function available in Laravel Database Selects And Raw Expressions.

• 1. Selects.
• 2. Raw Expressions.

1. Selects.

Specifying A Select Clause

Yes you may not always want to select all columns from the database table. By using 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();

If you want to add a column to its existing select clause, you may use the addselect
method.

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

$users = $query->addSelect('age')->get();

2. Raw Expressions.

Sometime we have to need use a raw expression in a query. It will be injected expression into the query as a string. To create a raw expression, you can use DB::raw method.

$users = DB::table('users')
                     ->select(DB::raw('count(*) as user_count, status'))
                     ->where('status', '<>', 1)
                     ->groupBy('status')
                     ->get();

Laravel Database Query Builder – The Laravel Database Query Builder is used to provides a easy way to creating and running database queries.

Laravel Database Query Builder.

Let us understand how to use Laravel Database Query Builder.

Function:-

There are some followings function available in Laravel Database Query Builder.

• 1. Introduction.
• 2. Retrieving Results.

1. Introduction.

Laravel database query builder provides a simple and fluent interface to creating and running database queries. It can be used to perform most database operation in your application and perform on all database supported system.

Query builder uses PDO parameter binding to protect your application against SQL injection attack and no need to clean string being passed as bindings.

2. Retrieving Results.

Retrieving All Rows From A Table

You can use the table method on the DB facade to start a query. The table method returns a fluent query builder instance for the given table, allowing you to chain more constraints onto the query and then finally get the results using the get method.

get();

        return view('user.index', ['users' => $users]);
    }
}

The get method returns an Illuminate/support/collection containing the result
where each result is an instance of the PHP StdClass object.

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

Retrieving A Single Row / Column From A Table

If you just need to get a single row from the database table, you can use the first
method. this method will return a single Stdclass object.

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

echo $user->name;

If you do not want to get entire row, you can get exact a single value from a record using the value method.

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

Retrieving A List Of Column Values

If you want to retrieve a collection containing the values of a single column, you can use the pluck method.

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

foreach ($titles as $title) {
    echo $title;
}

You can also specify a custom key column for the returned collection.

$roles = DB::table('roles')->pluck('title', 'name');

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

Chunking Results

If you need to work with thousands of data records, you can use chunk method. This method get a small chunk of the result at a time and feeds each chunk into the closure for processing.

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

You can stop further chunks from being processed by returning false from the closure.

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

    return false;
});

Aggregates

The query builder also provides a variety of aggregate method like count, max, min, avg and sum.

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

$price = DB::table('orders')->max('price');

You can also combine these method with other clauses.

$price = DB::table('orders')
                ->where('finalized', 1)
                ->avg('price');

Laravel Database: Getting Started – The Laravel Database is used to interacting with database.

Laravel Database: Getting Started.

Let us understand how to use laravel Database.

Function:-

There are some followings function available in laravel Database.

• 1. Introduction.
• 2. Running Raw SQL Queries.
• 3. Database Transactions.

1. Introduction.

Laravel makes interacting with databases extremly simple across a variety of database backend using either raw SQL. Laravel supports Four databases.

MySql

Postgres

SQLite

SQL Server

Configuration

The database configuration for your application is placed at config/database.php. In this file you can define all of your database connection and specify which connection should be used by default.

SQLite Configuration

After creating a new SQLite database using a command as touch database/database.sqlite,
you may configure your enviroment variables to point to this created database.

DB_CONNECTION=sqlite
DB_DATABASE=/absolute/path/to/database.sqlite

SQL Server Configuration

It supports SQL Server out of the box, you will need to add the connection configuration for the database to your config/database.php configuration file.

'sqlsrv' => [
    'driver' => 'sqlsrv',
    'host' => env('DB_HOST', 'localhost'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
],

Read & Write Connections

Sometimes you want to use one database connection for select statement and another for INSERT, UPDATE and DELETE statement.

Lets see how read/write connection should be configured.

'mysql' => [
    'read' => [
        'host' => '192.168.1.1',
    ],
    'write' => [
        'host' => '196.168.1.2'
    ],
    'driver'    => 'mysql',
    'database'  => 'database',
    'username'  => 'root',
    'password'  => '',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix'    => '',
],

Using Multiple Database Connections

When using multiple connection, you can access each connection via the connection method on the DB facade. The name passed to the connection method should correspond to one of the connection listed in your config/database.php file.

$users = DB::connection('foo')->select(...);

You can also access the raw using like this:-

$pdo = DB::connection()->getPdo();

2. Running Raw SQL Queries.

You have configured your database connection, you can run queries using the DB facade. This facade provides methods for each type of query such as:select, update insert delete and statement.

Running A Select Query

To run a basic query, you can use the select method on the DB facade.

 $users]);
    }
}

The select method will always return an array of results.

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

Using Named Bindings

Instead of using ? to represent your parameter bindings, you can execute a query using named bindings.

$results = DB::select('select * from users where id = :id', ['id' => 1]);

Running An Insert Statement

To execute an insert statement, we can use insert method on the DB
facade.

DB::insert('insert into users (id, name) values (?, ?)', [1, 'Tutorialsplane.com']);

Running An Update Statement

The update method is used to update record in the database.

$affected = DB::update('update users set votes = 100 where name = ?', ['SolidCoupon']);

Running A Delete Statement

The delete method is used to delete record from the database.

$deleted = DB::delete('delete from users');

Running A General Statement

Some database statements do not return any value, then you can use statement method.

DB::statement('drop table users');

Listening For Query Events

If you want to recieve each SQL query executed by your application, you can use listen
method.

sql
            // $query->bindings
            // $query->time
        });
    }

    public function register()
    {
        //
    }
}

3. Database Transactions.

You can use the transaction method on the facade to run a set of operations within a database transaction. If an exception is thrown within the transaction closure, the transaction will automatically be rolled back. If the closure executes successfully, the transaction will automatically be committed. You don’t need to worry about manually rolling back or committing while using the transaction method.

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);

    DB::table('posts')->delete();
});

Manually Using Transactions

If you want to start a transaction manually and have complete control over rollbacks and commits, you can use the beginTransaction method.

DB::beginTransaction();

You can rollback the transaction via the rollback method.

DB::rollBack();

Finally, you can commit a transaction via the commit method.

DB::commit();

Laravel Resetting Passwords – The Laravel Resetting Passwords provide a way to user to reset their password.

Laravel Resetting Passwords.

Let us understand how to use laravel Resetting Passwords.

Function:-

There are some followings function available in laravel Resetting Passwords.

• 1. Introduction.
• 2. Database Considerations.
• 3. Routing.
• 4. Views.
• 5. After Resetting Passwords.
• 6. Customization.

1. Introduction.

Mostly web application provide a way for users to rest their passord, if they forgotten password. Even forcing you to re-implement this on each application. Its provides a method for sending password reminders and performing password resets.

2. Database Considerations.

First verify that your App/User model implements the Illuminate/Contracts/Auth/CanResetPassword contract. The App/User model already included with the framework and use the Illuminate/Auth/Passwords/CanResetPassword trait to include the methods.

Generating The Reset Token Table Migration

Table must be created to store the password reset tokens. The migration for this table is included with Laravel and resides in the database/migrations directory.

php artisan migrate

3. Routing.

All the routes needed to perform passwords resets can be generated using the make:auth Artisan command.

php artisan make:auth

4. Views.

Laravel will generate all of the necessary view for password reset when the make:auth command is executed. Views are placed in resources/views/auth/passwords. You are free to customize them as need for your application.

5. After Resetting Passwords.

You have defined the routes and views to reset your users password, you can simply access the route in your browser at /password/reset.

After a password is reset, the user will automatically be logged into the application and redirected to /home.

protected $redirectTo = '/dashboard';

6. Customization.

Authentication Guard Customization

In your auth.php configuration file, you can configure multiple Guards, which can be used to define auth behaviour for multiple user tables. You can customize the included ResetPasswordController to use the guard of your choice by overriding the Gaurd
method on the controller.

use Illuminate\Support\Facades\Auth;

protected function guard()
{
    return Auth::guard('guard-name');
}

Password Broker Customization

In your auth.php configuration file, you can configure multiple password brokers which can be used to reset password on multiple user tables.

use Illuminate\Support\Facades\Password;

protected function broker()
{
    return Password::broker('name');
}

Reset Email Customization

You can easily modify the notification class used to send the password reset link to the user.First override the SendPasswordResetNotification on your user model. Then you can send the notification using any notification class you choose. The password reset $token is the first argument recieved by the method.

public function sendPasswordResetNotification($token)
{
    $this->notify(new ResetPasswordNotification($token));
}

Laravel Hashing – The Laravel Hashing is used to provide a secure Bcrypt hashing for storing user password. When we create authentication we use hash facade for secure the password.

Laravel Hashing.

Let us understand how to use laravel Hashing.

Function:-

There are some followings function available in laravel Hashing.

• 1. Introduction.
• 2. Basic Usage.

1. Introduction.

The Laravel hash facade provides secure authentication for storing user password. If you are using the built in LoginController and RegisterController classes which is included with laravel application. They will automatically use Bcrypt for registration and authentication.

2. Basic Usage.

You can hash a password by calling the make method on the hash facade.

user()->fill([
            'password' => Hash::make($request->newPassword)
        ])->save();
    }
}

Verifying A Password Against A Hash

The check method is used to verify a given text-plain string correspond to a given hash. If you are using LoginController, you do not need to use this directory, as this controller automatically call this method.

if (Hash::check('plain-text', $hashedPassword)) {
    // The passwords match...
}

Checking If A Password Needs To Be Rehashed

The needsRehash is used to determine if the work factor used by the hasher has changed since the password was hashed.

if (Hash::needsRehash($hashed)) {
    $hashed = Hash::make('plain-text');
}