クエリービルダで使用できる全メソッド まとめ

2022/06/14

Eloquentモデルはクエリビルダですから、クエリビルダで使用できる全メソッドを確認しておくべきでしょう。

Eloquentクエリでどんなメソッドも使用できます。

【 クエリビルダとは 】
SQLに詳しくなくても、データベースにアクセスするためのクエリを生成してくれる機能です。
クエリを記述するには、主にDBファサードのtable()メソッドを使用します。
（※Eloquentモデルでも使用可）


//get()で、全件取得
//usersテーブルの全レコードを取得
$users = DB::table('users')->get();

//get()はcollectionを返す
//collectionはforeachで回すことができる
//オブジェクトのプロパティとして、各カラムの値にアクセスすることができる
foreach($users as $user){
  echo $user->name;
}
PHP

 
DBファサードについて まとめ

 
INDEX

  1. 結果データ取得（複数）
  2. 結果データ取得（単数）
  3. 条件
  4. 条件（queryメソッドを使った書き方）
  5. 複数条件を扱いたい(A and B)
  6. 複数条件を扱いたい(A or B)
  7. 指定した配列の中にカラムの値が含まれているか？
  8. ページネーションをつける
  9. 複数条件を扱いたい + 集計関数 + ページネーション
  10. 内部結合・外部結合
  11. A and (B or C)
  12. A and (B or C) ＋ 変数を使いたい
  13. POST／GET送信で送られたデータを条件にする
  14. POST／GET送信で送られたデータを条件にする(配列の場合)
  15. (A or B) and (C or D)
  16. 順序／グループ
  17. あいまい検索（部分一致検索）
  18. オフセット
  19. 特定のカラムだけを取得
  20. 3テーブルを結合
  21. レコードのカウントを取得（count関数）
  22. SELECT句のみ（where句なし）
  23. 特定の値以外を抽出
  24. SQL文を確認（デバッグ作業）
  25. 検索 keyword（パラメータ）の持ち回り
  26. リレーション先の情報を元にクエリの生成（whereHas）
  27. ランダムに取得（random）
  28. 「以外」を指定する
  29. クエリビルダを使わない場合（直のSQL文）

 

１．結果データ取得(複数)

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


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

 

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

Post::where('author_id','1')->get() など->get()で返ってくるオブジェクトのタイプはIlluminate\Database\Eloquent\Collectionとなっています。
Collection は様々な機能を持ったクラスで、厳密にはarrayではありません。
[]添字をサポートしていたり、foreach に入れても動きます。

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


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

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

 

２．結果データ取得(単数)

単一レコードのみ抽出。


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

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

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

stdClassについて

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


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

 

３．条件

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

 

４．条件（queryメソッドを使った書き方）

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


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

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

 

５．複数条件を扱いたい(A and B)

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

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


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

$users = $query->get();
PHP

 

６．複数条件を扱いたい(A or B)

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

 

７．指定した配列の中にカラムの値が含まれているか？

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

 

８．ページネーションをつける

$users = User::paginate(15);
PHP
ポイント
Bladeでの表示

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


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

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

 

該当レコードが無い場合

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

 

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

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

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

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

 

１０．内部結合・外部結合

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


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

#-----------------------
# 内部結合＋検索
#-----------------------
$query = \App\Models\User::query();
$query->join('profiles',function($query) use ($request){ // 検索条件にリクエストパラメータを使うときは use を使う
  $query->on('users.id','=','profiles.user_id');
});

// 名前の検索があった場合だけ実行
if(!empty($name)){ 
  $query->where('user.name','=',$name);
}
// カラム名の重複対策
$query->select('users.*');

#--------------------------
# 外部結合（left outer join）
#--------------------------
$users = User::leftJoin('profiles', 'users.id', '=', 'profiles.user_id')->get();
PHP
【 ポイント 】
usersテーブルのemailカラムとprofilesテーブルのemailカラムが重複する場合


// usersテーブルで上書きするには以下
// ただし、これだとusersテーブルのカラムしかとれない
$users = User::Join('profiles', 'users.id', '=', 'profiles.user_id')
                    ->select('users.*') // 回避策 
                    ->get();

// 結合先のテーブルのカラムも一部使用する場合
$users = User::Join('profiles', 'users.id', '=', 'profiles.user_id')
                    ->select('users.*','profiles.hobby') // profiles テーブルの hobby も使用する  
                    ->get();
PHP

 

１１．A and (B or C)

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


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

 

１２．A and (B or C) ＋ 変数を使いたい

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


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

#-------------------------
# パターンB
#-------------------------
$query = User::query();
      $query->where(function($q) use ($keyword){
          $q->where('name','like',"%$keyword%")
              ->orWhere('gender','like',"%$keyword%")
              ->orWhere('category','like',"%$keyword%")
              ->orWhere('grade','like',"%$keyword%");
      });
$query->Where('disp_flag','=',2); // フラグ
$query->orderBy('pub_date','desc'); // 最新順
$results_count = $query->count(); // カウント
$sql = $query->toSql(); //デバッグ用

// 検索keyword（パラメータ）の持ち回り
$params = $req->except('page');
$results = $query->paginate(6)->appends($params);
PHP

 

１３．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();
PHP

 

１４．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();
PHP

 

１５．(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();
PHP

 

１６．順序／グループ

#------------------------
# 順序
#------------------------
// ID 降順
$users = User::orderBy('id','desc')->get();
// 新しい作成順
$users = User::orderBy('created_at','desc')->get();
// 古い作成順
$users = User::orderBy('created_at','asc')->get();

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

 

１７．あいまい検索（部分一致検索）

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

# like 検索 + 変数
$users = User::where('name', 'not like', "%$sample%")->get();
PHP
【 変数を使った like 検索 】
第３引数を ''（シングルクォーテーション）で囲むと、変数 $sample が展開されない。
これを ""（ダブルクォーテーション）で囲ってあげると展開され、検索できる。

 

１８．オフセット

結果レコードの取得開始位置（何件目から取得するか）を指定するには offset()メソッド を使います。

offset()メソッドとセットで利用されるのが limit()メソッド です。

limit()メソッドはデータベースからの取得件数を指定するメソッドです。


// 2件目から3件目を取得する
$users = User::offset(2)->limit(3)->get();

// 以下でも同じ意味になります
$users = User::skip(2)->take(3)->get();

// 実例 CSVダウンロード
$blocksize = 100;
for($i=0; true; $i++){
  $query = User::query();
  $query -> skip($i * $blocksize); // 取得開始位置
  $query -> take($blocksize); // 取得件数を指定
  $list = $query->get()
：
PHP
ポイント

offset() & limit() ＝ skip() & take()

 

１９．特定のカラムだけを取得

SQLでいうところの SELECT `tag` FROM contents WHERE id=$id AND category=$category;

value()メソッドで指定した1つのカラムのみを取得します。


// tagカラムの値だけ取り出す
$tag = Content::where('id',$id)->where('category',$category)->value('tag');
PHP

 
get()メソッドの引数にカラム名を入れるやり方も可能。ただ、こちらの場合、引数が配列になることに注意。


// get()メソッドにカラム名を入れる
$tag = Content::where('id',$id)->where('category',$category)->get(['tag']);

// 普通の配列にすると便利
$tag = $tag->toArray();

// 指定した値だけ取り出す
$tag = $tag[0]["tag"]
PHP

 

２０．3テーブルを結合

３つのテーブルを内部結合する場合は Join() メソッドをそのままチェーンメソッドとして使います。

事例）「users」テーブル + 「user_admins」テーブル +「profiles」テーブル


// SQL文(usesテーブル)をデータベースに発行
$query = User::query();

// user_adminsテーブルを結合
$query->Join('user_admins','users.id','=','user_admins.user_id');

// profilesテーブルを結合
$query->Join('profiles','users.id','=','profiles.user_id');

// 結果を取得
$lists = $query->get();
PHP

 

２１．レコードのカウントを取得

DBのレコードのカウントを取得したいケースです。


// データ取得後
$users = User::get();
$count = $users->count();

// getまで書かなくてもOK
$count = User::count();
PHP

 

２２．SELECT句のみ（where句なし）

一覧画面で代表的なカラムのみ取得したい場合、SELECT句はどこに書けばいいか？


// データ取得
$users = User::select('*')->get();

// 普通はページネーションをよく使う
$users = User::select('*')->paginate(10);
PHP

 

２３．特定の値以外を抽出

// id=1 以外のデータを取得
$users = User::where('id','!=',1)->get();

// ただ、id に null が入っていると != で抽出できない
// その場合は以下のようにすれば OK
$users = User::where('id','!=',1)->orWhereNull('id')->get();
PHP

 

２４．SQL文を確認（デバッグ作業）

toSql()メソッドで、発行しているSQL文を確認できます。


// データ取得
$query = Content::query();
$query->Where('disp_flag','=',2);
$query->orderBy('pub_date','desc');

// デバッグ作業
$sql = $query->toSql();
dd($sql);
PHP

 

２５．検索 keyword（パラメータ）の持ち回り

ページリンクにて、検索Keywordを持ちまわる必要があるが、それは、$model->appends() を利用する。


public function usersSearchResult(Request $req){

// データ取得
$query = User::query();
$query->Where('disp_flag','=',2);
$query->orderBy('pub_date','desc');

$params = $req->except('page'); // 「except」で指定した場合、記述したメソッド以外のリクエストが可能
$results = $query->paginate(6)->appends($params);
PHP

 

２６．リレーション先の情報を元にクエリの生成（whereHas）

リレーション先の情報を利用してクエリを発行したい場合は「whereHas」が利用できます。

例）Userモデルを作成し、リレーション先（投稿テーブル）のカラムで条件をかけたい

 

User.php（モデル）

：
// 投稿テーブル（Postモデル：postsテーブル）にリレーション
public function posts(){
    return $this->hasMany('App\Post', 'user_id', 'id');
}
PHP

 

IndexController.php（コントローラ）

：
class IndexController extends Controller
{
    public function index(){

        $user = User::whereHas('posts', function($query){
            $query->where('created_at', '>', '2021-05-17');
        })->get();

        return view('home');
    }
}
PHP
・Userモデルで作成したリレーションは posts でしたので、第一引数に posts を指定。
・第二引数では postsテーブル を見た際のクエリです。（※無名関数を使用する）
・無名関数とはクロージャとも呼ばれ、関数名を指定せずに関数を作成できるようにするもの。
・この場合の created_at は posts.created_at を意味する。

 

２７．ランダムに取得（random）

Collectionクラスでは、randomメソッドを使用することでランダムな並び順にすることが出来ます。


// ユーザテーブルの id と名前を取得
$students = User::where('id',$id)->get(['id','name']);

// ランダムに３つ取得
if($students->count() >= 3){
   $students = $students->random(3);
}else{
   $students = null;
}
PHP
上記の例でいくと、条件式の結果が 3 以下の場合、 random(3)を使うとエラーになります。

 

２８．以外を指定する

自分以外のユーザーの投稿のみを表示させたい場合


// ログインユーザーのidを取得
$id=Auth::id();
$other=Post::where('user_id','!=','id')->get();
PHP

 

２９．クエリビルダを使わない場合（直のSQL文）

最初の呼び出しで DB と動作をコールします。

DB の SELECT を使うよと宣言してから PHP 従来の SELECT 文を記載します。

JOIN なども従来通りで使います。


// select
$items = \DB::select('select * from users');

// select + プレースホルダ（SQLに引数を渡すことができる機能）
$results = \DB::select('select * from users where id = ?',[1]);

// insert
\DB::table('products')->insert(['name' => $name, 'price' => $price]);
\DB::insert('insert into users (id, name) values (?, ?)',[1, 'hoge']);

// インサートしたLastInsertIDを取得する
$last_id = \DB::table('products')->insertGetId(['name' => $name, 'price' => $price]);
PHP

 

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

以上です。

 

PHPフレームワーク Laravel入門 第2版

僕がはじめてLaravelを学習するために参考にしたサイトは、掌田津耶乃（しょうだつやの）さんの libro というサイトです。当時(2016年)、Laravel学習サイトの中でもこのサイトは群を抜いてわかりやすく説明されていたので、とても勉強になったのを覚えています。この本は掌田津耶乃さんが書いた本なので、わかりやすく解説されているだろうと kindle で購入しました。2020年8月の時点でいうと日本国内にて唯一の Laravel の良書と言っても良いかと思います。口コミでもLaravel本のロングセラー定番解説書として認知されています。当サイトではチュートリアル形式でLaravelを解説しているので、初心者の方はこの本とセットで学習されるといいと思います。しかし、かなりわかりやすく解説されているとはいえ、PHP中級者以上のスキルは必要です。PHP自体の知識が乏しい方は 独習PHP 第3版 をあわせて購入することをお勧めします。

Amazonで詳細を見る

オススメ

 

本庄マサノリ
本庄マサノリ

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

>> Twitter をフォローする

 

-実践知識

Laravelが学べるプログラミングスクール2選
 
S