Laravel DB周りサンプルメモ

投稿日：2021年5月23日

モデル周り

Schema::create('user_mails', function (Blueprint $table) {
$table->bigIncrements('user_mail_id');
$table->bigInteger('user_id')->unsigned()->comment('ユーザーID');
$table->timestamps();
$table->softDeletes();
$table->foreign('user_id')->references('user_id')->on('users');
});
protected $fillable = [
'user_id',
];
=User
public function userMails(): HasMany
{
return $this->hasMany('App\Models\UserMail', 'user_id');
}
=UserMail
public function user(): BelongsTo
{
return $this->belongsTo('App\Models\User', 'user_id');
}

Schema::create('user_mails', function (Blueprint $table) {

$table->bigIncrements('user_mail_id');

$table->bigInteger('user_id')->unsigned()->comment('ユーザーID');

$table->timestamps();

$table->softDeletes();

$table->foreign('user_id')->references('user_id')->on('users');

});

protected $fillable = [

'user_id',

];

=User

public function userMails(): HasMany

{

return $this->hasMany('App\Models\UserMail', 'user_id');

}

=UserMail

public function user(): BelongsTo

{

return $this->belongsTo('App\Models\User', 'user_id');

}

データ抽出

$query = User::leftJoin('user_mails', function ($join) {
$join->on('users.user_id', '=', 'user_mails.user_id')->whereNull('user_mails.deleted_at');
});
$query->select([
'users.email',
\DB::raw('(case when user_mails.user_mail_id is not null then 1 else 0 end) as is_mail')
]);
 
$query->where('user_mails.user_mail_id', 1);
 
$query->where(function ($subQuery) use ($keyword) {
$subQuery->where('users.name', 'LIKE', "%{$keyword}%")
->orWhere('users.email', 'LIKE', "%{$keyword}%");
});

$query = User::leftJoin('user_mails', function ($join) {

$join->on('users.user_id', '=', 'user_mails.user_id')->whereNull('user_mails.deleted_at');

});

$query->select([

'users.email',

\DB::raw('(case when user_mails.user_mail_id is not null then 1 else 0 end) as is_mail')

]);

$query->where('user_mails.user_mail_id', 1);

$query->where(function ($subQuery) use ($keyword) {

$subQuery->where('users.name', 'LIKE', "%{$keyword}%")

->orWhere('users.email', 'LIKE', "%{$keyword}%");

});

-Laravel
-クエリビルダ, ORM, Eloquent

執筆者：管理人

comment コメントをキャンセル

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください。

: Laravel8メモ
Laravel 公式バリデーション全54種類！Laravel 5.7のバリデーション実例 Eloquent Laravel・データベースからデータ取得する全実例

Laravel6 バージョン確認方法

Laravel6 バージョン確認方法です。・コマンド

# php artisan -V
Laravel Framework 6.20.2

1 2	# php artisan -V Laravel Framework 6.20.2

Laravel6 artisanコマンド一覧

Laravel6のartisanコマンド一覧です。

$ php artisan list
Laravel Framework 6.20.2
Usage:
command [options] [arguments]
Options:
-h, --help            Display this help message
-q, --quiet           Do not output any message
-V, --version         Display this application version
--ansi            Force ANSI output
--no-ansi         Disable ANSI output
-n, --no-interaction  Do not ask any interactive question
--env[=ENV]       The environment the command should run under
-v|vv|vvv, --verbose  Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
Available commands:
clear-compiled       Remove the compiled class file
down                 Put the application into maintenance mode
env                  Display the current framework environment
help                 Displays help for a command
list                 Lists commands
migrate              Run the database migrations
optimize             Cache the framework bootstrap files
preset               Swap the front-end scaffolding for the application
serve                Serve the application on the PHP development server
tinker               Interact with your application
ui                   Swap the front-end scaffolding for the application
up                   Bring the application out of maintenance mode
auth
auth:clear-resets    Flush expired password reset tokens
cache
cache:clear          Flush the application cache
cache:forget         Remove an item from the cache
cache:table          Create a migration for the cache database table
config
config:cache         Create a cache file for faster configuration loading
config:clear         Remove the configuration cache file
db
db:seed              Seed the database with records
db:wipe              Drop all tables, views, and types
event
event:cache          Discover and cache the application's events and listeners
event:clear          Clear all cached events and listeners
event:generate       Generate the missing events and listeners based on registration
event:list           List the application's events and listeners
key
key:generate         Set the application key
make
make:channel         Create a new channel class
make:command         Create a new Artisan command
make:controller      Create a new controller class
make:event           Create a new event class
make:exception       Create a new custom exception class
make:factory         Create a new model factory
make:job             Create a new job class
make:listener        Create a new event listener class
make:mail            Create a new email class
make:middleware      Create a new middleware class
make:migration       Create a new migration file
make:model           Create a new Eloquent model class
make:notification    Create a new notification class
make:observer        Create a new observer class
make:policy          Create a new policy class
make:provider        Create a new service provider class
make:request         Create a new form request class
make:resource        Create a new resource
make:rule            Create a new validation rule
make:seeder          Create a new seeder class
make:test            Create a new test class
migrate
migrate:fresh        Drop all tables and re-run all migrations
migrate:install      Create the migration repository
migrate:refresh      Reset and re-run all migrations
migrate:reset        Rollback all database migrations
migrate:rollback     Rollback the last database migration
migrate:status       Show the status of each migration
notifications
notifications:table  Create a migration for the notifications table
optimize
optimize:clear       Remove the cached bootstrap files
package
package:discover     Rebuild the cached package manifest
queue
queue:failed         List all of the failed queue jobs
queue:failed-table   Create a migration for the failed queue jobs database table
queue:flush          Flush all of the failed queue jobs
queue:forget         Delete a failed queue job
queue:listen         Listen to a given queue
queue:restart        Restart queue worker daemons after their current job
queue:retry          Retry a failed queue job
queue:table          Create a migration for the queue jobs database table
queue:work           Start processing jobs on the queue as a daemon
route
route:cache          Create a route cache file for faster route registration
route:clear          Remove the route cache file
route:list           List all registered routes
schedule
schedule:run         Run the scheduled commands
session
session:table        Create a migration for the session database table
storage
storage:link         Create a symbolic link from "public/storage" to "storage/app/public"
ui
ui:auth              Scaffold basic login and registration views and routes
vendor
vendor:publish       Publish any publishable assets from vendor packages
view
view:cache           Compile all of the application's Blade templates
view:clear           Clear all compiled view files

100

101

102

103

104

105

106

107

108

109

110

$ php artisan list

Laravel Framework 6.20.2

Usage:

command [options] [arguments]

Options:

-h, --help Display this help message

-q, --quiet Do not output any message

-V, --version Display this application version

--ansi Force ANSI output

--no-ansi Disable ANSI output

-n, --no-interaction Do not ask any interactive question

--env[=ENV] The environment the command should run under

-v|vv|vvv, --verbose Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug

Available commands:

clear-compiled Remove the compiled class file

down Put the application into maintenance mode

env Display the current framework environment

help Displays help for a command

list Lists commands

migrate Run the database migrations

optimize Cache the framework bootstrap files

preset Swap the front-end scaffolding for the application

serve Serve the application on the PHP development server

tinker Interact with your application

ui Swap the front-end scaffolding for the application

up Bring the application out of maintenance mode

auth

auth:clear-resets Flush expired password reset tokens

cache

cache:clear Flush the application cache

cache:forget Remove an item from the cache

cache:table Create a migration for the cache database table

config

config:cache Create a cache file for faster configuration loading

config:clear Remove the configuration cache file

db:seed Seed the database with records

db:wipe Drop all tables, views, and types

event

event:cache Discover and cache the application's events and listeners

event:clear Clear all cached events and listeners

event:generate Generate the missing events and listeners based on registration

event:list List the application's events and listeners

key

key:generate Set the application key

make

make:channel Create a new channel class

make:command Create a new Artisan command

make:controller Create a new controller class

make:event Create a new event class

make:exception Create a new custom exception class

make:factory Create a new model factory

make:job Create a new job class

make:listener Create a new event listener class

make:mail Create a new email class

make:middleware Create a new middleware class

make:migration Create a new migration file

make:model Create a new Eloquent model class

make:notification Create a new notification class

make:observer Create a new observer class

make:policy Create a new policy class

make:provider Create a new service provider class

make:request Create a new form request class

make:resource Create a new resource

make:rule Create a new validation rule

make:seeder Create a new seeder class

make:test Create a new test class

migrate

migrate:fresh Drop all tables and re-run all migrations

migrate:install Create the migration repository

migrate:refresh Reset and re-run all migrations

migrate:reset Rollback all database migrations

migrate:rollback Rollback the last database migration

migrate:status Show the status of each migration

notifications

notifications:table Create a migration for the notifications table

optimize

optimize:clear Remove the cached bootstrap files

package

package:discover Rebuild the cached package manifest

queue

queue:failed List all of the failed queue jobs

queue:failed-table Create a migration for the failed queue jobs database table

queue:flush Flush all of the failed queue jobs

queue:forget Delete a failed queue job

queue:listen Listen to a given queue

queue:restart Restart queue worker daemons after their current job

queue:retry Retry a failed queue job

queue:table Create a migration for the queue jobs database table

queue:work Start processing jobs on the queue as a daemon

route

route:cache Create a route cache file for faster route registration

route:clear Remove the route cache file

route:list List all registered routes

schedule

schedule:run Run the scheduled commands

session

session:table Create a migration for the session database table

storage

storage:link Create a symbolic link from "public/storage" to "storage/app/public"

ui:auth Scaffold basic login and registration views and routes

vendor

vendor:publish Publish any publishable assets from vendor packages

view

view:cache Compile all of the application's Blade templates

view:clear Clear all compiled view files

: 俺のLaravel6 勉強用リンク【随時更新】
仕事で一時的にLaravel6を触ることになったので、その勉強用リンクです。命名規則・Laravelの命名規則 DB ・Laravel DBからデータ取得 …

: PHPフレームワーク Laravel Webアプリケーション開発バージョン8.x対応 [ 竹澤有貴 ] のメモ
PHPフレームワーク Laravel Webアプリケーション開発バージョン8.x対応 [ 竹澤有貴 ] 価格：4180円（税込、送料無料) (2022/7/22時点) 楽天で購入俺 …