さくらサーバー – Laravel～インストール

2022-02-21 / tau / コメントする

概要

さくらレンタルサーバーのスタンダードプランを利用していて、そこにLaravelをインストールしてみた記録。以下の2つの方法を試してみた。

composerをドキュメントルートに配置し、ドキュメントルート下にプロジェクトを作成
composerをドキュメントルートと別の場所に配置し、ドキュメントルート下で公開

ドキュメントルートへのインストール

composerのインストール

ssh接続後~/www(ドキュメントルート)に移動し、composer.pharをダウンロード。

FreeBSD 11.2-RELEASE-p14 (GENERIC) #0: Mon Aug 19 22:38:50 UTC 2019

Welcome to FreeBSD!

%cd ~/www
% curl -sS https://getcomposer.org/installer | php
All settings correct for using Composer
Downloading...

Composer (version 2.1.14) successfully installed to: /home/..../www/composer.phar
Use it: php composer.phar

FreeBSD 11.2-RELEASE-p14 (GENERIC) #0: Mon Aug 19 22:38:50 UTC 2019

Welcome to FreeBSD!

%cd ~/www

% curl -sS https://getcomposer.org/installer | php

All settings correct for using Composer

Downloading...

Composer (version 2.1.14) successfully installed to: /home/..../www/composer.phar

Use it: php composer.phar

インストール確認。

% php composer.phar
   ______
  / ____/___  ____ ___  ____  ____  ________  _____
/ /   / __ \/ __ `__ \/ __ \/ __ \/ ___/ _ \/ ___/
/ /___/ /_/ / / / / / / /_/ / /_/ (__  )  __/ /
\____/\____/_/ /_/ /_/ .___/\____/____/\___/_/
                    /_/
Composer version 2.1.14 2021-11-30 10:51:43

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
      --profile                  Display timing and memory usage information
      --no-plugins               Whether to disable plugins.
  -d, --working-dir=WORKING-DIR  If specified, use the given directory as working directory.
      --no-cache                 Prevent use of the cache
  -v|vv|vvv, --verbose           Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug

Available commands:
  about                Shows a short information about Composer.
  archive              Creates an archive of this composer package.
  browse               Opens the package's repository URL or homepage in your browser.
  cc                   Clears composer's internal package cache.
  check-platform-reqs  Check that platform requirements are satisfied.
  clear-cache          Clears composer's internal package cache.
  clearcache           Clears composer's internal package cache.
  config               Sets config options.
  create-project       Creates new project from a package into given directory.
  depends              Shows which packages cause the given package to be installed.
  diagnose             Diagnoses the system to identify common errors.
  dump-autoload        Dumps the autoloader.
  dumpautoload         Dumps the autoloader.
  exec                 Executes a vendored binary/script.
  fund                 Discover how to help fund the maintenance of your dependencies.
  global               Allows running commands in the global composer dir ($COMPOSER_HOME).
  help                 Displays help for a command
  home                 Opens the package's repository URL or homepage in your browser.
  i                    Installs the project dependencies from the composer.lock file if present, or falls back on the composer.json.
  info                 Shows information about packages.
  init                 Creates a basic composer.json file in current directory.
  install              Installs the project dependencies from the composer.lock file if present, or falls back on the composer.json.
  licenses             Shows information about licenses of dependencies.
  list                 Lists commands
  outdated             Shows a list of installed packages that have updates available, including their latest version.
  prohibits            Shows which packages prevent the given package from being installed.
  reinstall            Uninstalls and reinstalls the given package names
  remove               Removes a package from the require or require-dev.
  require              Adds required packages to your composer.json and installs them.
  run                  Runs the scripts defined in composer.json.
  run-script           Runs the scripts defined in composer.json.
  search               Searches for packages.
  self-update          Updates composer.phar to the latest version.
  selfupdate           Updates composer.phar to the latest version.
  show                 Shows information about packages.
  status               Shows a list of locally modified packages.
  suggests             Shows package suggestions.
  u                    Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.
  update               Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.
  upgrade              Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.
  validate             Validates a composer.json and composer.lock.
  why                  Shows which packages cause the given package to be installed.
  why-not              Shows which packages prevent the given package from being installed.

% php composer.phar

______

/ ____/___ ____ ___ ____ ____ ________ _____

/ / / __ \/ __ `__ \/ __ \/ __ \/ ___/ _ \/ ___/

/ /___/ /_/ / / / / / / /_/ / /_/ (__ ) __/ /

\____/\____/_/ /_/ /_/ .___/\____/____/\___/_/

/_/

Composer version 2.1.14 2021-11-30 10:51:43

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

--profile Display timing and memory usage information

--no-plugins Whether to disable plugins.

-d, --working-dir=WORKING-DIR If specified, use the given directory as working directory.

--no-cache Prevent use of the cache

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

Available commands:

about Shows a short information about Composer.

archive Creates an archive of this composer package.

browse Opens the package's repository URL or homepage in your browser.

cc Clears composer's internal package cache.

check-platform-reqs Check that platform requirements are satisfied.

clear-cache Clears composer's internal package cache.

clearcache Clears composer's internal package cache.

config Sets config options.

create-project Creates new project from a package into given directory.

depends Shows which packages cause the given package to be installed.

diagnose Diagnoses the system to identify common errors.

dump-autoload Dumps the autoloader.

dumpautoload Dumps the autoloader.

exec Executes a vendored binary/script.

fund Discover how to help fund the maintenance of your dependencies.

global Allows running commands in the global composer dir ($COMPOSER_HOME).

help Displays help for a command

home Opens the package's repository URL or homepage in your browser.

i Installs the project dependencies from the composer.lock file if present, or falls back on the composer.json.

info Shows information about packages.

init Creates a basic composer.json file in current directory.

install Installs the project dependencies from the composer.lock file if present, or falls back on the composer.json.

licenses Shows information about licenses of dependencies.

list Lists commands

outdated Shows a list of installed packages that have updates available, including their latest version.

prohibits Shows which packages prevent the given package from being installed.

reinstall Uninstalls and reinstalls the given package names

remove Removes a package from the require or require-dev.

require Adds required packages to your composer.json and installs them.

run Runs the scripts defined in composer.json.

run-script Runs the scripts defined in composer.json.

search Searches for packages.

self-update Updates composer.phar to the latest version.

selfupdate Updates composer.phar to the latest version.

show Shows information about packages.

status Shows a list of locally modified packages.

suggests Shows package suggestions.

u Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.

update Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.

upgrade Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.

validate Validates a composer.json and composer.lock.

why Shows which packages cause the given package to be installed.

why-not Shows which packages prevent the given package from being installed.

プロジェクト作成

~/wwwディレクトリーでテスト用のプロジェクトを作成。

% php composer.phar create-project laravel/laravel laravel_test
Creating a "laravel/laravel" project at "./laravel_test"
Installing laravel/laravel (v8.6.9)
  - Downloading laravel/laravel (v8.6.9)
  - Installing laravel/laravel (v8.6.9): Extracting archive
Created project in /home/..../www/laravel_test
> @php -r "file_exists('.env') || copy('.env.example', '.env');"
Loading composer repositories with package information
Updating dependencies
Lock file operations: 110 installs, 0 updates, 0 removals
  - Locking asm89/stack-cors (v2.0.3)
  - Locking brick/math (0.9.3)
  ........
  - Locking voku/portable-ascii (1.5.6)
  - Locking webmozart/assert (1.10.0)
Writing lock file
Installing dependencies from lock file (including require-dev)
Package operations: 110 installs, 0 updates, 0 removals
  - Downloading doctrine/inflector (2.0.4)
  - Downloading doctrine/lexer (1.2.1)
  ........
  - Downloading myclabs/deep-copy (1.10.2)
  - Downloading phpunit/phpunit (9.5.10)
  - Installing doctrine/inflector (2.0.4): Extracting archive
  - Installing doctrine/lexer (1.2.1): Extracting archive
  ........
  - Installing myclabs/deep-copy (1.10.2): Extracting archive
  - Installing phpunit/phpunit (9.5.10): Extracting archive
67 package suggestions were added by new dependencies, use `composer suggest` to see details.
Package swiftmailer/swiftmailer is abandoned, you should avoid using it. Use symfony/mailer instead.
Generating optimized autoload files
> Illuminate\Foundation\ComposerScripts::postAutoloadDump
> @php artisan package:discover --ansi
Discovered Package: facade/ignition
Discovered Package: fruitcake/laravel-cors
Discovered Package: laravel/sail
Discovered Package: laravel/sanctum
Discovered Package: laravel/tinker
Discovered Package: nesbot/carbon
Discovered Package: nunomaduro/collision
Package manifest generated successfully.
77 packages you are using are looking for funding.
Use the `composer fund` command to find out more!
> @php artisan vendor:publish --tag=laravel-assets --ansi --force
No publishable resources for tag [laravel-assets].
Publishing complete.
> @php artisan key:generate --ansi
Application key set successfully.

% php composer.phar create-project laravel/laravel laravel_test

Creating a "laravel/laravel" project at "./laravel_test"

Installing laravel/laravel (v8.6.9)

- Downloading laravel/laravel (v8.6.9)

- Installing laravel/laravel (v8.6.9): Extracting archive

Created project in /home/..../www/laravel_test

> @php -r "file_exists('.env') || copy('.env.example', '.env');"

Loading composer repositories with package information

Updating dependencies

Lock file operations: 110 installs, 0 updates, 0 removals

- Locking asm89/stack-cors (v2.0.3)

- Locking brick/math (0.9.3)

........

- Locking voku/portable-ascii (1.5.6)

- Locking webmozart/assert (1.10.0)

Writing lock file

Installing dependencies from lock file (including require-dev)

Package operations: 110 installs, 0 updates, 0 removals

- Downloading doctrine/inflector (2.0.4)

- Downloading doctrine/lexer (1.2.1)

........

- Downloading myclabs/deep-copy (1.10.2)

- Downloading phpunit/phpunit (9.5.10)

- Installing doctrine/inflector (2.0.4): Extracting archive

- Installing doctrine/lexer (1.2.1): Extracting archive

........

- Installing myclabs/deep-copy (1.10.2): Extracting archive

- Installing phpunit/phpunit (9.5.10): Extracting archive

67 package suggestions were added by new dependencies, use `composer suggest` to see details.

Package swiftmailer/swiftmailer is abandoned, you should avoid using it. Use symfony/mailer instead.

Generating optimized autoload files

> Illuminate\Foundation\ComposerScripts::postAutoloadDump

> @php artisan package:discover --ansi

Discovered Package: facade/ignition

Discovered Package: fruitcake/laravel-cors

Discovered Package: laravel/sail

Discovered Package: laravel/sanctum

Discovered Package: laravel/tinker

Discovered Package: nesbot/carbon

Discovered Package: nunomaduro/collision

Package manifest generated successfully.

77 packages you are using are looking for funding.

Use the `composer fund` command to find out more!

> @php artisan vendor:publish --tag=laravel-assets --ansi --force

No publishable resources for tag [laravel-assets].

Publishing complete.

> @php artisan key:generate --ansi

Application key set successfully.

動作確認

welcomeページ

ブラウザーでURLを指定。

http://ホスト名/laravel_test/

1	http://ホスト名/laravel_test/

結果は以下のエラー。

Forbidden
You don’t have permission to access this resource.

URLを以下のように指定して、welcomeページが無事表示される。ドキュメントルート下にプロジェクトを配置すると、publicディレクトリー内が公開対象になる。

~/www/laravel_test/public/

1	~/www/laravel_test/public/

ルーティング

以下のルーティングで'Hello'が表示される。

Route::get('/', function () {
    return 'Hello';
#    return view('welcome');
});

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

return 'Hello';

# return view('welcome');

});

コントローラー

TestControllerコントローラーを作成してアクションを定義し、その中で文字列を渡す。

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

class TestController extends Controller
{
    public function testAction() {
        return 'Hello!';
    } 
}

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

class TestController extends Controller

{

public function testAction() {

return 'Hello!';

}

ルーティングを以下のように変更。

Route::get('/', 'App\Http\Controllers\TestController@testAction');

1	Route::get('/', 'App\Http\Controllers\TestController@testAction');

これでブラウザーに'Hello!'と表示される。

ビュー

resource/viewsディレクトリーに以下のビューファイルtest.blade.phpを作成。

<!DOCTYPE html>
<html lang="ja">
  <head>
    <meta charset="utf-8">
    <title>{{ $title }}</title>
  </head>
  <body>
    <h1>Hello from blade!</h1>
  </body>
</html>

<!DOCTYPE html>

<head>

<title>{{ $title }}</title>

</head>

<body>

<h1>Hello from blade!</h1>

</body>

</html>

TestControllerを以下のように修正。

class TestController extends Controller
{
    public function testAction() {
        return view('test', ['title' => 'Laravel Test']);
    } 
}

class TestController extends Controller

{

public function testAction() {

return view('test', ['title' => 'Laravel Test']);

}

これでブラウザーにアクセスすると、タブのタイトルが'Laravel Test'となり、h1要素のフォントで'Hello from blade!'と表示される。

別ディレクトリーへのインストール

composerのインストール

ドキュメントルート下にプロジェクトを配置するのは気持ち悪いので、別に配置する。ここではユーザーホーム下のディレクトリーに配置してみる。ライブラリーを加えていないので、インストールはcomposer.pharを移動して配置するのみ。

まずcomposer_testディレクトリーを作ってその下にcomposer.pharを移動。また、先にドキュメントルートに作ったプロジェクトを削除しておく。

% mkdir composer_test
% mv www/composer.phar composer_test/
% rm -rf www/laravel_test/
% cd composer_test/

% mkdir composer_test

% mv www/composer.phar composer_test/

% rm -rf www/laravel_test/

% cd composer_test/

移動先でcomposerの動作確認。

% php composer.phar
   ______
  / ____/___  ____ ___  ____  ____  ________  _____
 / /   / __ \/ __ `__ \/ __ \/ __ \/ ___/ _ \/ ___/
/ /___/ /_/ / / / / / / /_/ / /_/ (__  )  __/ /
\____/\____/_/ /_/ /_/ .___/\____/____/\___/_/
                    /_/
Composer version 2.1.14 2021-11-30 10:51:43

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
      --profile                  Display timing and memory usage information
      --no-plugins               Whether to disable plugins.
  -d, --working-dir=WORKING-DIR  If specified, use the given directory as working directory.
      --no-cache                 Prevent use of the cache
  -v|vv|vvv, --verbose           Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug

Available commands:
  about                Shows a short information about Composer.
  archive              Creates an archive of this composer package.
  browse               Opens the package's repository URL or homepage in your browser.
  cc                   Clears composer's internal package cache.
  check-platform-reqs  Check that platform requirements are satisfied.
  clear-cache          Clears composer's internal package cache.
  clearcache           Clears composer's internal package cache.
  config               Sets config options.
  create-project       Creates new project from a package into given directory.
  depends              Shows which packages cause the given package to be installed.
  diagnose             Diagnoses the system to identify common errors.
  dump-autoload        Dumps the autoloader.
  dumpautoload         Dumps the autoloader.
  exec                 Executes a vendored binary/script.
  fund                 Discover how to help fund the maintenance of your dependencies.
  global               Allows running commands in the global composer dir ($COMPOSER_HOME).
  help                 Displays help for a command
  home                 Opens the package's repository URL or homepage in your browser.
  i                    Installs the project dependencies from the composer.lock file if present, or falls back on the composer.json.
  info                 Shows information about packages.
  init                 Creates a basic composer.json file in current directory.
  install              Installs the project dependencies from the composer.lock file if present, or falls back on the composer.json.
  licenses             Shows information about licenses of dependencies.
  list                 Lists commands
  outdated             Shows a list of installed packages that have updates available, including their latest version.
  prohibits            Shows which packages prevent the given package from being installed.
  reinstall            Uninstalls and reinstalls the given package names
  remove               Removes a package from the require or require-dev.
  require              Adds required packages to your composer.json and installs them.
  run                  Runs the scripts defined in composer.json.
  run-script           Runs the scripts defined in composer.json.
  search               Searches for packages.
  self-update          Updates composer.phar to the latest version.
  selfupdate           Updates composer.phar to the latest version.
  show                 Shows information about packages.
  status               Shows a list of locally modified packages.
  suggests             Shows package suggestions.
  u                    Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.
  update               Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.
  upgrade              Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.
  validate             Validates a composer.json and composer.lock.
  why                  Shows which packages cause the given package to be installed.
  why-not              Shows which packages prevent the given package from being installed.

% php composer.phar

______

/ ____/___ ____ ___ ____ ____ ________ _____

/ / / __ \/ __ `__ \/ __ \/ __ \/ ___/ _ \/ ___/

/ /___/ /_/ / / / / / / /_/ / /_/ (__ ) __/ /

\____/\____/_/ /_/ /_/ .___/\____/____/\___/_/

/_/

Composer version 2.1.14 2021-11-30 10:51:43

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

--profile Display timing and memory usage information

--no-plugins Whether to disable plugins.

-d, --working-dir=WORKING-DIR If specified, use the given directory as working directory.

--no-cache Prevent use of the cache

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

Available commands:

about Shows a short information about Composer.

archive Creates an archive of this composer package.

browse Opens the package's repository URL or homepage in your browser.

cc Clears composer's internal package cache.

check-platform-reqs Check that platform requirements are satisfied.

clear-cache Clears composer's internal package cache.

clearcache Clears composer's internal package cache.

config Sets config options.

create-project Creates new project from a package into given directory.

depends Shows which packages cause the given package to be installed.

diagnose Diagnoses the system to identify common errors.

dump-autoload Dumps the autoloader.

dumpautoload Dumps the autoloader.

exec Executes a vendored binary/script.

fund Discover how to help fund the maintenance of your dependencies.

global Allows running commands in the global composer dir ($COMPOSER_HOME).

help Displays help for a command

home Opens the package's repository URL or homepage in your browser.

i Installs the project dependencies from the composer.lock file if present, or falls back on the composer.json.

info Shows information about packages.

init Creates a basic composer.json file in current directory.

install Installs the project dependencies from the composer.lock file if present, or falls back on the composer.json.

licenses Shows information about licenses of dependencies.

list Lists commands

outdated Shows a list of installed packages that have updates available, including their latest version.

prohibits Shows which packages prevent the given package from being installed.

reinstall Uninstalls and reinstalls the given package names

remove Removes a package from the require or require-dev.

require Adds required packages to your composer.json and installs them.

run Runs the scripts defined in composer.json.

run-script Runs the scripts defined in composer.json.

search Searches for packages.

self-update Updates composer.phar to the latest version.

selfupdate Updates composer.phar to the latest version.

show Shows information about packages.

status Shows a list of locally modified packages.

suggests Shows package suggestions.

u Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.

update Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.

upgrade Upgrades your dependencies to the latest version according to composer.json, and updates the composer.lock file.

validate Validates a composer.json and composer.lock.

why Shows which packages cause the given package to be installed.

why-not Shows which packages prevent the given package from being installed.

プロジェクトの作成

composer_testディレクトリー下に先と同じ手順でプロジェクトを作成。

公開ディレクトリーへのシンボリックリンク

公開ディレクトリー以外に配置したプロジェクトは、クライアントからは認識されない。そこでプロジェクトのpublicディレクトリーのシンボリックリンクをドキュメントルート下に作成する。

ここではシンボリックリンク名をプロジェクト名と同じ名前にして、~/wwwディレクトリー下に作成。

% ln -s ~/laravel/laravel_test/public/ ~/www/laravel_test

1	% ln -s ~/laravel/laravel_test/public/ ~/www/laravel_test

この状態で以下のURLを指定するとwelcomeページが表示される。

http://taustation.com/laravel_test/

1	http://taustation.com/laravel_test/

コントローラー・ビュー

新しいプロジェクトでTestControllerコントローラーを作成。

% php artisan make:controller TestController
Controller created successfully.

% php artisan make:controller TestController

Controller created successfully.

ビューを呼び出すアクションを定義。

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

class TestController extends Controller
{
    public function testAction() {
        return view('test', ['title' => 'Laravel Test']);
    }
}

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

class TestController extends Controller

{

public function testAction() {

return view('test', ['title' => 'Laravel Test']);

}

ルーティングを定義。

Route::get('/', 'App\Http\Controllers\TestController@testAction');

1	Route::get('/', 'App\Http\Controllers\TestController@testAction');

resources/views/ディレクトリーにtest.blade.phpファイルを以下の内容で作成。

<!DOCTYPE html>
<html lang="ja">
  <head>
    <meta charset="utf-8">
    <title>{{ $title }}</title>
  </head>
  <body>
    <h1>Hello from blade!</h1>
  </body>
</html>

<!DOCTYPE html>

<head>

<title>{{ $title }}</title>

</head>

<body>

<h1>Hello from blade!</h1>

</body>

</html>

これでブラウザーにアクセスすると、タブのタイトルが'Laravel Test'となり、h1要素のフォントで'Hello from blade!'と表示される。

Laravelのバージョン指定

composerでプロジェクトを作成するときにLaravelのバージョン指定が可能だが、さくらサーバーでワイルドカードを使うとうまくいかない。

% php composer.phar create-project laravel/laravel laravel_test2 6.*
php: 照合パターンに合いません.

% php composer.phar create-project laravel/laravel laravel_test2 6.*

php: 照合パターンに合いません.

以下のようにワイルドカードを指定しない方法だと、無事にプロジェクトが作成される。

% php composer.phar create-project laravel/laravel laravel_test 6
Creating a "laravel/laravel" project at "./laravel_test"
Installing laravel/laravel (v6.0.0)
  - Downloading laravel/laravel (v6.0.0)
  - Installing laravel/laravel (v6.0.0): Extracting archive
Created project in /home/taustation/laravel/laravel_test
........
Package manifest generated successfully.
60 packages you are using are looking for funding.
Use the `composer fund` command to find out more!
> @php artisan key:generate --ansi
Application key set successfully.

% php composer.phar create-project laravel/laravel laravel_test 6

Creating a "laravel/laravel" project at "./laravel_test"

Installing laravel/laravel (v6.0.0)

- Downloading laravel/laravel (v6.0.0)

- Installing laravel/laravel (v6.0.0): Extracting archive

Created project in /home/taustation/laravel/laravel_test

........

Package manifest generated successfully.

60 packages you are using are looking for funding.

Use the `composer fund` command to find out more!

> @php artisan key:generate --ansi

Application key set successfully.

バージョン確認。

% cd laravel_test/
% php artisan --version
Laravel Framework 6.20.43

% cd laravel_test/

% php artisan --version

Laravel Framework 6.20.43

プロジェクトの設定

タイムゾーンとロケールをconfig/app.phpファイルで設定。

........
    'timezone' => 'Asia/Tokyo',
........
    'locale' => 'ja',
........

........

'timezone' => 'Asia/Tokyo',

........

'locale' => 'ja',

........

データベースはさくらサーバーの設定に従って、.envファイルで設定。DB_HOSTはmysql5.7アップグレード後の場合、phpMyAdminなどで使われるサーバー名。

DB_CONNECTION=mysql
DB_HOST=mysqlxxxx.db.sakura.ne.jp
DB_PORT=3306
DB_DATABASE=データベース名
DB_USERNAME=ユーザー名
DB_PASSWORD=********

DB_CONNECTION=mysql

DB_HOST=mysqlxxxx.db.sakura.ne.jp

DB_PORT=3306

DB_DATABASE=データベース名

DB_USERNAME=ユーザー名

DB_PASSWORD=********

Laravel – クエリービルダーでのエイリアス

2022-01-13 / tau / コメントする

概要

SQLではフィールド名やテーブル名のエイリアスを定義できるが、同じことがクエリービルダーでもできる。

フィールド名のエイリアスは、select()メソッドのフィールド名の引数にそのままASを書く
JOINされるテーブルのエイリアスはjoin()メソッドのテーブル名の引数にそのままASを書く
親テーブルのエイリアスはfrom()メソッドの引数で指定する

SQLの記述

元のSQLとして以下を使う。

SELECT
  customers.name,
  emails.address
FROM customers
LEFT JOIN emails
  ON customers.id = emails.id;

SELECT

customers.name,

emails.address

FROM customers

LEFT JOIN emails

ON customers.id = emails.id;

このSQLでフィールド名とテーブル名のエイリアスを以下のように定義する。これと同じことをクエリービルダーで書いていく。

SELECT
  c.name AS customer_name,
  e.address AS email_address
FROM customers as c
LEFT JOIN emails as e
  ON c.id = e.id;

SELECT

c.name AS customer_name,

e.address AS email_address

FROM customers as c

LEFT JOIN emails as e

ON c.id = e.id;

クエリービルダーの記述

オリジナル

元のSQLに対応するクエリービルダーは以下のとおり。

$customers = Customer::
    select('customers.name',
           'emails.address')
    ->leftJoin('emails', 'customers.id', '=', 'emails.customer_id')
    ->get();

$customers = Customer::

select('customers.name',

'emails.address')

->leftJoin('emails', 'customers.id', '=', 'emails.customer_id')

->get();

フィールド名のエイリアス

フィールド名のエイリアスは、select()メソッドのフィールド名の引数にas aliasで定義。

$customers = Customer::
    select('customers.name as customer_name',
           'emails.address as mail_address')
    ->leftJoin('emails', 'customers.id', '=', 'emails.customer_id')
    ->get();

$customers = Customer::

select('customers.name as customer_name',

'emails.address as mail_address')

->leftJoin('emails', 'customers.id', '=', 'emails.customer_id')

->get();

JOINされるテーブルのエイリアス

親テーブルに結合される子テーブルのエイリアスは、join()メソッドのテーブルを指定する引数(第1引数)でas aliasを指定する。

$customers = Customer::
    select('customers.name as customer_name',
           'e.address as mail_address')
    ->leftJoin('emails as e', 'customers.id', '=', 'e.customer_id')
    ->get();

$customers = Customer::

select('customers.name as customer_name',

'e.address as mail_address')

->leftJoin('emails as e', 'customers.id', '=', 'e.customer_id')

->get();

親テーブルのエイリアス

親テーブルのエイリアスは、最初にfrom()メソッドを呼んでその引数で指定する。select()メソッドはメソッドチェーンで繋げる。

$customers = Customer::from('customers as c')
    ->select('c.name as customer_name',
           'e.address as mail_address')
    ->leftJoin('emails as e', 'c.id', '=', 'e.customer_id')
    ->get();

$customers = Customer::from('customers as c')

->select('c.name as customer_name',

'e.address as mail_address')

->leftJoin('emails as e', 'c.id', '=', 'e.customer_id')

->get();

Laravel – Collection – 先頭・末尾などの例外処理

2021-12-19 / tau / コメントする

コールバックを用いるメソッドで、コールバック内で先頭要素や末尾要素に応じた処理をしたい場合、クロージャ―のuseを使う。ただしこの方法は、全要素に対して先頭・末尾などの判定処理が行われる。

以下の例ではコレクションの要素を並べた1つの文字列を返すが、文字列の最初に'('、最後に')'を付加し、要素間には','を置いている。

>>> $c->reduce(function($carry, $element) use($c) {
...   return
...     ($element == $c->first() ? '(' : '') .
...     $carry .
...     $element . ($element == $c->last() ? ')' : ', ');
... }, '')
=> "(A, B, C, D, E)"
>

>>> $c->reduce(function($carry, $element) use($c) {

... return

... ($element == $c->first() ? '(' : '') .

... $carry .

... $element . ($element == $c->last() ? ')' : ', ');

... }, '')

=> "(A, B, C, D, E)"

要点は以下のとおり。

クロージャ―のuseで呼び出し元のコレクションインスタンスを指定
クロージャ―内でコレクションインスタンスの先頭要素／末尾要素に等しいかどうかを判定し、それらに応じた処理を実行

$c->reduce(function($carry, $element) use($c) {
  return 
    ($element == $c->first() ? '(' : '') .
    $carry .
    $element . ($element == $c->last() ? ')' : ', ');
}, '')

$c->reduce(function($carry, $element) use($c) {

return

($element == $c->first() ? '(' : '') .

$carry .

$element . ($element == $c->last() ? ')' : ', ');

}, '')

Laravel – Collection – グルーピングと集計

2021-12-19 / tau / コメントする

概要

LaravelのCollectionには、count()やsum()などの集計用メソッドが準備されている。groupBy()メソッドも準備されているが、これはグルーピングされたキー列の値で配列をまとめるだけなので、直接groupBy()の結果をcount()などに適用することはできない。

たとえば以下のようなコレクションがあって、

>>> $p
=> Illuminate\Support\Collection {#4355
     all: [
       [
         "name" => "Egawa",
         "gender" => "male",
         "height" => 175,
       ],
       [
         "name" => "Iwata",
         "gender" => "female",
         "height" => 163,
       ],
       [
         "name" => "Okada",
         "gender" => "male",
         "height" => 168,
       ],
       [
         "name" => "Ando",
         "gender" => "male",
         "height" => 182,
       ],
       [
         "name" => "Ueda",
         "gender" => "female",
         "height" => 167,
       ],
     ],
   }

>>> $p

=> Illuminate\Support\Collection {#4355

all: [

[

"name" => "Egawa",

"gender" => "male",

"height" => 175,

[

"name" => "Iwata",

"gender" => "female",

"height" => 163,

[

"name" => "Okada",

"gender" => "male",

"height" => 168,

[

"name" => "Ando",

"gender" => "male",

"height" => 182,

[

"name" => "Ueda",

"gender" => "female",

"height" => 167,

}

groupBy()によってgenderでグルーピングすると以下のようなコレクションが得られる。

>>> $p->groupBy('gender')
=> Illuminate\Support\Collection {#4341
     all: [
       "male" => Illuminate\Support\Collection {#4367
         all: [
           [
             "name" => "Egawa",
             "gender" => "male",
             "height" => 175,
           ],
           [
             "name" => "Okada",
             "gender" => "male",
             "height" => 168,
           ],
           [
             "name" => "Ando",
             "gender" => "male",
             "height" => 182,
           ],
         ],
       },
       "female" => Illuminate\Support\Collection {#4360
         all: [
           [
             "name" => "Iwata",
             "gender" => "female",
             "height" => 163,
           ],
           [
             "name" => "Ueda",
             "gender" => "female",
             "height" => 167,
           ],
         ],
       },
     ],
   }

>>> $p->groupBy('gender')

=> Illuminate\Support\Collection {#4341

all: [

"male" => Illuminate\Support\Collection {#4367

all: [

[

"name" => "Egawa",

"gender" => "male",

"height" => 175,

[

"name" => "Okada",

"gender" => "male",

"height" => 168,

[

"name" => "Ando",

"gender" => "male",

"height" => 182,

"female" => Illuminate\Support\Collection {#4360

all: [

[

"name" => "Iwata",

"gender" => "female",

"height" => 163,

[

"name" => "Ueda",

"gender" => "female",

"height" => 167,

}

この結果を単にcount()メソッドに渡すと、maleとfemaleの2つのグループあることから、グループ数に対応する'2'が返される。

>>> $p->groupBy('gender')->count()
=> 2

1 2	>>> $p->groupBy('gender')->count() => 2

これを、male/female各グループの要素数をカウントしたり、グループごとの身長の集計値を出したりするようにしたい。

map()メソッドの利用したグループ処理

map()メソッドはコールバックを引数にとり、そのコールバックの引数には元のコレクションの各要素が順次与えられる。

たとえば先のグルーピングの結果をmap()メソッドに渡すと、2つの要素maleとfemale(その中身は連想配列の配列)が順次与えられる。これらグループ化された配列に対して、Collectionの集計メソッドを使うことができる。

$collection->groupBy('キー')->map(function($group) { return $group->処理; });

1	$collection->groupBy('キー')->map(function($group) { return $group->処理; });

この場合、グループ化された1つ目のグループの各要素に関する処理、2つ目のグループの各要素に関する処理・・・と実行される。

集計例

count()の例

性別ごとの人数をカウントする例。

>>> $p->groupBy('gender')->map(function($gender) { return $gender->count(); })
=> Illuminate\Support\Collection {#4333
     all: [
       "male" => 3,
       "female" => 2,
     ],
   }

>>> $p->groupBy('gender')->map(function($gender) { return $gender->count(); })

=> Illuminate\Support\Collection {#4333

all: [

"male" => 3,

"female" => 2,

}

average()の例

性別ごとの平均身長を計算する例。

>>> $p->groupBy('gender')->map(function($gender) { return $gender->average('height'); })
=> Illuminate\Support\Collection {#4358
     all: [
       "male" => 175,
       "female" => 165,
     ],
   }

>>> $p->groupBy('gender')->map(function($gender) { return $gender->average('height'); })

=> Illuminate\Support\Collection {#4358

all: [

"male" => 175,

"female" => 165,

}

Laravel – Collection – コールバックによる操作

2021-12-19 / tau / コメントする

概要

Collectionには引数にコールバックをとるメソッドが準備されている。これらはコレクションの各要素にコールバックで定義された処理をし、その結果に応じて要素の絞り込み、各要素への処理の適用、各要素を用いた縮退処理を行う。

いずれも元のコレクションは変更されず、戻り値として新たなコレクションやオブジェクト、変数などが返される。

単純配列型に対するメソッド

filter()～条件に合う要素の絞り込み

filter()は、設定した条件により元のコレクションの要素の一部を取り出す。

filter()のコールバックは、各要素に対応する引数を1つ撮る
コールバックの戻り値は、受け取った要素に対する条件の評価結果(true/false)
filter()の戻り値として、元のコレクションのうち条件に合致する要素だけが残ったコレクションが返される

$collection->filter(function($element) { return $elementの抽出条件; });

1	$collection->filter(function($element) { return $elementの抽出条件; });

以下の例は、奇数要素のみを取り出す例。

$collection = collect([1, 2, 3, 4, 5]);

$filtered = $collection->filter(function($element) {
    return $element % 2 == 1;
});

dd($filtered);

$collection = collect([1, 2, 3, 4, 5]);

$filtered = $collection->filter(function($element) {

return $element % 2 == 1;

});

dd($filtered);

基数要素だけが残ったコレクションが得られる。

Illuminate\Support\Collection {#1274 ▼
  #items: array:3 [▼
    0 => 1
    2 => 3
    4 => 5
  ]
}

Illuminate\Support\Collection {#1274 ▼

#items: array:3 [▼

0 => 1

2 => 3

4 => 5

]

}

以下は4未満の要素のみを残す例。

$filtered = $collection->filter(function($element) {
    return $element < 4;
});

$filtered = $collection->filter(function($element) {

return $element < 4;

});

4未満の要素のみが残っている。

Illuminate\Support\Collection {#1274 ▼
  #items: array:3 [▼
    0 => 1
    1 => 2
    2 => 3
  ]
}

Illuminate\Support\Collection {#1274 ▼

#items: array:3 [▼

0 => 1

1 => 2

2 => 3

]

}

reject()～条件に合わない要素の絞り込み

reject()はfilter()の逆で、条件に合う要素を取り除いた残りのコレクションを返す。

$collection->reject(function($element) { return $elementの除外条件; });

1	$collection->reject(function($element) { return $elementの除外条件; });

以下は奇数要素のみを取り除く例。

$rejected = $collection->filter(function($element) {
    return $element % 2 == 1;
});

$rejected = $collection->filter(function($element) {

return $element % 2 == 1;

});

奇数要素が除かれ、偶数要素のみが残っている。

Illuminate\Support\Collection {#1271 ▼
  #items: array:2 [▼
    1 => 2
    3 => 4
  ]
}

Illuminate\Support\Collection {#1271 ▼

#items: array:2 [▼

1 => 2

3 => 4

]

}

map()～各要素への処理のマッピング

map()は設定した処理を元のコレクションの全要素に適用する。

map()のコールバックは、各要素に対応する引数を1つ撮る
コールバックの戻り値は、処理の結果更新される各要素の値
map()の戻り値として、元のコレクションの各要素にコールバックの処理が適用されたコレクションが返される

$collection->map(function($element) { return 加工後の$element; });

1	$collection->map(function($element) { return 加工後の$element; });

以下はコレクションの各要素を10倍する例。

$collection = collect([1, 2, 3, 4, 5]);

$mapped = $collection->map(function($element) {
    return $element * 10;
});

dd($mapped);

$collection = collect([1, 2, 3, 4, 5]);

$mapped = $collection->map(function($element) {

return $element * 10;

});

dd($mapped);

元の要素が10倍された結果になっている。

Illuminate\Support\Collection {#1271 ▼
  #items: array:5 [▼
    0 => 10
    1 => 20
    2 => 30
    3 => 40
    4 => 50
  ]
}

Illuminate\Support\Collection {#1271 ▼

#items: array:5 [▼

0 => 10

1 => 20

2 => 30

3 => 40

4 => 50

]

}

reduce()～縮退処理

ここでいう縮退とは、各要素やその加工結果を1つの結果にまとめ込んでいくものとする。

例として「コレクションの各要素の値の合計を求める」という処理の場合、「合計値」という結果に要素ごとの値を「足し込む」という処理になる。

このためreuce()はfilter()やmap()と以下の2点で異なる。

コールバックの引数は縮退の途中結果とその時点での要素の2つの引数をとる
reduce()自身が上記のコールバックと縮退処理の初期値の2つの引数をとる

コールバックについては以下のとおり。

reduce()のコールバックは、各時点までに蓄積された処理結果(たとえば$carry)と、その時点で処理される要素(たとえば$element)の2つの引数をとる
コールバックの戻り値は、受け取った$carryに対して$elementに関して処理された新たな$carryの内容
最終的なreduce()の戻り値として、$carryの縮退結果が返される

$collection->reduce(function($carry, $element) { return 次の$carryに渡す結果; });

1	$collection->reduce(function($carry, $element) { return 次の$carryに渡す結果; });

以下はコレクションの各要素の和を求める例で、縮退の初期値を0としてreduce()の第2引数で与え、第1引数のコールバックでは$carryに各要素の値を足し込んでいる。

$collection = collect([1, 2, 3, 4, 5]);

$reduced = $collection->reduce(function($carry, $element) {
    return $carry + $element;
}, 0);

dd($reduced);

$collection = collect([1, 2, 3, 4, 5]);

$reduced = $collection->reduce(function($carry, $element) {

return $carry + $element;

}, 0);

dd($reduced);

結果は1～5までの和になる。

15

以下は初期値を0→900に変更した例。

$reduced = $collection->reduce(function($carry, $element) {
    return $carry + $element;
}, 900);

$reduced = $collection->reduce(function($carry, $element) {

return $carry + $element;

}, 900);

初期値0ではなく900に対する足し込みとなっている。

915

915

また次の例では、要素の値を文字列として後ろにつなげていく。初期値には空文字列''を指定している。

$reduced = $collection->reduce(function($carry, $element) {
    return $carry . $element;
}, '');

$reduced = $collection->reduce(function($carry, $element) {

return $carry . $element;

}, '');

結果は以下のように、値がつなげられた文字列になる。

"12345"

"12345"

連想配列への適用

以下の連想配列を使う。

>>> $p
=> Illuminate\Support\Collection {#4355
     all: [
       [
         "name" => "Egawa",
         "gender" => "male",
         "height" => 175,
       ],
       [
         "name" => "Iwata",
         "gender" => "female",
         "height" => 163,
       ],
       [
         "name" => "Okada",
         "gender" => "male",
         "height" => 168,
       ],
       [
         "name" => "Ando",
         "gender" => "male",
         "height" => 182,
       ],
       [
         "name" => "Ueda",
         "gender" => "female",
         "height" => 167,
       ],
     ],
   }

>>> $p

=> Illuminate\Support\Collection {#4355

all: [

[

"name" => "Egawa",

"gender" => "male",

"height" => 175,

[

"name" => "Iwata",

"gender" => "female",

"height" => 163,

[

"name" => "Okada",

"gender" => "male",

"height" => 168,

[

"name" => "Ando",

"gender" => "male",

"height" => 182,

[

"name" => "Ueda",

"gender" => "female",

"height" => 167,

}

filter()

連想配列のコレクションにフィルターを適用する場合。

>>> $p->filter(function($element) { return $element['gender'] === 'female'; })
=> Illuminate\Support\Collection {#4352
     all: [
       1 => [
         "name" => "Iwata",
         "gender" => "female",
         "height" => 163,
       ],
       4 => [
         "name" => "Ueda",
         "gender" => "female",
         "height" => 167,
       ],
     ],
   }

>>> $p->filter(function($element) { return $element['gender'] === 'female'; })

=> Illuminate\Support\Collection {#4352

all: [

1 => [

"name" => "Iwata",

"gender" => "female",

"height" => 163,

4 => [

"name" => "Ueda",

"gender" => "female",

"height" => 167,

}

ただしこれはwhere()による操作で代替できる。

>>> $p->where('gender', '=', 'female')
=> Illuminate\Support\Collection {#4358
     all: [
       1 => [
         "name" => "Iwata",
         "gender" => "female",
         "height" => 163,
       ],
       4 => [
         "name" => "Ueda",
         "gender" => "female",
         "height" => 167,
       ],
     ],
   }

>>> $p->where('gender', '=', 'female')

=> Illuminate\Support\Collection {#4358

all: [

1 => [

"name" => "Iwata",

"gender" => "female",

"height" => 163,

4 => [

"name" => "Ueda",

"gender" => "female",

"height" => 167,

}

map()

連想配列のコレクションにmap()を適用した例。genderキー列の値に応じて内容を書き換えている('male'→'m'、'female'→'f')。

>>> $p->map(function($element) {
...   $element['gender'] = ($element['gender'] === 'male' ? 'm' : 'f');
...   return $element;
... })
=> Illuminate\Support\Collection {#4351
     all: [
       [
         "name" => "Egawa",
         "gender" => "m",
         "height" => 175,
       ],
       [
         "name" => "Iwata",
         "gender" => "f",
         "height" => 163,
       ],
       [
         "name" => "Okada",
         "gender" => "m",
         "height" => 168,
       ],
       [
         "name" => "Ando",
         "gender" => "m",
         "height" => 182,
       ],
       [
         "name" => "Ueda",
         "gender" => "f",
         "height" => 167,
       ],
     ],
   }

>>> $p->map(function($element) {

... $element['gender'] = ($element['gender'] === 'male' ? 'm' : 'f');

... return $element;

... })

=> Illuminate\Support\Collection {#4351

all: [

[

"name" => "Egawa",

"gender" => "m",

"height" => 175,

[

"name" => "Iwata",

"gender" => "f",

"height" => 163,

[

"name" => "Okada",

"gender" => "m",

"height" => 168,

[

"name" => "Ando",

"gender" => "m",

"height" => 182,

[

"name" => "Ueda",

"gender" => "f",

"height" => 167,

}

また以下の例では、groupBy()でグルーピングされたコレクションに対してmap()を適用し、グループごとの要素数をカウントしている(グルーピングと集計参照)。

>>> $p->groupBy('gender')->map(function($group) { return $group->count(); })
=> Illuminate\Support\Collection {#4364
     all: [
       "male" => 3,
       "female" => 2,
     ],
   }

>>> $p->groupBy('gender')->map(function($group) { return $group->count(); })

=> Illuminate\Support\Collection {#4364

all: [

"male" => 3,

"female" => 2,

}

reduce()

以下は連想配列のコレクションに対してreduce()を適用して、要素のheightキー列の値の二乗和を求め、その結果から分散を計算している。

>>> $sum2 = $p->reduce(function($carry, $element) {
...   return $carry + $element['height']^2;
... }, 0)
=> 849
>>> ($sum2 - $p->average('height')^2) / $p->count()
=> 135.2

>>> $sum2 = $p->reduce(function($carry, $element) {

... return $carry + $element['height']^2;

... }, 0)

=> 849

>>> ($sum2 - $p->average('height')^2) / $p->count()

=> 135.2

なおコールバックにuseを使い、処理中の要素がコレクションの末尾要素のときに、集計結果を使った計算をさせ、reduce()の結果が直接分散となるようにもできる。

$sum2 = $p->reduce(function($carry, $element) use($p) {
  if ($element == $p->last()) {
    $result = $carry + $element['height']^2;
    return ($result - ($p->average('height'))^2) / $p->count();
  } else {
    return $carry + $element['height']^2;
  }
}, 0)

$sum2 = $p->reduce(function($carry, $element) use($p) {

if ($element == $p->last()) {

$result = $carry + $element['height']^2;

return ($result - ($p->average('height'))^2) / $p->count();

} else {

return $carry + $element['height']^2;

}

}, 0)

Laravel – Collection – 基本操作

2021-12-19 / tau / コメントする

概要

コレクションは配列と同じように要素の集合体だが、細やかな操作・集計などのメソッドを備えている。

コレクションの作成～collect()ヘルパー

collect()ヘルパーの引数に配列を入れて、Collectionのインスタンスを得られる。

>>> $c = collect([1, 2, 3])
=> Illuminate\Support\Collection {#4318
     all: [
       1,
       2,
       3,
     ],
   }

>>> $c = collect([1, 2, 3])

=> Illuminate\Support\Collection {#4318

all: [

}

与えるのは連想配列でもよい。

>>> $b = collect(['one' => 1, 'two' => 2, 'three' => 3])
=> Illuminate\Support\Collection {#4321
     all: [
       "one" => 1,
       "two" => 2,
       "three" => 3,
     ],
   }

>>> $b = collect(['one' => 1, 'two' => 2, 'three' => 3])

=> Illuminate\Support\Collection {#4321

all: [

"one" => 1,

"two" => 2,

"three" => 3,

}

値の参照

ブラケット([])による参照

以下のような連想配列を準備する。

>>> $c
=> Illuminate\Support\Collection {#4318
     all: [
       0 => 1,
       "two" => 2,
       1 => 3,
     ],
   }

>>> $c

=> Illuminate\Support\Collection {#4318

all: [

0 => 1,

"two" => 2,

1 => 3,

}

配列のインデックスや連想配列のキーを指定するのと同じようにして、ブラケット([])で要素を参照できる。

>>> $c[0]
=> 1
>>> $c['two']
=> 2

>>> $c[0]

=> 1

>>> $c['two']

=> 2

get()メソッドによる参照

ブラケットの代わりにget()メソッドを用いても要素を参照できる。

>>> $c->get(0)
=> 1
>>> $c->get('two')
=> 2

>>> $c->get(0)

=> 1

>>> $c->get('two')

=> 2

random()～ランダムに1つ取り出す

random()メソッドを呼び出すたびに、コレクションの要素がランダムに1つ返される。

>>> $c->random()
=> 2
>>> $c->random()
=> 1

>>> $c->random()

=> 2

>>> $c->random()

=> 1

first()/last()

first()メソッド／last()メソッドは、コレクションの先頭要素／末尾要素を返す。

>>> $c->first()
=> 1
>>> $c->last()
=> 3
>

>>> $c->first()

=> 1

>>> $c->last()

=> 3

値のセット

ブラケット([])によるセット

配列や連想配列と同じように、インデックスやキーを指定して値をセットできる。

>>> $c[0] = 'first'
=> "first"
>>> $c['two'] = 'second'
=> "second"
>>> $c
=> Illuminate\Support\Collection {#4318
     all: [
       0 => "first",
       "two" => "second",
       1 => 3,
     ],
   }

>>> $c[0] = 'first'

=> "first"

>>> $c['two'] = 'second'

=> "second"

>>> $c

=> Illuminate\Support\Collection {#4318

all: [

0 => "first",

"two" => "second",

1 => 3,

}

put()メソッドによるセット

put(index/key, value)でも指定したインデックス／キーの要素の値をセットできる。

>>> $c->put(1, 'third')
=> Illuminate\Support\Collection {#4330
     all: [
       0 => "first",
       "two" => "second",
       1 => "third",
     ],
   }

>>> $c->put(1, 'third')

=> Illuminate\Support\Collection {#4330

all: [

0 => "first",

"two" => "second",

1 => "third",

}

空のコレクションと要素の追加

空のコレクションの生成

collect()メソッドの引数を省略すると、空のCollectionインスタンスが生成される。

>>> $c = collect()
=> Illuminate\Support\Collection {#4312
     all: [],
   }

>>> $c = collect()

=> Illuminate\Support\Collection {#4312

all: [],

}

ブラケット([])による追加

存在しないインデックスやキーを指定して値をセットすると、新たに要素が追加される。

>>> $c[0] = 1
=> 1
>>> $c['two'] = 2
=> 2
>>> $c
=> Illuminate\Support\Collection {#4312
     all: [
       0 => 1,
       "two" => 2,
     ],
   }

>>> $c[0] = 1

=> 1

>>> $c['two'] = 2

=> 2

>>> $c

=> Illuminate\Support\Collection {#4312

all: [

0 => 1,

"two" => 2,

}

ブラケットでキーを指定しないと、末尾にデータが追加される。

>>> $c[] = 3
=> 3
>>> $c
=> Illuminate\Support\Collection {#4312
     all: [
       0 => 1,
       "two" => 2,
       1 => 3,
     ],
   }

>>> $c[] = 3

=> 3

>>> $c

=> Illuminate\Support\Collection {#4312

all: [

0 => 1,

"two" => 2,

1 => 3,

}

put()メソッドによる追加

put(index/key, value)でも要素を追加できる。

>>> $c->put('three', 3)
=> Illuminate\Support\Collection {#4312
     all: [
       0 => 1,
       "two" => 2,
       "three" => 3,
     ],
   }

>>> $c->put('three', 3)

=> Illuminate\Support\Collection {#4312

all: [

0 => 1,

"two" => 2,

"three" => 3,

}

値の引き抜き

pull()メソッドは、指定したインデックス／キーの要素をコレクションから抜き取る。その要素はコレクションから削除され、戻り値として返される。

以下の例では、pull()メソッドで2つの要素を抜き取っている。

>>> $c->pull(0)
=> "first"
>>> $c->pull('two')
=> "second"
>>> $c
=> Illuminate\Support\Collection {#4316
     all: [
       1 => 3,
     ],
   }

>>> $c->pull(0)

=> "first"

>>> $c->pull('two')

=> "second"

>>> $c

=> Illuminate\Support\Collection {#4316

all: [

1 => 3,

}

スタック

Collectionにはpush()メソッドとpop()メソッドが準備されていて、コレクションをスタックのように扱える。以下のデータで確認する。

>>> $c = collect([1, 2, 3])
=> Illuminate\Support\Collection {#4329
     all: [
       1,
       2,
       3,
     ],
   }

>>> $c = collect([1, 2, 3])

=> Illuminate\Support\Collection {#4329

all: [

}

push()メソッド

push()メソッドは、引数で指定した値をコレクションの末尾に追加する。

>>> $c->push(4)
=> Illuminate\Support\Collection {#4329
     all: [
       1,
       2,
       3,
       4,
     ],
   }
>>> $c->push('five')
=> Illuminate\Support\Collection {#4329
     all: [
       1,
       2,
       3,
       4,
       "five",
     ],
   }

>>> $c->push(4)

=> Illuminate\Support\Collection {#4329

all: [

}

>>> $c->push('five')

=> Illuminate\Support\Collection {#4329

all: [

"five",

}

2つ以上の引数を指定してもエラーにはならないが、1つ目の引数の値のみが使われてプッシュされる。

pop()メソッド

pop()メソッドは、コレクションの末尾から要素を抜き取り、戻り値として返す。

>>> $c->pop()
=> "five"
>>> $c->pop()
=> 4
>>> $c
=> Illuminate\Support\Collection {#4329
     all: [
       1,
       2,
       3,
     ],
   }

>>> $c->pop()

=> "five"

>>> $c->pop()

=> 4

>>> $c

=> Illuminate\Support\Collection {#4329

all: [

}

空かどうかの確認

isEmpty()/isNotEmpty()は、コレクションが空か／空でないかを確認して結果をtrue/falseで返す。

>>> $c->isEmpty()
=> false
>>> $c->isNotEmpty()
=> true

>>> $c->isEmpty()

=> false

>>> $c->isNotEmpty()

=> true

nullの場合はCollectionのインスタンスではないので、これらのメソッドの結果はエラーになる。

コレクションから配列への変換

toArray()メソッドは、コレクションの内容を配列として返す。toArray()のほかall()メソッドでも同じ結果が得られる。

>>> $c = collect([1, 'two' => 2, 'three'])
=> Illuminate\Support\Collection {#4318
     all: [
       0 => 1,
       "two" => 2,
       1 => "three",
     ],
   }
>>> $c->toArray()
=> [
     0 => 1,
     "two" => 2,
     1 => "three",
   ]

>>> $c = collect([1, 'two' => 2, 'three'])

=> Illuminate\Support\Collection {#4318

all: [

0 => 1,

"two" => 2,

1 => "three",

}

>>> $c->toArray()

=> [

0 => 1,

"two" => 2,

1 => "three",

]

Laravel – Collection – 集計・加工

2021-12-19 / tau / コメントする

集計

コレクションの要素の個数、合計値、平均などを求めるメソッドが準備されている。これらのメソッドは、コレクションの要素に基づいた処理結果を1つの数値として返す。

クエリービルダーのメソッドでも書いたが、データベースに対するクエリーに関するメソッドというよりも、Collectionの要素を集計するメソッドととらえた方がよい。

以下の2つのコレクション$cと$pで確認する。

>>> $c = collect([1,2,3,4,5])
=> Illuminate\Support\Collection {#4332
     all: [
       1,
       2,
       3,
       4,
       5,
     ],
   }
>>> $p
=> Illuminate\Support\Collection {#4355
     all: [
       [
         "name" => "Egawa",
         "gender" => "male",
         "height" => 175,
       ],
       [
         "name" => "Iwata",
         "gender" => "female",
         "height" => 163,
       ],
       [
         "name" => "Okada",
         "gender" => "male",
         "height" => 168,
       ],
       [
         "name" => "Ando",
         "gender" => "male",
         "height" => 182,
       ],
       [
         "name" => "Ueda",
         "gender" => "female",
         "height" => 167,
       ],
     ],
   }

>>> $c = collect([1,2,3,4,5])

=> Illuminate\Support\Collection {#4332

all: [

}

>>> $p

=> Illuminate\Support\Collection {#4355

all: [

[

"name" => "Egawa",

"gender" => "male",

"height" => 175,

[

"name" => "Iwata",

"gender" => "female",

"height" => 163,

[

"name" => "Okada",

"gender" => "male",

"height" => 168,

[

"name" => "Ando",

"gender" => "male",

"height" => 182,

[

"name" => "Ueda",

"gender" => "female",

"height" => 167,

}

count()～要素数

コレクションの要素の数を返す。引数を指定しても無視される。

>>> $c->count()
=> 2
>>> $p->count()
=> 5
>>> $p->count('foo')
=> 5

>>> $c->count()

=> 2

>>> $p->count()

=> 5

>>> $p->count('foo')

=> 5

max()/min()～最大値・最小値

コレクションの要素の最大値／最小値を返す。

>>> $c->max()
=> 5
>>> $c->min()
=> 1

>>> $c->max()

=> 5

>>> $c->min()

=> 1

連想配列を要素とする配列の場合は、キーを指定してその値の最小値／最大値を取得できる。値が文字列を含む場合、辞書順での最小値／最大値となる。

>>> $p->min('height')
=> 163
>>> $p->max('name')
=> "Ueda"

>>> $p->min('height')

=> 163

>>> $p->max('name')

=> "Ueda"

連想配列で引数を指定しないと、最初のキー列の値で判定され、その値を持つ要素が連想配列として返される。

>>> $p->min()
=> [
     "name" => "Ando",
     "gender" => "male",
     "height" => 182,
   ]
>>> $p->max()
=> [
     "name" => "Ueda",
     "gender" => "female",
     "height" => 167,
   ]

>>> $p->min()

=> [

"name" => "Ando",

"gender" => "male",

"height" => 182,

]

>>> $p->max()

=> [

"name" => "Ueda",

"gender" => "female",

"height" => 167,

]

sum()/average()/avg()～合計値・平均値

コレクションの要素の合計値／平均値を返す。

>>> $c->sum()
=> 15
>>> $c->average()
=> 3
>>> $c->avg()
=> 3

>>> $c->sum()

=> 15

>>> $c->average()

=> 3

>>> $c->avg()

=> 3

連想配列の場合は、集計するキー列を指定する。引数を指定しないとエラー、文字列を値に含むキーを指定すると警告が出て結果は0になる。

>>> $p->sum('height')
=> 855
>>> $p->average('height')
=> 171

>>> $p->sum('height')

=> 855

>>> $p->average('height')

=> 171

並べ替え

コレクションの要素を並べ替え、結果のコレクションを返す。元のコレクションは影響を受けない。

reverse()～逆順に並べる

reverse()メソッドは、元のコレクションの要素を逆順にしたコレクションを返す。引数を指定しても無視される。

>>> $c->reverse()
=> Illuminate\Support\Collection {#4372
     all: [
       4 => 5,
       3 => 4,
       2 => 3,
       1 => 2,
       0 => 1,
     ],
   }
>>> $p->reverse()
=> Illuminate\Support\Collection {#4371
     all: [
       4 => [
         "name" => "Ueda",
         "gender" => "female",
         "height" => 167,
       ],
       3 => [
         "name" => "Ando",
         "gender" => "male",
         "height" => 182,
       ],
       2 => [
         "name" => "Okada",
         "gender" => "male",
         "height" => 168,
       ],
       1 => [
         "name" => "Iwata",
         "gender" => "female",
         "height" => 163,
       ],
       0 => [
         "name" => "Egawa",
         "gender" => "male",
         "height" => 175,
       ],
     ],
   }

>>> $c->reverse()

=> Illuminate\Support\Collection {#4372

all: [

4 => 5,

3 => 4,

2 => 3,

1 => 2,

0 => 1,

}

>>> $p->reverse()

=> Illuminate\Support\Collection {#4371

all: [

4 => [

"name" => "Ueda",

"gender" => "female",

"height" => 167,

3 => [

"name" => "Ando",

"gender" => "male",

"height" => 182,

2 => [

"name" => "Okada",

"gender" => "male",

"height" => 168,

1 => [

"name" => "Iwata",

"gender" => "female",

"height" => 163,

0 => [

"name" => "Egawa",

"gender" => "male",

"height" => 175,

}

shuffle()～シャッフル

shuffle()メソッドは、元のコレクションの要素の値をシャッフルしたコレクションを返す。

>>> $c->shuffle()
=> Illuminate\Support\Collection {#4367
     all: [
       1,
       3,
       4,
       5,
       2,
     ],
   }
>>> $p->shuffle()
=> Illuminate\Support\Collection {#4376
     all: [
       [
         "name" => "Ando",
         "gender" => "male",
         "height" => 182,
       ],
       [
         "name" => "Ueda",
         "gender" => "female",
         "height" => 167,
       ],
       [
         "name" => "Egawa",
         "gender" => "male",
         "height" => 175,
       ],
       [
         "name" => "Okada",
         "gender" => "male",
         "height" => 168,
       ],
       [
         "name" => "Iwata",
         "gender" => "female",
         "height" => 163,
       ],
     ],
   }

>>> $c->shuffle()

=> Illuminate\Support\Collection {#4367

all: [

}

>>> $p->shuffle()

=> Illuminate\Support\Collection {#4376

all: [

[

"name" => "Ando",

"gender" => "male",

"height" => 182,

[

"name" => "Ueda",

"gender" => "female",

"height" => 167,

[

"name" => "Egawa",

"gender" => "male",

"height" => 175,

[

"name" => "Okada",

"gender" => "male",

"height" => 168,

[

"name" => "Iwata",

"gender" => "female",

"height" => 163,

}

sort()～昇順ソート

sort()メソッドは元のコレクションの値の昇順で要素を並べ替える。

>>> $c
=> Illuminate\Support\Collection {#4369
     all: [
       2,
       3,
       5,
       4,
       1,
     ],
   }
>>> $c->sort()
=> Illuminate\Support\Collection {#4373
     all: [
       4 => 1,
       0 => 2,
       1 => 3,
       3 => 4,
       2 => 5,
     ],
   }

>>> $c

=> Illuminate\Support\Collection {#4369

all: [

}

>>> $c->sort()

=> Illuminate\Support\Collection {#4373

all: [

4 => 1,

0 => 2,

1 => 3,

3 => 4,

2 => 5,

}

連想配列型でも使えるが、引数を指定するとエラーになる。引数なしで呼ぶと、1つ目のキー列の内容でソートされる。

>>> $p->sort()
=> Illuminate\Support\Collection {#4358
     all: [
       3 => [
         "name" => "Ando",
         "gender" => "male",
         "height" => 182,
       ],
       0 => [
         "name" => "Egawa",
         "gender" => "male",
         "height" => 175,
       ],
       1 => [
         "name" => "Iwata",
         "gender" => "female",
         "height" => 163,
       ],
       2 => [
         "name" => "Okada",
         "gender" => "male",
         "height" => 168,
       ],
       4 => [
         "name" => "Ueda",
         "gender" => "female",
         "height" => 167,
       ],
     ],
   }

>>> $p->sort()

=> Illuminate\Support\Collection {#4358

all: [

3 => [

"name" => "Ando",

"gender" => "male",

"height" => 182,

0 => [

"name" => "Egawa",

"gender" => "male",

"height" => 175,

1 => [

"name" => "Iwata",

"gender" => "female",

"height" => 163,

2 => [

"name" => "Okada",

"gender" => "male",

"height" => 168,

4 => [

"name" => "Ueda",

"gender" => "female",

"height" => 167,

}

sortBy()～引数キーの昇順

sortBy()メソッドは引数にキーを指定して、そのキーの値の昇順で要素を並べ替えたコレクションを返す。

以下の例ではnameを指定して名前の昇順で並べ替えている。文字列は辞書順でソートされる。

>>> $p->sortBy('name')
=> Illuminate\Support\Collection {#4372
     all: [
       3 => [
         "name" => "Ando",
         "gender" => "male",
         "height" => 182,
       ],
       0 => [
         "name" => "Egawa",
         "gender" => "male",
         "height" => 175,
       ],
       1 => [
         "name" => "Iwata",
         "gender" => "female",
         "height" => 163,
       ],
       2 => [
         "name" => "Okada",
         "gender" => "male",
         "height" => 168,
       ],
       4 => [
         "name" => "Ueda",
         "gender" => "female",
         "height" => 167,
       ],
     ],
   }

>>> $p->sortBy('name')

=> Illuminate\Support\Collection {#4372

all: [

3 => [

"name" => "Ando",

"gender" => "male",

"height" => 182,

0 => [

"name" => "Egawa",

"gender" => "male",

"height" => 175,

1 => [

"name" => "Iwata",

"gender" => "female",

"height" => 163,

2 => [

"name" => "Okada",

"gender" => "male",

"height" => 168,

4 => [

"name" => "Ueda",

"gender" => "female",

"height" => 167,

}

以下の例ではheightを指定して並べ替えている。身長の数値が昇順となるように並んでいる。

>>> $p->sortBy('height')
=> Illuminate\Support\Collection {#4369
     all: [
       1 => [
         "name" => "Iwata",
         "gender" => "female",
         "height" => 163.,
       ],
       4 => [
         "name" => "Ueda",
         "gender" => "female",
         "height" => 167.,
       ],
       2 => [
         "name" => "Okada",
         "gender" => "male",
         "height" => 168.,
       ],
       0 => [
         "name" => "Egawa",
         "gender" => "male",
         "height" => 175.,
       ],
       3 => [
         "name" => "Ando",
         "gender" => "male",
         "height" => 182.,
       ],
     ],
   }

>>> $p->sortBy('height')

=> Illuminate\Support\Collection {#4369

all: [

1 => [

"name" => "Iwata",

"gender" => "female",

"height" => 163.,

4 => [

"name" => "Ueda",

"gender" => "female",

"height" => 167.,

2 => [

"name" => "Okada",

"gender" => "male",

"height" => 168.,

0 => [

"name" => "Egawa",

"gender" => "male",

"height" => 175.,

3 => [

"name" => "Ando",

"gender" => "male",

"height" => 182.,

}

sortByDesc()～引数キーの降順

sortByDesc()メソッドは引数にキーを指定して、そのキーの値の降順で要素を並べ替えたコレクションを返す。

以下の例では、身長の高い順に並べ替えている。

>>> $p->sortByDesc('height')
=> Illuminate\Support\Collection {#4382
     all: [
       3 => [
         "name" => "Ando",
         "gender" => "male",
         "height" => 182.,
       ],
       0 => [
         "name" => "Egawa",
         "gender" => "male",
         "height" => 175.,
       ],
       2 => [
         "name" => "Okada",
         "gender" => "male",
         "height" => 168.,
       ],
       4 => [
         "name" => "Ueda",
         "gender" => "female",
         "height" => 167.,
       ],
       1 => [
         "name" => "Iwata",
         "gender" => "female",
         "height" => 163.,
       ],
     ],
   }

>>> $p->sortByDesc('height')

=> Illuminate\Support\Collection {#4382

all: [

3 => [

"name" => "Ando",

"gender" => "male",

"height" => 182.,

0 => [

"name" => "Egawa",

"gender" => "male",

"height" => 175.,

2 => [

"name" => "Okada",

"gender" => "male",

"height" => 168.,

4 => [

"name" => "Ueda",

"gender" => "female",

"height" => 167.,

1 => [

"name" => "Iwata",

"gender" => "female",

"height" => 163.,

}

コレクションの変形・取出し

pluck()～キー列の取出し

pluck()メソッドは引数にキーを指定し、そのキーの値を単純配列のコレクションとして取り出す。pluckは英語で「摘む」という意味。

>>> $p->pluck('name')
=> Illuminate\Support\Collection {#4377
     all: [
       "Egawa",
       "Iwata",
       "Okada",
       "Ando",
       "Ueda",
     ],
   }

>>> $p->pluck('name')

=> Illuminate\Support\Collection {#4377

all: [

"Egawa",

"Iwata",

"Okada",

"Ando",

"Ueda",

}

以下はgenderキーの値を取り出している例。

>>> $p->pluck('gender')
=> Illuminate\Support\Collection {#4380
     all: [
       "male",
       "female",
       "male",
       "male",
       "female",
     ],
   }

>>> $p->pluck('gender')

=> Illuminate\Support\Collection {#4380

all: [

"male",

"female",

"male",

"female",

}

flatten()～コレクションの展開

flatten()メソッドは、多次元配列型のコレクションの要素を再帰的にたどって1次元の配列型に並べる。

>>> $c = collect([1, [2, 3], [[4, 5], 6]])
=> Illuminate\Support\Collection {#4333
     all: [
       1,
       [
         2,
         3,
       ],
       [
         [
           4,
           5,
         ],
         6,
       ],
     ],
   }
>>> $c->flatten()
=> Illuminate\Support\Collection {#4311
     all: [
       1,
       2,
       3,
       4,
       5,
       6,
     ],
   }

>>> $c = collect([1, [2, 3], [[4, 5], 6]])

=> Illuminate\Support\Collection {#4333

all: [

[

}

>>> $c->flatten()

=> Illuminate\Support\Collection {#4311

all: [

}

連想配列はキーが無視され、値が使われる。

>>> $p->flatten()
=> Illuminate\Support\Collection {#4373
     all: [
       "Egawa",
       "male",
       175,
       "Iwata",
       "female",
       163,
       "Okada",
       "male",
       168,
       "Ando",
       "male",
       182,
       "Ueda",
       "female",
       167,
     ],
   }

>>> $p->flatten()

=> Illuminate\Support\Collection {#4373

all: [

"Egawa",

"male",

175,

"Iwata",

"female",

163,

"Okada",

"male",

168,

"Ando",

"male",

182,

"Ueda",

"female",

167,

}

多次元配列の場合、再帰的に要素をたどって1次元化する。

SQL的処理

クエリービルダーメソッドと同等のwhere()、groupBy()がある。ただしこれらのメソッドは、クエリービルダーの様にはクエリーを生成はしない。

where()～条件による抽出

where()メソッドはクエリービルダーメソッドのwhere()と同じ。

>>> $p->where('gender', '=', 'female')
=> Illuminate\Support\Collection {#4356
     all: [
       1 => [
         "name" => "Iwata",
         "gender" => "female",
         "height" => 163,
       ],
       4 => [
         "name" => "Ueda",
         "gender" => "female",
         "height" => 167,
       ],
     ],
   }

>>> $p->where('gender', '=', 'female')

=> Illuminate\Support\Collection {#4356

all: [

1 => [

"name" => "Iwata",

"gender" => "female",

"height" => 163,

4 => [

"name" => "Ueda",

"gender" => "female",

"height" => 167,

}

groupBy()～キーによるグルーピング

groupBy()メソッドは、指定したキー列の値で要素をグルーピングする。

元の配列の要素として、指定したキー列の値をキーとした配列ができる
その配列の要素として、各キーに対応した値を持つ元の要素が格納される

以下の例ではgenderキーによってグルーピングしている。genderキーの値male、femaleをキーとする配列が作られ、キーがmaleの配列にはgenderがmaleの要素、キーがfemaleの配列にはgenderがfemaleの要素が格納されている。

>>> $g = $p->groupBy('gender')
=> Illuminate\Support\Collection {#4373
     all: [
       "male" => Illuminate\Support\Collection {#4370
         all: [
           [
             "name" => "Egawa",
             "gender" => "male",
             "height" => 175,
           ],
           [
             "name" => "Okada",
             "gender" => "male",
             "height" => 168,
           ],
           [
             "name" => "Ando",
             "gender" => "male",
             "height" => 182,
           ],
         ],
       },
       "female" => Illuminate\Support\Collection {#4372
         all: [
           [
             "name" => "Iwata",
             "gender" => "female",
             "height" => 163,
           ],
           [
             "name" => "Ueda",
             "gender" => "female",
             "height" => 167,
           ],
         ],
       },
     ],
   }

>>> $g = $p->groupBy('gender')

=> Illuminate\Support\Collection {#4373

all: [

"male" => Illuminate\Support\Collection {#4370

all: [

[

"name" => "Egawa",

"gender" => "male",

"height" => 175,

[

"name" => "Okada",

"gender" => "male",

"height" => 168,

[

"name" => "Ando",

"gender" => "male",

"height" => 182,

"female" => Illuminate\Support\Collection {#4372

all: [

[

"name" => "Iwata",

"gender" => "female",

"height" => 163,

[

"name" => "Ueda",

"gender" => "female",

"height" => 167,

}

Laravel – Collection

2021-12-19 / tau / コメントする

クエリービルダーのget()メソッドの結果、Eloquent\Collectionのインスタンスが得られるが、これはSupport\Collectionを継承している。

ここではSupport\Collectionの操作方法を整理する。

基本操作

コレクションの作成や要素の参照・追加などの基本操作
スタックのような操作、空かどうかの判定、配列への変換操作も含む

集計・加工

要素数のカウントや最小値・最大値の取得、合計・平均の計算
各種並べ替え
キーの抜き出し、一次元展開
where、groupBy
グループ化された結果の集計については一工夫必要

コールバックによる操作

filterによる要素の条件抽出
mapによる各要素に対する処理定義
reduceによるコレクション→スカラー値への縮退処理
useによる元のコレクションの参照
- 先頭・末尾などの例外処理

Laravel – ローカルスコープクエリー

2021-12-03 / tau / コメントする

概要

ローカルクエリースコープを使うと、特定モデルに必要なクエリービルダーに名前を付けて、1つのメソッドとして呼び出すことができる。

準備

クエリービルダーで使ったOrderとCustomerのモデルとテーブルを使う。

mysql> SELECT * FROM customers;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | customer1 |
|  2 | customer2 |
|  3 | customer3 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM orders;
+----+-------------+---------------------+---------------+
| id | customer_id | ordered_at          | item          |
+----+-------------+---------------------+---------------+
|  1 |           1 | 2020-06-05 00:00:00 | screw         |
|  2 |           2 | 2020-06-05 00:00:00 | rubber sheet  |
|  3 |           2 | 2020-06-06 00:00:00 | plastic plate |
|  4 |           1 | 2020-06-07 00:00:00 | rubber sheet  |
|  5 |           3 | 2020-06-08 00:00:00 | wire          |
+----+-------------+---------------------+---------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM customers;

+----+-----------+

| id | name |

+----+-----------+

| 1 | customer1 |

| 2 | customer2 |

| 3 | customer3 |

+----+-----------+

3 rows in set (0.00 sec)

mysql> SELECT * FROM orders;

+----+-------------+---------------------+---------------+

+----+-------------+---------------------+---------------+

| 1 | 1 | 2020-06-05 00:00:00 | screw |

| 2 | 2 | 2020-06-05 00:00:00 | rubber sheet |

| 3 | 2 | 2020-06-06 00:00:00 | plastic plate |

| 4 | 1 | 2020-06-07 00:00:00 | rubber sheet |

| 5 | 3 | 2020-06-08 00:00:00 | wire |

+----+-------------+---------------------+---------------+

5 rows in set (0.00 sec)

それぞれのモデルでリレーションを定義している。

# Customerモデル

class Customer extends Model
{
    public $timestamps = false;

    public function email() {
        return $this->hasOne('App\Email');
    }

    public function orders() {
        return $this->hasMany('App\Order');
    }
}

# Orderモデル

class Order extends Model
{
    public $timestamps = false;

    public function customer() {
        return $this->belongsTo('App\Customer');
    }
}

# Customerモデル

class Customer extends Model

{

public $timestamps = false;

public function email() {

return $this->hasOne('App\Email');

}

public function orders() {

return $this->hasMany('App\Order');

}

# Orderモデル

class Order extends Model

{

public $timestamps = false;

public function customer() {

return $this->belongsTo('App\Customer');

}

手順

ローカルスコープのクエリービルダーは、次の手順で定義する。

対象のモデルでメソッド定義
- 'scope'に続いてキャメルケースでメソッド名を定義
- 第1引数にクエリー($queryなど)
- ビルダーに引数を渡したいときは第2引数以降に加える
- メソッド内の戻り値は、第1引数のクエリーに対してビルダーを組んだ結果を渡す
定義したメソッドをビルダーの様に呼び出す
- 呼び出す際は'scope'を除いたメソッド名で呼び出す

例1：引数なし

たとえばordersの最新3データのみ取り出す定形操作があるとする。このとき、Orderモデルで以下の様にメソッドを定義する。

メソッド名は頭に'scope'を付けてscopeLatest3()として、クエリーを受け取る引数に$queryを設定。

class Order extends Model
{
    public $timestamps = false;

    public function customer() {
        return $this->belongsTo('App\Customer');
    }

    public function scopeLatest3($query) {
        return $query->latest('ordered_at')->limit(3);
    }
}

class Order extends Model

{

public $timestamps = false;

public function customer() {

return $this->belongsTo('App\Customer');

}

public function scopeLatest3($query) {

return $query->latest('ordered_at')->limit(3);

}

tinkerでこれを利用してみる。呼び出す際は’scope’を付けずにlatest3()とし、$queryに対応する引数は渡す必要はない。

>>> Order::latest3()->get()
=> Illuminate\Database\Eloquent\Collection {#4315
     all: [
       App\Order {#4311
         id: 5,
         customer_id: 3,
         ordered_at: "2020-06-08 00:00:00",
         item: "wire",
       },
       App\Order {#4321
         id: 4,
         customer_id: 1,
         ordered_at: "2020-06-07 00:00:00",
         item: "rubber sheet",
       },
       App\Order {#4328
         id: 3,
         customer_id: 2,
         ordered_at: "2020-06-06 00:00:00",
         item: "plastic plate",
       },
     ],
   }

>>> Order::latest3()->get()

=> Illuminate\Database\Eloquent\Collection {#4315

all: [

App\Order {#4311

id: 5,

customer_id: 3,

ordered_at: "2020-06-08 00:00:00",

item: "wire",

App\Order {#4321

id: 4,

customer_id: 1,

ordered_at: "2020-06-07 00:00:00",

item: "rubber sheet",

App\Order {#4328

id: 3,

customer_id: 2,

ordered_at: "2020-06-06 00:00:00",

item: "plastic plate",

}

生成されるSQLは以下のとおり。

>>> Order::latest3()->toSql()
=> "select * from `orders` order by `ordered_at` desc limit 3"

1 2	>>> Order::latest3()->toSql() => "select * from `orders` order by `ordered_at` desc limit 3"

例2：引数を渡す場合

モデルでのメソッド定義の第2引数以下に、渡したい引数を列挙する。以下の例ではcustomer_idを渡し、これに合致するordersのデータを抽出している。

実行時に渡したい引数は、第2引数custome_idで定義している。

    public function scopeOfCustomer($query, $customer_id) {
        return $query->where('customer_id', '=', $customer_id);
    }

public function scopeOfCustomer($query, $customer_id) {

return $query->where('customer_id', '=', $customer_id);

}

tinkerで引数にcustoer_id=1を渡して実行してみる。

>>> Order::ofCustomer(1)->get()
=> Illuminate\Database\Eloquent\Collection {#4323
     all: [
       App\Order {#4324
         id: 1,
         customer_id: 1,
         ordered_at: "2020-06-05 00:00:00",
         item: "screw",
       },
       App\Order {#4326
         id: 4,
         customer_id: 1,
         ordered_at: "2020-06-07 00:00:00",
         item: "rubber sheet",
       },
     ],
   }

>>> Order::ofCustomer(1)->get()

=> Illuminate\Database\Eloquent\Collection {#4323

all: [

App\Order {#4324

id: 1,

customer_id: 1,

ordered_at: "2020-06-05 00:00:00",

item: "screw",

App\Order {#4326

id: 4,

customer_id: 1,

ordered_at: "2020-06-07 00:00:00",

item: "rubber sheet",

}

生成されるSQLとバインドされる値は以下のとおり。

>>> Order::ofCustomer(1)->toSql()
=> "select * from `orders` where `customer_id` = ?"
>>> Order::ofCustomer(1)->getBindings()
=> [
     1,
   ]

>>> Order::ofCustomer(1)->toSql()

=> "select * from `orders` where `customer_id` = ?"

>>> Order::ofCustomer(1)->getBindings()

=> [

]

例3：子モデルが絡む場合

ビルダーに引数を渡して、子モデルの条件で抽出したい場合。whereHas()を使うが、引数に渡すクロージャ―の変数はローカルスコープのため、useで引数の変数を受け取って渡す。

    public function scopeOfCustomerName($query, $name) {
        return $query->whereHas('customer',
            function($query) use($name) {
                $query->where('name', '=', $name);
            }
        );
    }

public function scopeOfCustomerName($query, $name) {

return $query->whereHas('customer',

function($query) use($name) {

$query->where('name', '=', $name);

}

);

}

tinkerでcustomer_name='customer2'を渡して確認。

>>> Order::ofCustomerName('customer2')->get()
=> Illuminate\Database\Eloquent\Collection {#4317
     all: [
       App\Order {#4329
         id: 2,
         customer_id: 2,
         ordered_at: "2020-06-05 00:00:00",
         item: "rubber sheet",
       },
       App\Order {#4313
         id: 3,
         customer_id: 2,
         ordered_at: "2020-06-06 00:00:00",
         item: "plastic plate",
       },
     ],
   }

>>> Order::ofCustomerName('customer2')->get()

=> Illuminate\Database\Eloquent\Collection {#4317

all: [

App\Order {#4329

id: 2,

customer_id: 2,

ordered_at: "2020-06-05 00:00:00",

item: "rubber sheet",

App\Order {#4313

id: 3,

customer_id: 2,

ordered_at: "2020-06-06 00:00:00",

item: "plastic plate",

}

生成されるSQLとバインドされる値は以下のとおり。

>>> Order::ofCustomerName('customer2')->toSql()
=> "select * from `orders` where exists (select * from `customers` where `orders`.`customer_id` = `customers`.`id` and `name` = ?)"
>>> Order::ofCustomerName('customer2')->getBindings()
=> [
     "customer2",
   ]

>>> Order::ofCustomerName('customer2')->toSql()

=> "select * from `orders` where exists (select * from `customers` where `orders`.`customer_id` = `customers`.`id` and `name` = ?)"

>>> Order::ofCustomerName('customer2')->getBindings()

=> [

"customer2",

]

Laravel – ANDとORの優先順位

2021-12-02 / tau / コメントする

概要

where()とorWhere()組み合わせることで、WHERE句のANDやORを表現できる。ここではこれらのメソッドの書き方と生成されるSQL、条件の組み合わせの優先順位を整理する。

以下のようなメソッドの書き方が可能になる。

条件1 OR 条件2 AND 条件3でANDが優先される書き方
(条件1 OR 条件2) AND 条件3で括弧内のORが優先される書き方

where()やorWhere()については、クエリービルダーのメソッドを参照。

準備

クエリービルダーで使った以下の3つのテーブルで各メソッドを確認する。

mysql> SELECT * FROM customers;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | customer1 |
|  2 | customer2 |
|  3 | customer3 |
+----+-----------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM emails;
+----+-------------+--------------------+
| id | customer_id | email              |
+----+-------------+--------------------+
|  1 |           1 | customer1@mail.com |
|  2 |           2 | customer2@mail.com |
+----+-------------+--------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM orders;
+----+-------------+---------------------+---------------+
| id | customer_id | ordered_at          | item          |
+----+-------------+---------------------+---------------+
|  1 |           1 | 2020-06-05 00:00:00 | screw         |
|  2 |           2 | 2020-06-05 00:00:00 | rubber sheet  |
|  3 |           2 | 2020-06-06 00:00:00 | plastic plate |
|  4 |           1 | 2020-06-07 00:00:00 | rubber sheet  |
|  5 |           3 | 2020-06-08 00:00:00 | wire          |
+----+-------------+---------------------+---------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM customers;

+----+-----------+

| id | name |

+----+-----------+

| 1 | customer1 |

| 2 | customer2 |

| 3 | customer3 |

+----+-----------+

3 rows in set (0.00 sec)

mysql> SELECT * FROM emails;

+----+-------------+--------------------+

| id | customer_id | email |

+----+-------------+--------------------+

| 1 | 1 | customer1@mail.com |

| 2 | 2 | customer2@mail.com |

+----+-------------+--------------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM orders;

+----+-------------+---------------------+---------------+

+----+-------------+---------------------+---------------+

| 1 | 1 | 2020-06-05 00:00:00 | screw |

| 2 | 2 | 2020-06-05 00:00:00 | rubber sheet |

| 3 | 2 | 2020-06-06 00:00:00 | plastic plate |

| 4 | 1 | 2020-06-07 00:00:00 | rubber sheet |

| 5 | 3 | 2020-06-08 00:00:00 | wire |

+----+-------------+---------------------+---------------+

5 rows in set (0.00 sec)

これをordersに対してcustomersとemailsを左結合で関連付けて並べると以下のとおり。

mysql> SELECT
    ->   o.id, o.ordered_at, o.item, c.id, c.name, e.address
    -> FROM orders AS o
    -> LEFT JOIN customers AS c ON o.customer_id = c.id
    -> LEFT JOIN emails AS e ON c.id = e.customer_id;
+----+---------------------+---------------+------+-----------+--------------------+
| id | ordered_at          | item          | id   | name      | address            |
+----+---------------------+---------------+------+-----------+--------------------+
|  1 | 2020-06-05 00:00:00 | screw         |    1 | customer1 | customer1@mail.com |
|  2 | 2020-06-05 00:00:00 | rubber sheet  |    2 | customer2 | customer2@mail.com |
|  3 | 2020-06-06 00:00:00 | plastic plate |    2 | customer2 | customer2@mail.com |
|  4 | 2020-06-07 00:00:00 | rubber sheet  |    1 | customer1 | customer1@mail.com |
|  5 | 2020-06-08 00:00:00 | wire          |    3 | customer3 | NULL               |
+----+---------------------+---------------+------+-----------+--------------------+
5 rows in set (0.00 sec)

mysql> SELECT

-> o.id, o.ordered_at, o.item, c.id, c.name, e.address

-> FROM orders AS o

-> LEFT JOIN customers AS c ON o.customer_id = c.id

-> LEFT JOIN emails AS e ON c.id = e.customer_id;

+----+---------------------+---------------+------+-----------+--------------------+

+----+---------------------+---------------+------+-----------+--------------------+

| 1 | 2020-06-05 00:00:00 | screw | 1 | customer1 | customer1@mail.com |

| 2 | 2020-06-05 00:00:00 | rubber sheet | 2 | customer2 | customer2@mail.com |

| 3 | 2020-06-06 00:00:00 | plastic plate | 2 | customer2 | customer2@mail.com |

| 4 | 2020-06-07 00:00:00 | rubber sheet | 1 | customer1 | customer1@mail.com |

| 5 | 2020-06-08 00:00:00 | wire | 3 | customer3 | NULL |

+----+---------------------+---------------+------+-----------+--------------------+

5 rows in set (0.00 sec)

チェーンの展開とANDの優先

where()やorWhere()をチェーンで連ねた場合、それぞれの条件が単純にANDあるいはORで連ねられる。その結果ANDが処理された後ORが処理される。

...->where(条件)は'... AND 条件'
...->orWhere(条件)は'... OR 条件'

以下の例では、where()->orWhere()->where()とメソッドチェーンを組んでいる。

        $orders = Order::where('item', '=', 'wire')
            ->orWhere('item', '=', 'rubber sheet')
            ->where('customer_id', '=', 1)->get();

$orders = Order::where('item', '=', 'wire')

->orWhere('item', '=', 'rubber sheet')

->where('customer_id', '=', 1)->get();

上記のクエリービルダーで生成されるSQLは以下のとおり。

>>> Order::where('item', '=', 'wire')->
... orWhere('item', '=', 'rubber sheet')->
... where('customer_id', '=', 1)->toSql()
=> "select * from `orders` where `item` = ? or `item` = ? and `customer_id` = ?"

>>> Order::where('item', '=', 'wire')->

... orWhere('item', '=', 'rubber sheet')->

... where('customer_id', '=', 1)->toSql()

=> "select * from `orders` where `item` = ? or `item` = ? and `customer_id` = ?"

この結果AND演算が優先され、「item='wire'または、item='rubber sheet'かつid=1」という条件で2つのデータが抽出される。

4: 2020-06-07 00:00:00
- customer name: customer1
- email address: customer1@mail.com
- ordered item : rubber sheet
5: 2020-06-08 00:00:00
- customer name: customer3
- email address:
- ordered item : wire

クロージャによる（）の表現

ANDに対してORの条件を優先させたい場合、SQLでは括弧で括る。クエリービルダーでは、括弧で括りたい処理をクロージャ―でまとめる。

where(function($query) { 括弧でまとめたい処理群; })

以下の例では、上の例と順番は同じだが、最初のwhere()とorWhere()をクロージャ―でまとめている。

        $orders = Order::where(function($q) {
            $q->where('item', '=', 'wire')
            ->orWhere('item', '=', 'rubber sheet');
        })->Where('customer_id', '=', 1)->get();

$orders = Order::where(function($q) {

$q->where('item', '=', 'wire')

->orWhere('item', '=', 'rubber sheet');

})->Where('customer_id', '=', 1)->get();

この結果、まず最初の2項のOR演算が処理され、その結果と残りの条件のAND演算で抽出処理される。生成されるSQLは以下のとおりでOR演算が括弧で括られている。

>>> Order::where(function($q) {
...   $q->where('item', '=', 'wire')
...     ->orWhere('item', '=', 'rubber sheet');
... })->Where('customer_id', '=', 1)->toSql();
=> "select * from `orders` where (`item` = ? or `item` = ?) and `customer_id` =

>>> Order::where(function($q) {

... $q->where('item', '=', 'wire')

... ->orWhere('item', '=', 'rubber sheet');

... })->Where('customer_id', '=', 1)->toSql();

=> "select * from `orders` where (`item` = ? or `item` = ?) and `customer_id` =

実行結果は以下のとおりで、「item='wire'またはitem='rubber sheet'」で3つのデータが抽出され、その結果とid=1のANDで結果が一つに絞り込まれる。

4: 2020-06-07 00:00:00
- customer name: customer1
- email address: customer1@mail.com
- ordered item : rubber sheet