Laravel – クエリービルダー

1 準備
2 基本操作
3 コントローラーでの記述

準備

以下の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)

3つのテーブルを結合させるSQLの例。

SELECT
  orders.id,
  orders.ordered_at,
  orders.customer_id,
  customers.name,
  emails.email
FROM orders
LEFT JOIN customers
  ON orders.customer_id = customers.id
LEFT JOIN emails
  ON customers.id = emails.customer_id;

SELECT

orders.id,

orders.ordered_at,

orders.customer_id,

customers.name,

emails.email

FROM orders

LEFT JOIN customers

ON orders.customer_id = customers.id

LEFT JOIN emails

ON customers.id = emails.customer_id;

SQLの実行結果。

+----+---------------------+-------------+-----------+--------------------+
| id | ordered_at          | customer_id | name      | email              |
+----+---------------------+-------------+-----------+--------------------+
|  1 | 2020-06-05 00:00:00 |           1 | customer1 | customer1@mail.com |
|  2 | 2020-06-05 00:00:00 |           2 | customer2 | customer2@mail.com |
|  3 | 2020-06-06 00:00:00 |           2 | customer2 | customer2@mail.com |
|  4 | 2020-06-07 00:00:00 |           1 | customer1 | customer1@mail.com |
|  5 | 2020-06-08 00:00:00 |           3 | customer3 | NULL               |
+----+---------------------+-------------+-----------+--------------------+
5 rows in set (0.00 sec)

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

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

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

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

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

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

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

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

5 rows in set (0.00 sec)

基本操作

tinkerで確認する。

$ php artisan tinker
Psy Shell v0.10.8 (PHP 7.3.29 — cli) by Justin Hileman
>>>

$ php artisan tinker

Psy Shell v0.10.8 (PHP 7.3.29 — cli) by Justin Hileman

>>>

ビルダーの書き方

DBファサードのtableメソッド

DBファサードのtable()メソッドを使う場合。

>>> DB::table('customers')->select('*')->toSql()
=> "select * from `customers`"

1 2	>>> DB::table('customers')->select('')->toSql() => "select from `customers`"

モデルのビルダーメソッド

モデルのビルダーメソッドを直接実行する場合。

>>> Customer::select('*')->toSql()
[!] Aliasing 'Customer' to 'App\Customer' for this Tinker session.
=> "select * from `customers`"

>>> Customer::select('*')->toSql()

[!] Aliasing 'Customer' to 'App\Customer' for this Tinker session.

=> "select * from `customers`"

ビルダーのインスタンスを保存して使う

DBファサードのtable()メソッドの場合、tinkerだと以下のように詳細な内容が表示される。toSql()メソッドで構築されるSQLが確認できる。

>>> $builder = DB::table('customers')->select('*')
=> Illuminate\Database\Query\Builder {#4332
     +connection: Illuminate\Database\MySqlConnection {#4316},
     +grammar: Illuminate\Database\Query\Grammars\MySqlGrammar {#4317},
     +processor: Illuminate\Database\Query\Processors\MySqlProcessor {#4319},
     +bindings: [
       "select" => [],
       "from" => [],
       "join" => [],
     ........
     +useWritePdo: false,
   }
>>> $builder->toSql()
=> "select * from `customers`"

>>> $builder = DB::table('customers')->select('*')

=> Illuminate\Database\Query\Builder {#4332

+connection: Illuminate\Database\MySqlConnection {#4316},

+grammar: Illuminate\Database\Query\Grammars\MySqlGrammar {#4317},

+processor: Illuminate\Database\Query\Processors\MySqlProcessor {#4319},

+bindings: [

"select" => [],

"from" => [],

"join" => [],

........

+useWritePdo: false,

}

>>> $builder->toSql()

=> "select * from `customers`"

モデルからビルダーメソッドを直接呼び出す場合、tinkerでのインスタンスは表示はシンプルになる。

>>> $builder = Customer::select('*')
=> Illuminate\Database\Eloquent\Builder {#4324}
>>> $builder->toSql()
=> "select * from `customers`"

>>> $builder = Customer::select('*')

=> Illuminate\Database\Eloquent\Builder {#4324}

>>> $builder->toSql()

=> "select * from `customers`"

SQLや実行結果などの取得・確認方法

ビルダーのインスタンス取得

クエリービルダーを実行すると、ビルダーのインスタンスが得られる。

>>> $builder = Customer::whereIn('id', [1, 2])
=> Illuminate\Database\Eloquent\Builder {#4350}

1 2	>>> $builder = Customer::whereIn('id', [1, 2]) => Illuminate\Database\Eloquent\Builder {#4350}

get()～実行結果の取得

クエリービルダーの実行結果はget()メソッドで得る。

>>> $builder->get()
=> Illuminate\Database\Eloquent\Collection {#4329
     all: [
       App\Customer {#4333
         id: 1,
         name: "customer1",
       },
       App\Customer {#4348
         id: 2,
         name: "customer2",
       },
     ],
   }

>>> $builder->get()

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

all: [

App\Customer {#4333

id: 1,

App\Customer {#4348

id: 2,

}

toSql()～SQLの確認

toSql()メソッドでビルダーで生成されるSQLを確認できる。

>>> $builder->toSql()
=> "select * from `customers` where `id` in (?, ?)"

1 2	>>> $builder->toSql() => "select * from `customers` where `id` in (?, ?)"

getBindings()～バインドされる値の確認

バインドされる値はgetBindings()メソッドで確認できる。

>>> $builder->getBindings()
=> [
     1,
     2,
   ]

>>> $builder->getBindings()

=> [

]

クエリービルダーメソッドのチェーン

複数のコマンドを含むSQLの場合は、コマンドに対応するクエリービルダーメソッドをチェーンで繋げる。

>>> Customer::select('name')->whereIn('id', [1, 2])->orderBy('id', 'desc')->get()
=> Illuminate\Database\Eloquent\Collection {#4335
     all: [
       App\Customer {#4347
         name: "customer2",
       },
       App\Customer {#4299
         name: "customer1",
       },
     ],
   }

>>> Customer::select('name')->whereIn('id', [1, 2])->orderBy('id', 'desc')->get()

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

all: [

App\Customer {#4347

App\Customer {#4299

}

メソッドチェーンの順番は問わない。以下のようにメソッドの順番を変えても、生成されるSQLは同じ。

>>> Customer::select('name')->whereIn('id', [1, 2])->orderBy('id', 'desc')->toSql()
=> "select `name` from `customers` where `id` in (?, ?) order by `id` desc"
>>> Customer::orderBy('id', 'desc')->whereIn('id', [1, 2])->select('name')->toSql()
=> "select `name` from `customers` where `id` in (?, ?) order by `id` desc"

>>> Customer::select('name')->whereIn('id', [1, 2])->orderBy('id', 'desc')->toSql()

=> "select `name` from `customers` where `id` in (?, ?) order by `id` desc"

>>> Customer::orderBy('id', 'desc')->whereIn('id', [1, 2])->select('name')->toSql()

=> "select `name` from `customers` where `id` in (?, ?) order by `id` desc"

コントローラーでの記述

基本の書き方

以下のルーティングで基本の書き方を確認。

Route::get('/query_simple', 'QueryController@query_simple');

1	Route::get('/query_simple', 'QueryController@query_simple');

コントローラーでの書き方。DBファサードを使う例。

customersテーブルのnameカラムの値を取得している
結果は配列customersで各要素は2つの要素id、nameを持つ連想配列となる
$customersをビューに渡している

?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use Illuminate\Support\Facades\DB;
use App\Customer;

class QueryController extends Controller
{
    public function query_simple() {
        $customers = DB::table('customers')->select('id', 'name')->get();
        
        return view('queries.query_simple', [
            'title' => 'Query Test',
            'customers' => $customers,
        ]);
    }
}

?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use Illuminate\Support\Facades\DB;

use App\Customer;

class QueryController extends Controller

{

public function query_simple() {

$customers = DB::table('customers')->select('id', 'name')->get();

return view('queries.query_simple', [

'title' => 'Query Test',

'customers' => $customers,

]);

}

ビューでは受け取った$customersの各要素を取り出し、idとnameを表示。

<!DOCTYPE html>
<html lang="ja" dir="ltr">
  <head>
    <meta charset="utf-8">
    <title>{{ $title }}</title>
  </head>
  <body>
    <h1>{{ $title }}</h1>
    @foreach ($customers as $customer)
      <p>{{ $customer->id }}: {{ $customer->name }}</p>
    @endforeach
  </body>
</html>

<!DOCTYPE html>

<head>

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

</head>

<body>

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

@foreach ($customers as $customer)

<p>{{ $customer->id }}: {{ $customer->name }}</p>

@endforeach

</body>

</html>

なおdd()で表示させた$customersの内容は以下のとおり。

Illuminate\Support\Collection {#1274 ▼
  #items: array:3 [▼
    0 => {#1275 ▼
      +"id": 1
      +"name": "customer1"
    }
    1 => {#1278 ▼
      +"id": 2
      +"name": "customer2"
    }
    2 => {#1279 ▼
      +"id": 3
      +"name": "customer3"
    }
  ]
}

Illuminate\Support\Collection {#1274 ▼

#items: array:3 [▼

0 => {#1275 ▼

+"id": 1

+"name": "customer1"

}

1 => {#1278 ▼

+"id": 2

+"name": "customer2"

}

2 => {#1279 ▼

+"id": 3

+"name": "customer3"

}

]

}

メソッドチェーン

SQLの行が増えてきた場合、メソッドチェーンでビルダーメソッドを繋げる。

まず以下のようなルーティングを定義。

Route::get('/query_joined', 'QueryController@query_joined');

1	Route::get('/query_joined', 'QueryController@query_joined');

コントローラーのアクション内でメソッドチェーンを記述。以下の例では、冒頭のLEFT JOINを使ったSQLを意図している。

なお本筋ではないが、select()メソッドの要素内でASによるエイリアスを定義できる。

class QueryController extends Controller
{
    ........
    public function query_joined() {
        $orders = DB::table('orders')
            ->select(
                'orders.ordered_at',
                'customers.name AS customer_name',
                'emails.email',
                'orders.item')
            ->leftjoin('customers', 'orders.customer_id', '=', 'customers.id')
            ->leftJoin('emails', 'customers.id', '=', 'emails.customer_id')
            ->get();

        return view('queries.query_joined', [
            'title' => 'Query Test',
            'orders' => $orders,
        ]);
    }
    ........
}

class QueryController extends Controller

{

........

public function query_joined() {

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

->select(

'orders.ordered_at',

'customers.name AS customer_name',

'emails.email',

'orders.item')

->leftjoin('customers', 'orders.customer_id', '=', 'customers.id')

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

->get();

return view('queries.query_joined', [

'title' => 'Query Test',

'orders' => $orders,

]);

}

........

}

メソッドチェーンでSQLをビルドした場合、結果は指定したカラムを持つ複数レコードが2次元配列で返される。

以下はビューで受け取った$ordersを表示させる例。foreachで1レコードに相当する連想配列を取り出し、ネストしたforeachで各レコードの属性・内容の対を取り出して表示させている。

    @foreach ($orders as $order)
      <ol>
      @foreach ($order as $key => $value)
        <li>{{ $key }}: {{ $value }}</li>
      @endforeach
      </ol>
    @endforeach

@foreach ($orders as $order)

<ol>

@foreach ($order as $key => $value)

<li>{{ $key }}: {{ $value }}</li>

@endforeach

</ol>

@endforeach

ブラウザーには以下の様に表示される。

ordered_at: 2020-06-05 00:00:00
customer_name: customer1
address: customer1@mail.com
item: screw

ordered_at: 2020-06-05 00:00:00
customer_name: customer2
address: customer2@mail.com
item: rubber sheet

ordered_at: 2020-06-06 00:00:00
customer_name: customer2
address: customer2@mail.com
item: plastic plate

ordered_at: 2020-06-07 00:00:00
customer_name: customer1
address: customer1@mail.com
item: rubber sheet

ordered_at: 2020-06-08 00:00:00
customer_name: customer3
address:
item: wire

モデルのリレーション設定に基づく場合

クエリービルダーのメソッドチェーンへの対比として、同じデータをモデル間のリレーション定義に基づいて操作する例を示す。

Customer、Email、Orderの各モデルにリレーションに関する記述を追加する。

# Customerモデル

class Customer extends Model
{
    public $timestamps = false;

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

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

# Emailモデル

class Email extends Model
{
    public $timestamps = false;

    public function customer() {
        return $this->belongsTo('App\Customer');
    }
}
# 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');

}

# Emailモデル

class Email extends Model

{

public $timestamps = false;

public function customer() {

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

}

# Orderモデル

class Order extends Model

{

public $timestamps = false;

public function customer() {

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

}

コントローラーではOrderモデルの全データを取り込み、ビューに渡す。

class QueryController extends Controller
{
    ........
    public function query_relation() {
        $orders = Order::all();

        return view('queries.query_relation', [
            'title' => 'Query Test',
            'orders' => $orders,
        ]);
    }
}

class QueryController extends Controller

{

........

public function query_relation() {

$orders = Order::all();

return view('queries.query_relation', [

'title' => 'Query Test',

'orders' => $orders,

]);

}

ビューでは受け取った$ordersから要素を順に取り出して、関連付けられたデータを表示させる。関連付けられたデータがない場合のエラーを防ぐため、optional()ヘルパーを使っている。

  <body>
    <h1>{{ $title }}</h1>
    <ol>
      @foreach ($orders as $order)
        <li>{{ $order->ordered_at }}: {{ $order->customer->name }}</li>
        <ul>
          <li>e-mail: {{ optional($order->customer->email)->address }}</li>
          <li>item: {{ $order->item }}</li>
        </ul>
      @endforeach
    </ol>
  </body>

<body>

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

<ol>

@foreach ($orders as $order)

<li>{{ $order->ordered_at }}: {{ $order->customer->name }}</li>

<ul>

<li>e-mail: {{ optional($order->customer->email)->address }}</li>

<li>item: {{ $order->item }}</li>

</ul>

@endforeach

</ol>

</body>

ブラウザーには以下の様に出力される。emailが存在しない場合は無表示となっている。

2020-06-05 00:00:00: customer1
- e-mail: customer1@mail.com
- item: screw
2020-06-05 00:00:00: customer2
- e-mail: customer2@mail.com
- item: rubber sheet
2020-06-06 00:00:00: customer2
- e-mail: customer2@mail.com
- item: plastic plate
2020-06-07 00:00:00: customer1
- e-mail: customer1@mail.com
- item: rubber sheet
2020-06-08 00:00:00: customer3
- e-mail:
- item: wire

なおdd()で表示させた$ordersの内容は以下のとおり。1つ目の要素のみ展開している。

Illuminate\Database\Eloquent\Collection {#1288 ▼
  #items: array:5 [▼
    0 => App\Order {#1289 ▼
      +timestamps: false
      #connection: "mysql"
      #table: "orders"
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #attributes: array:4 [▼
        "id" => 1
        "customer_id" => 1
        "ordered_at" => "2020-06-05 00:00:00"
        "item" => "screw"
      ]
      #original: array:4 [▶]
      #changes: []
      #casts: []
      #dates: []
      #dateFormat: null
      #appends: []
      #dispatchesEvents: []
      #observables: []
      #relations: []
      #touches: []
      #hidden: []
      #visible: []
      #fillable: []
      #guarded: array:1 [▶]
    }
    1 => App\Order {#1290 ▶}
    2 => App\Order {#1291 ▶}
    3 => App\Order {#1292 ▶}
    4 => App\Order {#1293 ▶}
  ]
}

Illuminate\Database\Eloquent\Collection {#1288 ▼

#items: array:5 [▼

0 => App\Order {#1289 ▼

+timestamps: false

#connection: "mysql"

#table: "orders"

#primaryKey: "id"

#keyType: "int"

+incrementing: true

#with: []

#withCount: []

#perPage: 15

+exists: true

+wasRecentlyCreated: false

#attributes: array:4 [▼

"id" => 1

"customer_id" => 1

"ordered_at" => "2020-06-05 00:00:00"

"item" => "screw"

]

#original: array:4 [▶]

#changes: []

#casts: []

#dates: []

#dateFormat: null

#appends: []

#dispatchesEvents: []

#observables: []

#relations: []

#touches: []

#hidden: []

#visible: []

#fillable: []

#guarded: array:1 [▶]

}

1 => App\Order {#1290 ▶}

2 => App\Order {#1291 ▶}

3 => App\Order {#1292 ▶}

4 => App\Order {#1293 ▶}

]

}

クエリービルダーとリレーション

クエリービルダーでSQLを発行すると、フレームワークに負担をかけずにDBMS側でデータを絞り込めるが、その方法によっては意図した動作にならなかったり、リレーションを使えない場合がある。

結論としてはモデルクラスでクエリービルダーを記述するのがよさそうだが、その詳細についてはこちらにまとめた。

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

クエリービルダーでSQLと同様にエイリアスを定義できる。クエリービルダーでのエイリアスを参照。

TauStation

Laravel – クエリービルダー

準備

基本操作

ビルダーの書き方

DBファサードのtableメソッド

モデルのビルダーメソッド

ビルダーのインスタンスを保存して使う

SQLや実行結果などの取得・確認方法

ビルダーのインスタンス取得

get()～実行結果の取得

toSql()～SQLの確認

getBindings()～バインドされる値の確認

クエリービルダーメソッドのチェーン

コントローラーでの記述

基本の書き方

メソッドチェーン

モデルのリレーション設定に基づく場合

クエリービルダーとリレーション

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

コメントを残すコメントをキャンセル

準備

基本操作

ビルダーの書き方

DBファサードのtableメソッド

モデルのビルダーメソッド

ビルダーのインスタンスを保存して使う

SQLや実行結果などの取得・確認方法

ビルダーのインスタンス取得

get()～実行結果の取得

toSql()～SQLの確認

getBindings()～バインドされる値の確認

クエリービルダーメソッドのチェーン

コントローラーでの記述

基本の書き方

メソッドチェーン

モデルのリレーション設定に基づく場合

クエリービルダーとリレーション

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

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル