データベースとの連携（3）クエリビルダ

SQLでは、集計関数や並び順、グループ化など様々な条件を組み合わせてデータを加工・抽出しています。LaravelのクエリビルダでもSQLのクエリと同様にPHPの構文としてメソッドチェーンで実装できます。
ここでは、クエリビルダでのクエリパターンを紹介します
テーブルは、前項目で作成済みの「members」テーブルを対象としています。

データの取得

複数件取得（条件がないのでメンバーテーブルから全件数取得）
get()

DB::table('members')->get();

1	DB::table('members')->get();

最初の1件を取得
first()

DB::table('members')->first();

1	DB::table('members')->first();

取得カラムの指定

select( ‘カラム1’ , ‘カラム2’ , ‘カラム2’ )

DB::table('tmembers')->select( 'name' , 'age' , 'email' )->get();

1	DB::table('tmembers')->select( 'name' , 'age' , 'email' )->get();

◆レコードの抽出条件

where( ‘カラム名’ , ‘比較演算子’ , ‘比較する値’ )

DB::table('members')->where( 'age' , '>' , 20 )->get();

1	DB::table('members')->where( 'age' , '>' , 20 )->get();

年齢が20歳より上のレコードを全件取得します

第二引数がイコールの場合に限っては、比較演算子を省略し、引数は2つで取得できます。

DB::table('members')->where( 'age' , 20 )->get();

1	DB::table('members')->where( 'age' , 20 )->get();

年齢が20歳のレコードを全件取得します

複数条件の場合は、whereを複数つなげます

DB::table('members')->where( 'age' , '>=' , 20 )->where( 'name' , 'like' , '%木%' )->get();

1	DB::table('members')->where( 'age' , '>=' , 20 )->where( 'name' , 'like' , '%木%' )->get();

年齢が20歳以上で名前に「木」を含むレコードを取得

where() を複数つなげると「and」（複数条件を満たす）となります。
「or」(いづれかの条件が満たせれば）にする場合は、orWhere() を使います

DB::table('members')->where( 'age' , 20 )->orWhere( 'age' , 30 )->get();

1	DB::table('members')->where( 'age' , 20 )->orWhere( 'age' , 30 )->get();

年齢が20歳、もしくは30歳のレコードを全件取得します

集計

レコードの件数を取得
count()

DB::table('members')->count();

1	DB::table('members')->count();

指定したカラムの最大値
max()

DB::table('members')->max( 'age' );

1	DB::table('members')->max( 'age' );

指定したカラムの最小値
max()

DB::table('members')->min( 'age' );

1	DB::table('members')->min( 'age' );

指定したカラムの平均値
avg()

DB::table('members')->avg( 'age' );

1	DB::table('members')->avg( 'age' );

指定したカラムの合計値
sum()

DB::table('members')->sum( 'age' );

1	DB::table('members')->sum( 'age' );

並び順（ソート）

抽出レコードの並び順：昇順（asc）、降順（desc）
orderBy( ‘カラム名’ , ‘昇順か降順を指定’ )

DB::table('members')->orderBy( 'age' , 'desc' )->get();

1	DB::table('members')->orderBy( 'age' , 'desc' )->get();

部分的にSQL文を直接記述する場合

出力の整形、SQL関数の使用や抽出条件など場合によっては、SQLクエリを直接したい場合もあります。
DB::rawメソッドを使ってSQL文を記述します。

DB::table('members')->select(DB::raw('count(*) as member_count'))->get();

1	DB::table('members')->select(DB::raw('count(*) as member_count'))->get();

項目名に別名をつけています。
これだけ見てもあまりピントきません。次のgroupByと組み合わせるとよりわかりやすいです。

グループ単位で表示する

groupBy( ‘ グループぶするカラム’ );

DB::table('members')
->select( 'age' , DB::raw('count(*) as member_count'))
->groupBy( 'age' )
->get();

DB::table('members')

->select( 'age' , DB::raw('count(*) as member_count'))

->groupBy( 'age' )

->get();

年齢でグループ化しています。グループ化した各年齢の人数を取得しています（取得カラムは、年齢と人数）。

テーブルの結合

join( ‘結合テーブル’ , ‘結合元テーブル名.結合するカラム名’ , ‘=’ , ‘結合先テーブル名.リンクするカラム名’ )

DB::table('tweets')->join( 'members' , 'tweets.member_id' , '=' , 'members.id' )->get();

1	DB::table('tweets')->join( 'members' , 'tweets.member_id' , '=' , 'members.id' )->get();

注意）両テーブルに同じカラム名の項目がある場合は、別名にして取得する必要があります。

leftJoin( ‘結合テーブル’ , ‘結合元テーブル名.結合するカラム名’ , ‘=’ , ‘結合先テーブル名.リンクするカラム名’ )

DB::table('members')
  ->leftJoin( 'tweets' , 'members.id' , '=' , 'tweets.member_id' )
  ->groupBy( 'members.id' )
  ->select->('members.id' ,'members.name' ,
       DB::raw( 'count(tweets.member_id is not null or null) as tweet_count' )
      )
  ->get();

DB::table('members')

->leftJoin( 'tweets' , 'members.id' , '=' , 'tweets.member_id' )

->groupBy( 'members.id' )

->select->('members.id' ,'members.name' ,

DB::raw( 'count(tweets.member_id is not null or null) as tweet_count' )

)

->get();

「Laravel」の一覧を表示する