laravel db migrate

// roll back
php artisan migrate:rollback
php artisan migrate:rollback --step=5  // rollback the last five migrations
php artisan migrate:reset  // roll back all of your application's migrations

roll back all of your migrations and then execute the  migrate command. This command effectively re-creates your entire database:
php artisan migrate:refresh
php artisan migrate:refresh --step=5

//drop all tables and re-run all migrations
php artisan migrate:fresh

laravel Query Builder

Query BuilderのSQL確認方法
$builder->toSql()
$builder->getBindings()

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.

extract a single value

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

Retrieving A List Of Column Values

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

aggregate methods

->count()/max('price')/min('price')/avg('price')/sum('price')

->exists()/doesntExist()

Select Clause

$users = DB::table('users')->select('name', 'email as user_email')->get();
->distinct()
add a column to its existing select clause
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();

Raw Expressions

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

selectRaw/whereRaw / orWhereRaw/havingRaw / orHavingRaw/orderByRaw

Where Clauses

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

->where('votes', '>', 100)
->orWhere('name', 'John')

whereNotXxxx/orWhereXxxx
->whereBetween('votes', [1, 100])

->whereIn('id', [1, 2, 3])

->whereNull('updated_at')

->whereDate('created_at', '2016-12-31')
->whereMonth('created_at', '12')
->whereDay('created_at', '31')
->whereYear('created_at', '2016')
->whereTime('created_at', '=', '11:20:45')

The whereColumn method may be used to verify that two columns are equal:
->whereColumn('first_name', 'last_name')
You may also pass a comparison operator to the method:
->whereColumn('updated_at', '>', 'created_at')

Parameter Grouping

DB::table('users')
            ->where('name', '=', 'John')
            ->where(function ($query) {
                $query->where('votes', '>', 100)
                      ->orWhere('title', '=', 'Admin');
            })
            ->get();
The example above will produce the following SQL:
select * from users where name = 'John' and (votes > 100 or title = 'Admin')

order by

->orderBy('name', 'desc')
->latest ()/ oldest()
By default, result will be ordered by the created_at column.

groupBy/having/skip/take/offset/limit

Conditional Clauses

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

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

$users = DB::table('users')

                ->when($role, function ($query, $role) {

                    return $query->where('role_id', $role);

                })

                ->get();

Insert

DB::table('users')->insert([

    ['email' => 'taylor@example.com', 'votes' => 0],

    ['email' => 'dayle@example.com', 'votes' => 0]

]);

$id = DB::table('users')->insertGetId(

    ['email' => 'john@example.com', 'votes' => 0]

);

Update

DB::table('users')

            ->where('id', 1)

            ->update(['votes' => 1]);

The updateOrInsert method will first attempt to locate a matching database record using the first argument's column and value pairs. If the record exists, it will be updated with the values in the second argument. If the record can not be found, a new record will be inserted with the merged attributes of both arguments:

DB::table('users')

    ->updateOrInsert(

        ['email' => 'john@example.com', 'name' => 'John'],

        ['votes' => '2']

    );

Delete

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

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

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

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();

laravel table schema mapping

Auto-incrementing UNSIGNED BIGINT (primary key)  => $table->bigIncrements('id');
DATE  => $table->date('created_at');
DATETIME  => $table->dateTime('created_at');
VARCHAR  => $table->string('name', 100);
TEXT  => $table->text('description');
TIMESTAMP  => $table->timestamp('added_on');

Allows (by default) NULL values to be inserted into the column
->nullable($value = true)

Specify a "default" value for the column
->default($value)

Add a comment
->comment('my comment')

Set INTEGER columns as auto-increment (primary key)
->autoIncrement()

laravel relation

class User extends Model
{
    /**
     * Get the phone record associated with the user.
     */
    public function phone()
    {
        return $this->hasOne('App\Phone');
    }
}

one to one
hasOne/belongsTo

one to many
hasMany/belongsTo

many to many
belongsToMany/belongsToMany
$shop = Shop::find($shop_id);
$shop->products()->attach($product_id);
$shop->products()->detach($product_id);

we may access the intermediate table using the pivot attribute on the models:
foreach ($user->roles as $role) {
    echo $role->pivot->created_at;
}
Notice that each Role model we retrieve is automatically assigned a pivot attribute. This attribute contains a model representing the intermediate table, and may be used like any other Eloquent model.

Filtering Relationships Via Intermediate Table Columns
return $this->belongsToMany('App\Role')->wherePivot('approved', 1);
return $this->belongsToMany('App\Role')->wherePivotIn('priority', [1, 2]);

Inserting & Updating Related Models

setting the post_id attribute on the Comment
$comment = new App\Comment(['message' => 'A new comment.']);
$post = App\Post::find(1);
$post->comments()->save($comment);
$post->comments()->saveMany([
    new App\Comment(['message' => 'A new comment.']),
    new App\Comment(['message' => 'Another comment.']),
]);

$post = App\Post::find(1);
$post->comments[0]->message = 'Message';
$post->comments[0]->author->name = 'Author Name';
$post->push();

the difference between save and create is that save accepts a full Eloquent model instance while create accepts a plain PHP array:
$post = App\Post::find(1);
$comment = $post->comments()->create([
    'message' => 'A new comment.',
]);
$post->comments()->createMany([
    [
        'message' => 'A new comment.',
    ],
    [
        'message' => 'Another new comment.',
    ],
]);

Belongs To Relationships

When updating a belongsTo relationship, you may use the associate method. This method will set the foreign key on the child model:

$account = App\Account::find(10);
$user->account()->associate($account);
$user->save();

$user->account()->dissociate();
$user->save();

Many To Many Relationships

$user = App\User::find(1);
$user->roles()->attach($roleId);

When attaching a relationship to a model, you may also pass an array of additional data to be inserted into the intermediate table:
$user->roles()->attach($roleId, ['expires' => $expires]);

$user->roles()->detach($roleId);
// Detach all roles from the user...
$user->roles()->detach();

When working with a many-to-many relationship, the save method accepts an array of additional intermediate table attributes as its second argument:
App\User::find(1)->roles()->save($role, ['expires' => $expires]);

updateExistingPivot method. This method accepts the pivot record foreign key and an array of attributes to update:

$user = App\User::find(1);
$user->roles()->updateExistingPivot($roleId, $attributes);

lavavel blade

@extends
@section
@show
@yield
@parent
@component
@slot
@inculde, @includeIf, @includeWhen, @includeFirst

Hello, @{{ name }}.
In this example, the @ symbol will be removed by Blade; however, {{ name }} expression will remain untouched by the Blade engine, allowing it to instead be rendered by your JavaScript framework.

@verbatim
    <div class="container">
        Hello, {{ name }}.
    </div>
@endverbatim

@if, @elseif, @else

@unless

@isset, @empty

@auth, @guest

@switch, @case, @break, @default

@for, @foreach, @forelse/@empty, @while
@continue, @break

@php

@csrf, @method

@error directive may be used to quickly check if validation error messages exist for a given attribute. Within an @error directive, you may echo the $message variable to display the error message:

<input id="title" type="text" class="@error('title') is-invalid @enderror">

@error('title')
    <div class="alert alert-danger">{{ $message }}</div>
@enderror

You may combine loops and includes into one line with Blade's @each directive:
@each('view.name', $jobs, 'job')
@each('view.name', $jobs, 'job', 'view.empty')

@inject directive may be used to retrieve a service from the Laravel service container.
@inject('metrics', 'App\Services\MetricsService')
<div>
    Monthly Revenue: {{ $metrics->monthlyRevenue() }}.
</div>

laravel Routing

The routes/web.php file defines routes that are for your web interface. These routes are assigned the web middleware group, which provides features like session state and CSRF protection. The routes in routes/api.php are stateless and are assigned the api middleware group.

register a route that responds to multiple HTTP verbs. You may do so using the match method. Or, you may even register a route that responds to all HTTP verbs using the any method:

Route::match(['get', 'post'], '/', function () {
    //
});

Route::any('/', function () {
    //
});

POST, PUT, or DELETE routes that are defined in the web routes file should include a CSRF token field.

Redirect Routes

If you are defining a route that redirects to another URI, you may use the Route::redirect method. This method provides a convenient shortcut so that you do not have to define a full route or controller for performing a simple redirect:

Route::redirect('/here', '/there');

By default, Route::redirect returns a 302 status code. You may customize the status code using the optional third parameter:
Route::redirect('/here', '/there', 301);

You may use the Route::permanentRedirect method to return a 301 status code:
Route::permanentRedirect('/here', '/there');

View Routes

this method provides a simple shortcut so that you do not have to define a full route or controller.

Route::view('/welcome', 'welcome');

Route::view('/welcome', 'welcome', ['name' => 'Taylor']);

Route Parameters

Route::get('user/{id}', function ($id) {

    return 'User '.$id;

});

Route::get('posts/{post}/comments/{comment}', function ($postId, $commentId) {

    //

});

not contain a - character. Instead of using the - character, use an underscore (_).

Optional Parameters

Make sure to give the route's corresponding variable a default value:

Route::get('user/{name?}', function ($name = null) {

    return $name;

});

Route::get('user/{name?}', function ($name = 'John') {

    return $name;

});

Regular Expression Constraints

Route::get('user/{name}', function ($name) {

    //

})->where('name', '[A-Za-z]+');

Route::get('user/{id}', function ($id) {

    //

})->where('id', '[0-9]+');

Route::get('user/{id}/{name}', function ($id, $name) {

    //

})->where(['id' => '[0-9]+', 'name' => '[a-z]+']);

Encoded Forward Slashes

The Laravel routing component allows all characters except /. You must explicitly allow / to be part of your placeholder using a where condition regular expression:

Route::get('search/{search}', function ($search) {
    return $search;
})->where('search', '.*');

Encoded forward slashes are only supported within the last route segment.

Named Routes

Route::get('user/profile', 'UserProfileController@show')->name('profile');

// Generating URLs...

$url = route('profile');

// Generating Redirects...

return redirect()->route('profile');

Route Groups

Route::middleware(['first', 'second'])->group(function () {

    Route::get('/', function () {

        // Uses first & second Middleware

    });

    Route::get('user/profile', function () {

        // Uses first & second Middleware

    });

});

Route::namespace('Admin')->group(function () {
    // Controllers Within The "App\Http\Controllers\Admin" Namespace
});
by default, the RouteServiceProvider includes your route files within a namespace group, allowing you to register controller routes without specifying the full  App\Http\Controllers namespace prefix. So, you only need to specify the portion of the namespace that comes after the base App\Http\Controllers namespace.

Route::domain('{account}.myapp.com')->group(function () {
    Route::get('user/{id}', function ($account, $id) {
        //
    });
});
In order to ensure your sub-domain routes are reachable, you should register sub-domain routes before registering root domain routes. This will prevent root domain routes from overwriting sub-domain routes which have the same URI path.

Route::prefix('admin')->group(function () {
    Route::get('users', function () {
        // Matches The "/admin/users" URL
    });
});
Route::name('admin.')->group(function () {
    Route::get('users', function () {
        // Route assigned name "admin.users"...
    })->name('users');
});

Fallback Routes

define a route that will be executed when no other route matches the incoming request.
Route::fallback(function () {
    //
});
The fallback route should always be the last route registered by your application.

Accessing The Current Route

$route = Route::current();
$name = Route::currentRouteName();
$action = Route::currentRouteAction();

laravel eloquent

Each database table has a corresponding "Model" which is used to interact with that table.
php artisan make:model Flight --migration
php artisan make:model Flight -m

protected $table = 'my_flights';
protected $primaryKey = 'flight_id';
public $incrementing = false;
protected $keyType = 'string';

By default, Eloquent expects created_at and updated_at columns to exist on your tables.
public $timestamps = false;

If you need to customize the format of your timestamps, set the $dateFormat property on your model. This property determines how date attributes are stored in the database
protected $dateFormat = 'U';

const CREATED_AT = 'creation_date';
const UPDATED_AT = 'last_update';

Default Attribute Values

protected $attributes = [
        'delayed' => false,
    ];

App\Flight::all();

$flights = App\Flight::find([1, 2, 3]);

$flights = App\Flight::where('active', 1)
               ->orderBy('name', 'desc')
               ->take(10)
               ->get();

$flight = App\Flight::where('active', 1)->first();

Not Found Exceptions

$model = App\Flight::findOrFail(1);
$model = App\Flight::where('legs', '>', 100)->firstOrFail();
If the exception is not caught, a 404 HTTP response is automatically sent back to the user. It is not necessary to write explicit checks to return 404 responses when using these methods

Retrieving Aggregates

You may also use the count, sum, max, and other aggregate methods provided by the query builder. These methods return the appropriate scalar value instead of a full model instance:

$count = App\Flight::where('active', 1)->count();
$max = App\Flight::where('active', 1)->max('price');

Inserts

$flight = new Flight;
$flight->name = $request->name;
$flight->save();

Updates

$flight = App\Flight::find(1);

$flight->name = 'New Flight Name';

$flight->save();

App\Flight::where('active', 1)
          ->where('destination', 'San Diego')
          ->update(['delayed' => 1]);

Mass Assignment

You may also use the create method to save a new model in a single line. before doing so, you will need to specify either a fillable or guarded attribute on the model, as all Eloquent models protect against mass-assignment by default.

protected $fillable = ['name'];

$flight = App\Flight::create(['name' => 'Flight 10']);

If you already have a model instance, you may use the fill method to populate it with an array of attributes:

$flight->fill(['name' => 'Flight 22']);

protected $guarded = ['price'];

protected $guarded = [];

you should use either  $fillable or $guarded - not both. 

Deleting Models

$flight = App\Flight::find(1);

$flight->delete();

App\Flight::destroy(1);

App\Flight::destroy(1, 2, 3);

App\Flight::destroy([1, 2, 3]);

App\Flight::destroy(collect([1, 2, 3]));

$deletedRows = App\Flight::where('active', 0)->delete();