laravel Eloquent

Eloquent ORMはLaravelに含まれており、美しくシンプルなアクティブレコードによるデーター操作の実装です。 それぞれのデータベーステーブルは関連する「モデル」と結びついている

The Eloquent ORM included with Laravel provides a beautiful, simple ActiveRecord implementation for working with your database. Each database table has a corresponding “Model” which is used to interact with that table. Models allow you to query for data in your tables, as well as insert new records into the table.

Before getting started, be sure to configure a database connection in config/database.php. For more information on configuring your database, check out the documentation.

Defining Models
To get started, let’s create an Eloquent model. Models typically live in the app directory, but you are free to place them anywhere that can be auto-loaded according to your composer.json file. All Eloquent models extend Illuminate\Database\Eloquent\Model class.

The easiest way to create a model instance is using the make:model Artisan command:

php artisan make:model Flight
php artisan make:model Flight --migration
php artisan make:model Flight -m

Eloquent Model Conventions
Now, let’s look at an example Flight model, which we will use to retrieve and store information from our flights database table:

Table Names
Note that we did not tell Eloquent which table to use for our Flight model. By convention, the “snake case”, plural name of the class will be used as the table name unless another name is explicitly specified. So, in this case, Eloquent will assume the Flight model stores records in the flights table. You may specify a custom table by defining a table property on your model:

Primary Keys
Eloquent will also assume that each table has a primary key column named id. You may define a protected $primaryKey property to override this convention.

In addition, Eloquent assumes that the primary key is an incrementing integer value, which means that by default the primary key will be cast to an int automatically. If you wish to use a non-incrementing or a non-numeric primary key you must set the public $incrementing property on your model to false. If your primary key is not an integer, you should set the protected $keyType property on your model to string.

Timestamps
By default, Eloquent expects created_at and updated_at columns to exist on your tables. If you do not wish to have these columns automatically managed by Eloquent, set the $timestamps property on your model to false:

Retrieving Models
Once you have created a model and its associated database table, you are ready to start retrieving data from your database. Think of each Eloquent model as a powerful query builder allowing you to fluently query the database table associated with the model. For example:

$flights = App\Flight::all();

foreach ($flights as $flight) {
    echo $flight->name;
}

Retrieving Single Models / Aggregates
Of course, in addition to retrieving all of the records for a given table, you may also retrieve single records using find or first. Instead of returning a collection of models, these methods return a single model instance:

Inserting & Updating Models
Inserts
To create a new record in the database, create a new model instance, set attributes on the model, then call the save method:

namespace App\Http\Controllers;

use App\Flight;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

class FlightController extends Controller
{
    /**
     * Create a new flight instance.
     *
     * @param  Request  $request
     * @return Response
     */
    public function store(Request $request)
    {
        // Validate the request...

        $flight = new Flight;

        $flight->name = $request->name;

        $flight->save();
    }
}

In this example, we assign the name parameter from the incoming HTTP request to the name attribute of the App\Flight model instance. When we call the save method, a record will be inserted into the database. The created_at and updated_at timestamps will automatically be set when the save method is called, so there is no need to set them manually.

Updates
The save method may also be used to update models that already exist in the database. To update a model, you should retrieve it, set any attributes you wish to update, and then call the save method. Again, the updated_at timestamp will automatically be updated, so there is no need to manually set its value:

Interacting With Redis

Interacting With Redis
You may interact with Redis by calling various methods on the Redis facade. The Redis facade supports dynamic methods, meaning you may call any Redis command on the facade and the command will be passed directly to Redis. In this example, we will call the Redis GET command by calling the get method on the Redis facade

namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\Redis;
class UserController extends Controller
{
    /**
     * Show the profile for the given user.
     *
     * @param  int  $id
     * @return Response
     */
    public function showProfile($id)
    {
        $user = Redis::get('user:profile:'.$id);
        return view('user.profile', ['user' => $user]);
    }
}

Of course, as mentioned above, you may call any of the Redis commands on the Redis facade. Laravel uses magic methods to pass the commands to the Redis server, so pass the arguments the Redis command expects:

Wildcard Subscriptions
Using the psubscribe method, you may subscribe to a wildcard channel, which may be useful for catching all messages on all channels. The $channel name will be passed as the second argument to the provided callback Closure:

Redis::psubscribe(['*'], function ($message, $channel) {
    echo $message;
});

Redis::psubscribe(['users.*'], function ($message, $channel) {
    echo $message;
});

Laravel Redis

Introduction
Redis is an open source, advanced key-value store. It is often referred to as a data structure server since keys can contain strings, hashes, lists, sets, and sorted sets.
Before using Redis with Laravel, you will need to install the predis/predis package via Composer:

composer require predis/predis

Alternatively, you may install the PhpRedis PHP extension via PECL. The extension is more complex to install but may yield better performance for applications that make heavy use of Redis.

Configuration
The Redis configuration for your application is located in the config/database.php configuration file. Within this file, you will see a redis array containing the Redis servers utilized by your application:

'redis' => [

    'client' => 'predis',

    'default' => [
        'host' => env('REDIS_HOST', 'localhost'),
        'password' => env('REDIS_PASSWORD', null),
        'port' => env('REDIS_PORT', 6379),
        'database' => 0,
    ],

],

Laravel Database: Seeding

Introduction
Laravel includes a simple method of seeding your database with test data using seed classes. All seed classes are stored in the database/seeds directory. Seed classes may have any name you wish, but probably should follow some sensible convention, such as UsersTableSeeder, etc. By default, a DatabaseSeeder class is defined for you. From this class, you may use the call method to run other seed classes, allowing you to control the seeding order.

Writing Seeders
To generate a seeder, execute the make:seeder Artisan command. All seeders generated by the framework will be placed in the database/seeds directory:

php artisan make:seeder UsersTableSeeder

A seeder class only contains one method by default: run. This method is called when the db:seed Artisan command is executed. Within the run method, you may insert data into your database however you wish. You may use the query builder to manually insert data or you may use Eloquent model factories.

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;

class DatabaseSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        DB::table('users')->insert([
            'name' => str_random(10),
            'email' => str_random(10).'@gmail.com',
            'password' => bcrypt('secret'),
        ]);
    }
}

Using Model Factories
Of course, manually specifying the attributes for each model seed is cumbersome. Instead, you can use model factories to conveniently generate large amounts of database records. First, review the model factory documentation to learn how to define your factories. Once you have defined your factories, you may use the factory helper function to insert records into your database.

For example, let’s create 50 users and attach a relationship to each user:

public function run()
{
    factory(App\User::class, 50)->create()->each(function ($u) {
        $u->posts()->save(factory(App\Post::class)->make());
    });
}

Calling Additional Seeders
Within the DatabaseSeeder class, you may use the call method to execute additional seed classes. Using the call method allows you to break up your database seeding into multiple files so that no single seeder class becomes overwhelmingly large. Pass the name of the seeder class you wish to run:

public function run()
{
    $this->call([
        UsersTableSeeder::class,
        PostsTableSeeder::class,
        CommentsTableSeeder::class,
    ]);
}

Running Seeders
Once you have written your seeder, you may need to regenerate Composer’s autoloader using the dump-autoload command:

composer dump-autoload

Now you may use the db:seed Artisan command to seed your database. By default, the db:seed command runs the DatabaseSeeder class, which may be used to call other seed classes. However, you may use the –class option to specify a specific seeder class to run individually:

php artisan db:seed

php artisan db:seed –class=UsersTableSeeder
You may also seed your database using the migrate:refresh command, which will also rollback and re-run all of your migrations. This command is useful for completely re-building your database:

php artisan migrate:refresh –seed

Database migration: laravel

Introduction
Migrations are like version control for your database, allowing your team to easily modify and share the application’s database schema. Migrations are typically paired with Laravel’s schema builder to easily build your application’s database schema. If you have ever had to tell a teammate to manually add a column to their local database schema, you’ve faced the problem that database migrations solve.

The Laravel Schema facade provides database agnostic support for creating and manipulating tables across all of Laravel’s supported database systems.

Generating Migrations
To create a migration, use the make:migration Artisan command:

php artisan make:migration create_users_table

The new migration will be placed in your database/migrations directory. Each migration file name contains a timestamp which allows Laravel to determine the order of the migrations.

The –table and –create options may also be used to indicate the name of the table and whether the migration will be creating a new table. These options pre-fill the generated migration stub file with the specified table:

php artisan make:migration create_users_table --create=users

php artisan make:migration add_votes_to_users_table --table=users

If you would like to specify a custom output path for the generated migration, you may use the –path option when executing the make:migration command. The given path should be relative to your application’s base path.

Migration Structure
A migration class contains two methods: up and down. The up method is used to add new tables, columns, or indexes to your database, while the down method should reverse the operations performed by the up method.

Within both of these methods you may use the Laravel schema builder to expressively create and modify tables. To learn about all of the methods available on the Schema builder, check out its documentation. For example, this migration example creates a flights table:

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateFlightsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('flights', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('airline');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('flights');
    }
}

Running Migrations
To run all of your outstanding migrations, execute the migrate Artisan command:
php artisan migrate

Forcing Migrations To Run In Production
Some migration operations are destructive, which means they may cause you to lose data. In order to protect you from running these commands against your production database, you will be prompted for confirmation before the commands are executed. To force the commands to run without a prompt, use the –force flag:
php artisan migrate –force

To rollback the latest migration operation, you may use the rollback command. This command rolls back the last “batch” of migrations, which may include multiple migration files:

php artisan migrate:rollback
You may rollback a limited number of migrations by providing the step option to the rollback command. For example, the following command will rollback the last five migrations:

php artisan migrate:rollback –step=5
The migrate:reset command will roll back all of your application’s migrations:

php artisan migrate:reset

Rollback & Migrate In Single Command
The migrate:refresh command will roll back all of your migrations and then execute the migrate command. This command effectively re-creates your entire database:

php artisan migrate:refresh

// Refresh the database and run all database seeds…
php artisan migrate:refresh –seed
You may rollback & re-migrate a limited number of migrations by providing the step option to the refresh command. For example, the following command will rollback & re-migrate the last five migrations:
php artisan migrate:refresh –step=5

Drop All Tables & Migrate
The migrate:fresh command will drop all tables from the database and then execute the migrate command:

php artisan migrate:fresh

php artisan migrate:fresh –seed

Creating Tables
To create a new database table, use the create method on the Schema facade. The create method accepts two arguments. The first is the name of the table, while the second is a Closure which receives a Blueprint object that may be used to define the new table:

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
});

Database Connection & Table Options
If you want to perform a schema operation on a database connection that is not your default connection, use the connection method:

Schema::connection(‘foo’)->create(‘users’, function (Blueprint $table) {
$table->increments(‘id’);
});

Command Description
$table->engine = ‘InnoDB’; Specify the table storage engine (MySQL).
$table->charset = ‘utf8’; Specify a default character set for the table (MySQL).
$table->collation = ‘utf8_unicode_ci’; Specify a default collation for the table (MySQL).
$table->temporary(); Create a temporary table (except SQL Server).

Creating Columns
The table method on the Schema facade may be used to update existing tables. Like the create method, the table method accepts two arguments: the name of the table and a Closure that receives a Blueprint instance you may use to add columns to the table:
Schema::table(‘users’, function (Blueprint $table) {
$table->string(’email’);
});

Available Column Types
Of course, the schema builder contains a variety of column types that you may specify when building your tables:

Command Description
$table->bigIncrements(‘id’); Auto-incrementing UNSIGNED BIGINT (primary key) equivalent column.
$table->bigInteger(‘votes’); BIGINT equivalent column.
$table->binary(‘data’); BLOB equivalent column.
$table->boolean(‘confirmed’); BOOLEAN equivalent column.
$table->char(‘name’, 100); CHAR equivalent column with an optional length.
$table->date(‘created_at’); DATE equivalent column.
$table->dateTime(‘created_at’); DATETIME equivalent column.
$table->dateTimeTz(‘created_at’); DATETIME (with timezone) equivalent column.
$table->decimal(‘amount’, 8, 2); DECIMAL equivalent column with a precision (total digits) and scale (decimal digits).
$table->double(‘amount’, 8, 2); DOUBLE equivalent column with a precision (total digits) and scale (decimal digits).
$table->enum(‘level’, [‘easy’, ‘hard’]); ENUM equivalent column.
$table->float(‘amount’, 8, 2); FLOAT equivalent column with a precision (total digits) and scale (decimal digits).
$table->geometry(‘positions’); GEOMETRY equivalent column.
$table->geometryCollection(‘positions’); GEOMETRYCOLLECTION equivalent column.
$table->increments(‘id’); Auto-incrementing UNSIGNED INTEGER (primary key) equivalent column.
$table->integer(‘votes’); INTEGER equivalent column.
$table->ipAddress(‘visitor’); IP address equivalent column.
$table->json(‘options’); JSON equivalent column.
$table->jsonb(‘options’); JSONB equivalent column.
$table->lineString(‘positions’); LINESTRING equivalent column.
$table->longText(‘description’); LONGTEXT equivalent column.
$table->macAddress(‘device’); MAC address equivalent column.
$table->mediumIncrements(‘id’); Auto-incrementing UNSIGNED MEDIUMINT (primary key) equivalent column.
$table->mediumInteger(‘votes’); MEDIUMINT equivalent column.
$table->mediumText(‘description’); MEDIUMTEXT equivalent column.
$table->morphs(‘taggable’); Adds taggable_id UNSIGNED BIGINT and taggable_type VARCHAR equivalent columns.
$table->multiLineString(‘positions’); MULTILINESTRING equivalent column.
$table->multiPoint(‘positions’); MULTIPOINT equivalent column.
$table->multiPolygon(‘positions’); MULTIPOLYGON equivalent column.
$table->nullableMorphs(‘taggable’); Adds nullable versions of morphs() columns.
$table->nullableTimestamps(); Alias of timestamps() method.
$table->point(‘position’); POINT equivalent column.
$table->polygon(‘positions’); POLYGON equivalent column.
$table->rememberToken(); Adds a nullable remember_token VARCHAR(100) equivalent column.
$table->smallIncrements(‘id’); Auto-incrementing UNSIGNED SMALLINT (primary key) equivalent column.
$table->smallInteger(‘votes’); SMALLINT equivalent column.
$table->softDeletes(); Adds a nullable deleted_at TIMESTAMP equivalent column for soft deletes.
$table->softDeletesTz(); Adds a nullable deleted_at TIMESTAMP (with timezone) equivalent column for soft deletes.
$table->string(‘name’, 100); VARCHAR equivalent column with a optional length.
$table->text(‘description’); TEXT equivalent column.
$table->time(‘sunrise’); TIME equivalent column.
$table->timeTz(‘sunrise’); TIME (with timezone) equivalent column.
$table->timestamp(‘added_on’); TIMESTAMP equivalent column.
$table->timestampTz(‘added_on’); TIMESTAMP (with timezone) equivalent column.
$table->timestamps(); Adds nullable created_at and updated_at TIMESTAMP equivalent columns.
$table->timestampsTz(); Adds nullable created_at and updated_at TIMESTAMP (with timezone) equivalent columns.
$table->tinyIncrements(‘id’); Auto-incrementing UNSIGNED TINYINT (primary key) equivalent column.
$table->tinyInteger(‘votes’); TINYINT equivalent column.
$table->unsignedBigInteger(‘votes’); UNSIGNED BIGINT equivalent column.
$table->unsignedDecimal(‘amount’, 8, 2); UNSIGNED DECIMAL equivalent column with a precision (total digits) and scale (decimal digits).
$table->unsignedInteger(‘votes’); UNSIGNED INTEGER equivalent column.
$table->unsignedMediumInteger(‘votes’); UNSIGNED MEDIUMINT equivalent column.
$table->unsignedSmallInteger(‘votes’); UNSIGNED SMALLINT equivalent column.
$table->unsignedTinyInteger(‘votes’); UNSIGNED TINYINT equivalent column.
$table->uuid(‘id’); UUID equivalent column.
$table->year(‘birth_year’); YEAR equivalent column.

Column Modifiers
In addition to the column types listed above, there are several column “modifiers” you may use while adding a column to a database table. For example, to make the column “nullable”, you may use the nullable method:

Modifier Description
->after(‘column’) Place the column “after” another column (MySQL)
->autoIncrement() Set INTEGER columns as auto-increment (primary key)
->charset(‘utf8’) Specify a character set for the column (MySQL)
->collation(‘utf8_unicode_ci’) Specify a collation for the column (MySQL/SQL Server)
->comment(‘my comment’) Add a comment to a column (MySQL)
->default($value) Specify a “default” value for the column
->first() Place the column “first” in the table (MySQL)
->nullable($value = true) Allows (by default) NULL values to be inserted into the column
->storedAs($expression) Create a stored generated column (MySQL)
->unsigned() Set INTEGER columns as UNSIGNED (MySQL)
->useCurrent() Set TIMESTAMP columns to use CURRENT_TIMESTAMP as default value
->virtualAs($expression) Create a virtual generated column (MySQL)
->generatedAs($expression) Create an identity column with specified sequence options (PostgreSQL)
->always() Defines the precedence of sequence values over input for an identity column (PostgreSQL)

Updating Column Attributes
The change method allows you to modify some existing column types to a new type or modify the column’s attributes. For example, you may wish to increase the size of a string column. To see the change method in action, let’s increase the size of the name column from 25 to 50:

Schema::table('users', function (Blueprint $table) {
    $table->string('name', 50)->change();
});

Laravel pagenation

In other frameworks, pagination can be very painful. Laravel’s paginator is integrated with the query builder and Eloquent ORM and provides convenient, easy-to-use pagination of database results out of the box. The HTML generated by the paginator is compatible with the Bootstrap CSS framework.

Basic Usage
Paginating Query Builder Results

There are several ways to paginate items. The simplest is by using the paginate method on the query builder or an Eloquent query. The paginate method automatically takes care of setting the proper limit and offset based on the current page being viewed by the user. By default, the current page is detected by the value of the page query string argument on the HTTP request. Of course, this value is automatically detected by Laravel, and is also automatically inserted into links generated by the paginator.

In this example, the only argument passed to the paginate method is the number of items you would like displayed “per page”. In this case, let’s specify that we would like to display 15 items per page:

amespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    /**
     * Show all of the users for the application.
     *
     * @return Response
     */
    public function index()
    {
        $users = DB::table('users')->paginate(15);

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

“Simple Pagination”
If you only need to display simple “Next” and “Previous” links in your pagination view, you may use the simplePaginate method to perform a more efficient query. This is very useful for large datasets when you do not need to display a link for each page number when rendering your view:

$users = DB::table(‘users’)->simplePaginate(15);

Paginating Eloquent Results
You may also paginate Eloquent queries. In this example, we will paginate the User model with 15 items per page. As you can see, the syntax is nearly identical to paginating query builder results:

$users = App\User::paginate(15);

Sometimes you may wish to create a pagination instance manually, passing it an array of items. You may do so by creating either an Illuminate\Pagination\Paginator or Illuminate\Pagination\LengthAwarePaginator instance, depending on your needs.

The Paginator class does not need to know the total number of items in the result set; however, because of this, the class does not have methods for retrieving the index of the last page. The LengthAwarePaginator accepts almost the same arguments as the Paginator; however, it does require a count of the total number of items in the result set.

In other words, the Paginator corresponds to the simplePaginate method on the query builder and Eloquent, while the LengthAwarePaginator corresponds to the paginate method.

When calling the paginate method, you will receive an instance of Illuminate\Pagination\LengthAwarePaginator. When calling the simplePaginate method, you will receive an instance of Illuminate\Pagination\Paginator. These objects provide several methods that describe the result set. In addition to these helpers methods, the paginator instances are iterators and may be looped as an array. So, once you have retrieved the results, you may display the results and render the page links using Blade:

<div class="container">
    @foreach ($users as $user)
        {{ $user->name }}
    @endforeach
</div>

{{ $users->links() }}
Route::get('users', function () {
    $users = App\User::paginate(15);

    $users->withPath('custom/url');

    //
});
{
   "total": 50,
   "per_page": 15,
   "current_page": 1,
   "last_page": 4,
   "first_page_url": "http://laravel.app?page=1",
   "last_page_url": "http://laravel.app?page=4",
   "next_page_url": "http://laravel.app?page=2",
   "prev_page_url": null,
   "path": "http://laravel.app",
   "from": 1,
   "to": 15,
   "data":[
        {
            // Result Object
        },
        {
            // Result Object
        }
   ]
}

$results->count()
$results->currentPage()
$results->firstItem()
$results->hasMorePages()
$results->lastItem()
$results->lastPage() (Not available when using simplePaginate)
$results->nextPageUrl()
$results->onFirstPage()
$results->perPage()
$results->previousPageUrl()
$results->total() (Not available when using simplePaginate)
$results->url($page)

Query builder

Introduction
Laravel’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 on all supported database systems.

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

Retrieving Results
Retrieving All Rows From A Table
You may use the table method on the DB facade to begin 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:

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    /**
     * Show a list of all of the application's users.
     *
     * @return Response
     */
    public function index()
    {
        $users = DB::table('users')->get();

        return view('user.index', ['users' => $users]);
    }
}
foreach ($users as $user) {
    echo $user->name;
}
$user = DB::table('users')->where('name', 'John')->first();
echo $user->name;
$email = DB::table('users')->where('name', 'John')->value('email');

Retrieving A List Of Column Values
If you would like to retrieve a Collection containing the values of a single column, you may use the pluck method. In this example, we’ll retrieve a Collection of role titles:

$titles = DB::table('roles')->pluck('title');
foreach ($titles as $title) {
    echo $title;
}
$roles = DB::table('roles')->pluck('title', 'name');
foreach ($roles as $name => $title) {
    echo $title;
}

Chunking Results
If you need to work with thousands of database records, consider using the chunk method. This method retrieves a small chunk of the results at a time and feeds each chunk into a Closure for processing. This method is very useful for writing Artisan commands that process thousands of records. For example, let’s work with the entire users table in chunks of 100 records at a time:

DB::table('users')->orderBy('id')->chunk(100, function ($users) {
    foreach ($users as $user) {
        //
    }
});
DB::table('users')->where('active', false)
    ->chunkById(100, function ($users) {
        foreach ($users as $user) {
            DB::table('users')
                ->where('id', $user->id)
                ->update(['active' => true]);
        }
    });
$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')
                ->where('finalized', 1)
                ->avg('price');
$users = DB::table('users')->select('name', 'email as user_email')->get();
$users = DB::table('users')->distinct()->get();
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();
$orders = DB::table('orders')
                ->selectRaw('price * ? as price_with_tax', [1.0825])
                ->get();
$orders = DB::table('orders')
                ->whereRaw('price > IF(state = "TX", ?, 100)', [200])
                ->get();
$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();

Laravel Database: Getting Started

Introduction
Laravel makes interacting with databases extremely simple across a variety of database backends using either raw SQL, the fluent query builder, and the Eloquent ORM. Currently, Laravel supports four databases:
-MySQL
-PostgreSQL
-SQLite
-SQL Server

Configuration
The database configuration for your application is located at config/database.php. In this file you may define all of your database connections, as well as specify which connection should be used by default. Examples for most of the supported database systems are provided in this file.

By default, Laravel’s sample environment configuration is ready to use with Laravel Homestead, which is a convenient virtual machine for doing Laravel development on your local machine. Of course, you are free to modify this configuration as needed for your local database.

'connections' => [

        'sqlite' => [
            'driver' => 'sqlite',
            'database' => env('DB_DATABASE', database_path('database.sqlite')),
            'prefix' => '',
        ],

        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

        'pgsql' => [
            'driver' => 'pgsql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '5432'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'schema' => 'public',
            'sslmode' => 'prefer',
        ],

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

    ],

SQLite Configuration
After creating a new SQLite database using a command such as touch database/database.sqlite, you can easily configure your environment variables to point to this newly created database by using the database’s absolute path:

DB_CONNECTION=sqlite
DB_DATABASE=/absolute/path/to/database.sqlite
'sqlite' => [
    // ...
    'foreign_key_constraints' => true,
],

Read & Write Connections
Sometimes you may wish to use one database connection for SELECT statements, and another for INSERT, UPDATE, and DELETE statements. Laravel makes this a breeze, and the proper connections will always be used whether you are using raw queries, the query builder, or the Eloquent ORM.

To see how read / write connections should be configured, let’s look at this example:

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

Note that three keys have been added to the configuration array: read, write and sticky. The read and write keys have array values containing a single key: host. The rest of the database options for the read and write connections will be merged from the main mysql array.

You only need to place items in the read and write arrays if you wish to override the values from the main array. So, in this case, 192.168.1.1 will be used as the host for the “read” connection, while 192.168.1.2 will be used for the “write” connection. The database credentials, prefix, character set, and all other options in the main mysql array will be shared across both connections.

The sticky Option
The sticky option is an optional value that can be used to allow the immediate reading of records that have been written to the database during the current request cycle. If the sticky option is enabled and a “write” operation has been performed against the database during the current request cycle, any further “read” operations will use the “write” connection. This ensures that any data written during the request cycle can be immediately read back from the database during that same request. It is up to you to decide if this is the desired behavior for your application.

Using Multiple Database Connections
When using multiple connections, you may access each connection via the connection method on the DB facade. The name passed to the connection method should correspond to one of the connections listed in your config/database.php configuration file:

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

Running Raw SQL Queries
Once you have configured your database connection, you may run queries using the DB facade. The DB facade provides methods for each type of query: select, update, insert, delete, and statement.

Running A Select Query
To run a basic query, you may use the select method on the DB facade:

namespace App\Http\Controllers;

use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
    /**
     * Show a list of all of the application's users.
     *
     * @return Response
     */
    public function index()
    {
        $users = DB::select('select * from users where active = ?', [1]);

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

Listening For Query Events
If you would like to receive each SQL query executed by your application, you may use the listen method. This method is useful for logging queries or debugging. You may register your query listener in a service provider:

namespace App\Providers;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        DB::listen(function ($query) {
            // $query->sql
            // $query->bindings
            // $query->time
        });
    }

    /**
     * Register the service provider.
     *
     * @return void
     */
    public function register()
    {
        //
    }
}

You may use the transaction method on the DB 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:

Laravel Task Scheduling

Introduction
In the past, you may have generated a Cron entry for each task you needed to schedule on your server. However, this can quickly become a pain, because your task schedule is no longer in source control and you must SSH into your server to add additional Cron entries.

Laravel’s command scheduler allows you to fluently and expressively define your command schedule within Laravel itself. When using the scheduler, only a single Cron entry is needed on your server. Your task schedule is defined in the app/Console/Kernel.php file’s schedule method. To help you get started, a simple example is defined within the method.

Starting The Scheduler
When using the scheduler, you only need to add the following Cron entry to your server. If you do not know how to add Cron entries to your server, consider using a service such as Laravel Forge which can manage the Cron entries for you:

* * * * * cd /path-to-your-project && php artisan schedule:run >> /dev/null 2>&1

This Cron will call the Laravel command scheduler every minute. When the schedule:run command is executed, Laravel will evaluate your scheduled tasks and runs the tasks that are due.

Defining Schedules
You may define all of your scheduled tasks in the schedule method of the App\Console\Kernel class. To get started, let’s look at an example of scheduling a task. In this example, we will schedule a Closure to be called every day at midnight. Within the Closure we will execute a database query to clear a table:

namespace App\Console;

use DB;
use Illuminate\Console\Scheduling\Schedule;
use Illuminate\Foundation\Console\Kernel as ConsoleKernel;

class Kernel extends ConsoleKernel
{
    /**
     * The Artisan commands provided by your application.
     *
     * @var array
     */
    protected $commands = [
        //
    ];

    /**
     * Define the application's command schedule.
     *
     * @param  \Illuminate\Console\Scheduling\Schedule  $schedule
     * @return void
     */
    protected function schedule(Schedule $schedule)
    {
        $schedule->call(function () {
            DB::table('recent_users')->delete();
        })->daily();
    }
}

In addition to scheduling using Closures, you may also use invokable objects. Invokable objects are simple PHP classes that contain an __invoke method:

$schedule->call(new DeleteRecentUsers)->daily();

Scheduling Artisan Commands
In addition to scheduling Closure calls, you may also schedule Artisan commands and operating system commands. For example, you may use the command method to schedule an Artisan command using either the command’s name or class:

$schedule->command(’emails:send –force’)->daily();
$schedule->command(EmailsCommand::class, [‘–force’])->daily();

Scheduling Queued Jobs
The job method may be used to schedule a queued job. This method provides a convenient way to schedule jobs without using the call method to manually create Closures to queue the job:

$schedule->job(new Heartbeat)->everyFiveMinutes();
// Dispatch the job to the “heartbeats” queue…
$schedule->job(new Heartbeat, ‘heartbeats’)->everyFiveMinutes();

Scheduling Shell Commands
The exec method may be used to issue a command to the operating system:
$schedule->exec(‘node /home/forge/script.js’)->daily();

Schedule Frequency Options
Of course, there are a variety of schedules you may assign to your task:

Method Description
->cron(‘* * * * *’); Run the task on a custom Cron schedule
->everyMinute(); Run the task every minute
->everyFiveMinutes(); Run the task every five minutes
->everyTenMinutes(); Run the task every ten minutes
->everyFifteenMinutes(); Run the task every fifteen minutes
->everyThirtyMinutes(); Run the task every thirty minutes
->hourly(); Run the task every hour
->hourlyAt(17); Run the task every hour at 17 mins past the hour
->daily(); Run the task every day at midnight
->dailyAt(’13:00′); Run the task every day at 13:00
->twiceDaily(1, 13); Run the task daily at 1:00 & 13:00
->weekly(); Run the task every week
->weeklyOn(1, ‘8:00′); Run the task every week on Monday at 8:00
->monthly(); Run the task every month
->monthlyOn(4, ’15:00’); Run the task every month on the 4th at 15:00
->quarterly(); Run the task every quarter
->yearly(); Run the task every year
->timezone(‘America/New_York’); Set the timezone
These methods may be combined with additional constraints to create even more finely tuned schedules that only run on certain days of the week. For example, to schedule a command to run weekly on Monday:

$schedule->call(function () {
    //
})->weekly()->mondays()->at('13:00');

// Run hourly from 8 AM to 5 PM on weekdays...
$schedule->command('foo')
          ->weekdays()
          ->hourly()
          ->timezone('America/Chicago')
          ->between('8:00', '17:00');

Below is a list of the additional schedule constraints:

Method Description
->weekdays(); Limit the task to weekdays
->sundays(); Limit the task to Sunday
->mondays(); Limit the task to Monday
->tuesdays(); Limit the task to Tuesday
->wednesdays(); Limit the task to Wednesday
->thursdays(); Limit the task to Thursday
->fridays(); Limit the task to Friday
->saturdays(); Limit the task to Saturday
->between($start, $end); Limit the task to run between start and end times
->when(Closure); Limit the task based on a truth test
->environments($env); Limit the task to specific environments

$schedule->command('reminders:send')
                    ->hourly()
                    ->between('7:00', '22:00');
$schedule->command('reminders:send')
                    ->hourly()
                    ->unlessBetween('23:00', '4:00');
$schedule->command('emails:send')->daily()->when(function () {
    return true;
});
$schedule->command('emails:send')->daily()->skip(function () {
    return true;
});
$schedule->command('report:generate')
         ->timezone('America/New_York')
         ->at('02:00')

Laravel Queues

Creating Jobs
Generating Job Classes
By default, all of the queueable jobs for your application are stored in the app/Jobs directory. If the app/Jobs directory doesn’t exist, it will be created when you run the make:job Artisan command. You may generate a new queued job using the Artisan CLI:

php artisan make:job ProcessPodcast
The generated class will implement the Illuminate\Contracts\Queue\ShouldQueue interface, indicating to Laravel that the job should be pushed onto the queue to run asynchronously.

Class Structure
Job classes are very simple, normally containing only a handle method which is called when the job is processed by the queue. To get started, let’s take a look at an example job class. In this example, we’ll pretend we manage a podcast publishing service and need to process the uploaded podcast files before they are published:

namespace App\Jobs;

use App\Podcast;
use App\AudioProcessor;
use Illuminate\Bus\Queueable;
use Illuminate\Queue\SerializesModels;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;

class ProcessPodcast implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    protected $podcast;

    /**
     * Create a new job instance.
     *
     * @param  Podcast  $podcast
     * @return void
     */
    public function __construct(Podcast $podcast)
    {
        $this->podcast = $podcast;
    }

    /**
     * Execute the job.
     *
     * @param  AudioProcessor  $processor
     * @return void
     */
    public function handle(AudioProcessor $processor)
    {
        // Process uploaded podcast...
    }
}

In this example, note that we were able to pass an Eloquent model directly into the queued job’s constructor. Because of the SerializesModels trait that the job is using, Eloquent models will be gracefully serialized and unserialized when the job is processing. If your queued job accepts an Eloquent model in its constructor, only the identifier for the model will be serialized onto the queue. When the job is actually handled, the queue system will automatically re-retrieve the full model instance from the database. It’s all totally transparent to your application and prevents issues that can arise from serializing full Eloquent model instances.

The handle method is called when the job is processed by the queue. Note that we are able to type-hint dependencies on the handle method of the job. The Laravel service container automatically injects these dependencies.

Dispatching Jobs
Once you have written your job class, you may dispatch it using the dispatch method on the job itself. The arguments passed to the dispatch method will be given to the job’s constructor:

namespace App\Http\Controllers;

use App\Jobs\ProcessPodcast;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

class PodcastController extends Controller
{
    /**
     * Store a new podcast.
     *
     * @param  Request  $request
     * @return Response
     */
    public function store(Request $request)
    {
        // Create podcast...

        ProcessPodcast::dispatch($podcast);
    }
}

Delayed Dispatching
If you would like to delay the execution of a queued job, you may use the delay method when dispatching a job. For example, let’s specify that a job should not be available for processing until 10 minutes after it has been dispatched:

namespace App\Http\Controllers;

use App\Jobs\ProcessPodcast;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

class PodcastController extends Controller
{
    /**
     * Store a new podcast.
     *
     * @param  Request  $request
     * @return Response
     */
    public function store(Request $request)
    {
        // Create podcast...

        ProcessPodcast::dispatch($podcast)
                ->delay(now()->addMinutes(10));
    }
}

Job Chaining
Job chaining allows you to specify a list of queued jobs that should be run in sequence. If one job in the sequence fails, the rest of the jobs will not be run. To execute a queued job chain, you may use the withChain method on any of your dispatchable jobs:

ProcessPodcast::withChain([
    new OptimizePodcast,
    new ReleasePodcast
])->dispatch();

Chain Connection & Queue
If you would like to specify the default connection and queue that should be used for the chained jobs, you may use the allOnConnection and allOnQueue methods. These methods specify the queue connection and queue name that should be used unless the queued job is explicitly assigned a different connection / queue:

ProcessPodcast::withChain([
    new OptimizePodcast,
    new ReleasePodcast
])->dispatch()->allOnConnection('redis')->allOnQueue('podcasts');

Dispatching To A Particular Queue
By pushing jobs to different queues, you may “categorize” your queued jobs and even prioritize how many workers you assign to various queues. Keep in mind, this does not push jobs to different queue “connections” as defined by your queue configuration file, but only to specific queues within a single connection. To specify the queue, use the onQueue method when dispatching the job:

namespace App\Http\Controllers;
use App\Jobs\ProcessPodcast;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
class PodcastController extends Controller
{
    /**
     * Store a new podcast.
     *
     * @param  Request  $request
     * @return Response
     */
    public function store(Request $request)
    {
        // Create podcast...

        ProcessPodcast::dispatch($podcast)->onQueue('processing');
    }
}

Dispatching To A Particular Connection
If you are working with multiple queue connections, you may specify which connection to push a job to. To specify the connection, use the onConnection method when dispatching the job:

namespace App\Http\Controllers;

use App\Jobs\ProcessPodcast;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

class PodcastController extends Controller
{
    /**
     * Store a new podcast.
     *
     * @param  Request  $request
     * @return Response
     */
    public function store(Request $request)
    {
        // Create podcast...

        ProcessPodcast::dispatch($podcast)->onConnection('sqs');
    }
}
ProcessPodcast::dispatch($podcast)
              ->onConnection('sqs')
              ->onQueue('processing');

Max Attempts
One approach to specifying the maximum number of times a job may be attempted is via the –tries switch on the Artisan command line:

php artisan queue:work --tries=3
namespace App\Jobs;

class ProcessPodcast implements ShouldQueue
{
    /**
     * The number of times the job may be attempted.
     *
     * @var int
     */
    public $tries = 5;
}

Time Based Attempts
As an alternative to defining how many times a job may be attempted before it fails, you may define a time at which the job should timeout. This allows a job to be attempted any number of times within a given time frame. To define the time at which a job should timeout, add a retryUntil method to your job class:

Error Handling
If an exception is thrown while the job is being processed, the job will automatically be released back onto the queue so it may be attempted again. The job will continue to be released until it has been attempted the maximum number of times allowed by your application. The maximum number of attempts is defined by the –tries switch used on the queue:work Artisan command. Alternatively, the maximum number of attempts may be defined on the job class itself. More information on running the queue worker can be found below.

Laravel includes a queue worker that will process new jobs as they are pushed onto the queue. You may run the worker using the queue:work Artisan command. Note that once the queue:work command has started, it will continue to run until it is manually stopped or you close your terminal:

Remember, queue workers are long-lived processes and store the booted application state in memory. As a result, they will not notice changes in your code base after they have been started. So, during your deployment process, be sure to restart your queue workers.

You may customize your queue worker even further by only processing particular queues for a given connection. For example, if all of your emails are processed in an emails queue on your redis queue connection, you may issue the following command to start a worker that only processes only that queue:

php artisan queue:work redis –queue=emails

Processing A Single Job
The –once option may be used to instruct the worker to only process a single job from the queue:

php artisan queue:work –once

Processing All Queued Jobs & Then Exiting
The –stop-when-empty option may be used to instruct the worker to process all jobs and then exit gracefully. This option can be useful when working Laravel queues within a Docker container if you wish to shutdown the container after the queue is empty:

php artisan queue:work –stop-when-empty
Resource Considerations
Daemon queue workers do not “reboot” the framework before processing each job. Therefore, you should free any heavy resources after each job completes. For example, if you are doing image manipulation with the GD library, you should free the memory with imagedestroy when you are done.

Queue Priorities
Sometimes you may wish to prioritize how your queues are processed. For example, in your config/queue.php you may set the default queue for your redis connection to low. However, occasionally you may wish to push a job to a high priority queue like so:

dispatch((new Job)->onQueue(‘high’));
php artisan queue:work –queue=high,low

Queue Workers & Deployment
Since queue workers are long-lived processes, they will not pick up changes to your code without being restarted. So, the simplest way to deploy an application using queue workers is to restart the workers during your deployment process. You may gracefully restart all of the workers by issuing the queue:restart command:

php artisan queue:restart

Dealing With Failed Jobs
Sometimes your queued jobs will fail. Don’t worry, things don’t always go as planned! Laravel includes a convenient way to specify the maximum number of times a job should be attempted. After a job has exceeded this amount of attempts, it will be inserted into the failed_jobs database table. To create a migration for the failed_jobs table, you may use the queue:failed-table command: