クエリービルダーまとめ

2019/05/22

Laravelでデータベースを取り扱うなら、クエリービルダーを使ってレコードを抽出することになります。

クエリービルダーを使えば、PHP言語から離れることなくSQLを発行できます。(※SQL操作的な考えは必要ですが)

しかし、実際クエリービルダーの使い方をちゃんと理解していないと、生のSQLを書いたほうがよかったりするんですよね。

今回はそうならないためにクエリービルダーの使い方についてまとめました。

 
INDEX

 

1.結果データ取得(複数)

条件指定に沿った結果データの全てを取得


$users = User::where('status',1)->get();

返り値は StdClass オブジェクトのインスタンスを結果として含む Collection オブジェクトとして返ってきます。

個々の結果データにアクセスするには、以下のようにループで回します。


// 王道パターン
foreach($users as $user){
  echo $user->name;
}

// 結果データそのものから直接取得もできる
echo $users[0]->name;

 

2.結果データ取得(単数)

単一レコードのみ抽出。


// 主キー
$user = User::find(1);
// 最初に検知したレコード
$user = User::where('status',1)->first();

// 結果が返ってこなかった時は例外処理
$user = User::findOrFail(1);
$user = User::where('status',1)->firstOrFail();

結果データはStdClassオブジェクトで取得されます。

取り出し方は以下になります。


# 1件のみの取得なのでループの必要はありません
$name = $user->name;

 

3.条件

$users = User::where('name', '=' ,'hoge')->get();
#省略形
$users = User::where('name', 'hoge')->get();

ポイント

get()メソッドが返すのは配列ではありません。dd() メソッドで表示される通り Collection インスタンスです。

Laravel5.1コレクション

 

4.条件(queryメソッドを使った書き方)

例えば、集計関数とページネーションの両方を取りたい場合はこっちがおススメ。


$query = User::query();
$query->where('pref', '大阪府');

$result_count = $query->count(); // 集計関数
$users = $query->paginate(12);   // ページネーション

 

5.複数条件を扱いたい(A and B)

$users = User::where('name', 'hoge')
                   ->where('password', 'fuga')
                   ->get();

 
query()メソッドを使った書き方


$query = User::query();
$query->where('name', 'hoge');
$query->where('password', 'fuga');

$users = $query->get();

 

6.複数条件を扱いたい(A or B)

$users = User::where('name', 'hoge')
                     ->orWhere('is_admin', true)
                     ->get();

 

7.指定した配列の中にカラムの値が含まれているか?

$users = User::whereIn('id', [1, 2, 3])->get();

 

8.ページネーションをつける

$users = User::paginate(15);

ポイント
Bladeでの表示

クエリービルダーで抽出したレコードは以下のように ページャーとレコードを表示させます。


// foreach で配列で回す
@foreach ($users as $user)
  {{ $user->name }}
@endforeach

{!! $users->render() !!} // ページャー

 

該当レコードが無い場合

@if($users->isEmpty())
  <p>レコードがありません</p>
@endif

 

9.複数条件を扱いたい + 集計関数 + ページネーション

$query = User::query();
$query->where('pref', '京都府');
$query->where('skill', 'PHP');

$result_count = $query->count(); // 集計関数
$users = $query->paginate(12);   // ページネーション

ポイント

集計関数はページネーションより前に置いてください。
ページネーションを先に置くと、2ページ目でカウントが0になります。

 

10.内部結合・外部結合

例:profilesテーブルとusersテーブルを内部結合・外部結合


# 内部結合(inner join)
$users = User::Join('profiles', 'users.id', '=', 'profiles.user_id')->get();

# 外部結合(left outer join)
$users = User::leftJoin('profiles', 'users.id', '=', 'profiles.user_id')->get();

ポイント

テーブルを結合すると同盟のカラムは上書きされます。


$users = User::Join('profiles', 'users.id', '=', 'profiles.user_id')
                    ->select('users.*') //回避策 
                    ->get();

 

11.A and (B or C)

(B or C)の箇所をクロージャーで書きます。


$users = $User::where('sex' , 'male')
                 ->where(function($query){
                 $query->where('age', '<', 10)
                      ->orWhere('score', '>', 70);
                 })
           ->get();

 

12.A and (B or C) + 変数を使いたい

変数を使うときは use を使います。


$users = User::where('sex' , 'male')
                 ->where(function($query) use ($age, $score){
                 $query->where('age', '<', $age)
                      ->orWhere('score', '>', $score);
                 })
           ->get();

 

13.POST/GET送信で送られたデータを条件にする

whenメソッド の第一引数がfalseの場合、クロージャーを実行しません。


$name = $request->input('name');
$old = $request->input('old');

$users = DB::table('users')
                 // 名前が入力されていたら以下を実行
                 ->when($name, function ($query) use ($name) {
                    return $query->where('name', $name); 
                 })

                  // 年齢が入力されていたら以下を実行
                  ->when($old, function ($query) use ($old) { 
                    return $query->where('old', $old); 
                 })
                 ->get();

 

14.POST/GET送信で送られたデータを条件にする(配列の場合)

例:都道府県のチェックボックス


public function juniorlist(Request $req){

$query = User::query();

// 都道府県のチェックボックスにチェックがあったら。。
if(is_array($req->input('prefs'))){

  // OR検索+入力されたデータを変数として使う
  $query->where(function($q) use($req){
   
     // チェックボックスの値をまわす
     foreach($req->input('prefs') as $param){
        $q->orWhere('pref', 'like' , $param);
      }

  });
}

$users = $query->get();

 

15.(A or B) and (C or D)

$users = User::where(function($query){
                   $query->orWhere('pref','大阪')
                         ->orWhere('pref','京都');
               })->where(function($query){
                   $query->orWhere('skill','php')
                         ->orWhere('skill','ruby');
               })
               ->get();

 

16.順序/グループ

# 順序
$users = User::orderBy('id','desc')->get();

# グループ
$users = User::groupBy('pref')->get();

 

17.あいまい検索

# like
$users = User::where('name', 'like', '%sample')->get();

# NOT like
$users = User::where('name', 'not like', '%sample')->get();

# 変数を使ってあいまい検索
$param = 'sample';
$users = User::where('name', 'like', '%{$param}%')->get();

ポイント

1.NOT LIKE を複数指定する場合は、論理演算子は AND になります。


SELECT * FROM users WHERE name not like '%sample%' and name not like '%サンプル%'

 
2.NOT IN ではワイルドカードは使えません


# id が「1」「2」以外
$uses = User::whereNotIn('id',[1,2])->get();

 
また、何か発見したら、ここに追記しようと思います。

以上です。

本庄マサノリ

仕事でLaravelを使っています。気づいたことや新しい発見など情報を発信していきます。メールはこちら

 

-実践知識