MySQLコマンドについての備忘録(INNER JOIN句・AS句)

2018/12/18

Laravelでサイトの構築の開発を進めていると、場合によって複雑なSQL文を書くケースが出てきます。

特にテーブルの結合処理とかそうですね。

Eloquent では便利なリレーション機能がついていますが、複雑なSQL文だったら、生のSQL文を直接書いたほうがわかりやすかったりします。

生の SQL を Laravel で直接書く場合は、クエリビルダー(DBファサード)のSELECTメソッドを使います。


# 例
public function index()
{
  $users = DB::select('select * from users where active = ?', [1]);
  return view('user.index', ['users' => $users]);
}

SQL文を直接書くならSQLの文法をきちんと理解しておく必要があります。

今回は実践でよく使うSQLの文法についてエントリーします。

 
この記事の内容

 

内部結合(INNER JOIN句)

2つのテーブルを結合してデータを取得する方法です。

指定したそれぞれのテーブルのカラムの値が一致するデータだけを取得します。
(※内部結合の場合、ベースとなるテーブルから、条件にマッチするレコードがないものは削除されます)

書式


SELECT テーブル名.カラム名, ... FROM テーブル名1 
INNER JOIN テーブル名2
 ON テーブル名1.カラム名1 = テーブル名2.カラム名2;

-- 簡略化
SELECT (取得するカラム)FROM テーブル名1 
INNER JOIN テーブル名2
 ON (結合条件);

ポイント

取得するデータは2つのテーブルのカラムが混合したものとなります。どのテーブルのカラムなのかを表すために「テーブル名.カラム名」の形で指定します。

 

書式例


SELECT * FROM order_tbl 
INNER JOIN detail_tbl
 ON order_tbl.id = detail_tbl.order_id;

 
実行結果

テーブル結合 + 条件の場合

内部結合のあとにWHERE句をつけてあげます。


SELECT * From order_tbl 
INNER JOIN detail_tbl
 ON order_tbl.id = detail_tbl.order_id 
WHERE detail_tbl.product_price > 3000;

 

3つのテーブル結合

書式


SELECT テーブル名.カラム名, ... (取得したいカラム名)
FROM テーブル名1 
INNER JOIN テーブル名2
 ON テーブル名1.カラム名1 = テーブル名2.カラム名2 
INNER JOIN テーブル名3
 ON テーブル名1.カラム名1 = テーブル名3.カラム名3 

 

書式例


SELECT * FROM detail_tbl 
INNER JOIN order_tbl
 ON detail_tbl.order_id = order_tbl.id 
INNER JOIN product_tbl
 ON detail_tbl.product_id = product_tbl.id;

 
実行結果

LaravelでJOINメソッドの使用方法

join メソッドを使うとINNER JOIN句と同じになります。


$users = DB::table('detail_tbl')
  ->join('order_tbl','detail_tbl.order_id', '=', 'order_tbl.id') // detail_tblテーブルのorder_idとorder_tblテーブルのidを紐づける
  ->join('product','detail_tbl.product_id', '=', 'product.id') // detail_tblテーブルのproduct_idとproduct_tblテーブルのidを紐づける
  ->select('users.*','contacts.phone', 'orders.price')
  ->get();

 

カラムに別名をつける(AS句)

SELECT文を使ってテーブルからデータを取得したとき、AS句を使用することで元のテーブルのカラム名とは異なるカラム名をつけてデータを取得することができます。

書式


SELECT カラム名 AS 別名, ... FROM テーブル名;

 

書式例


SELECT id, user_id AS customer, amount AS volume FROM order_tbl;

 
実行結果

 

INNER JOIN句 + AS句

INNER JOIN句で内部結合すると結合先のカラム名とよくかぶってしまうので、合わせ技としてAS句をよく使います。

書式例


SELECT
 o.id AS order_id, -- 注文テーブルの id に order_id と列名を付ける 
 o.user_id AS user_id, --注文テーブルの user_id に user_id と列名をつける
 o.amount AS amount, --注文テーブルの amount に amount と列名をつける
 o.order_time AS order_time, --注文テーブルの order_time に order_time と列名をつける
 d.id AS detail_id, --詳細テーブルの id に detail_id と列名をつける
 d.product_qty AS qty --詳細テーブルの product_qty に qty と列名をつける
FROM
 order_tbl AS o --注文テーブル order_tble に o とテーブル名をつける
INNER JOIN
 detail_tbl AS d --注文テーブル detail_tble に d とテーブル名をつける
ON o.id = d.order_id
INNER JOIN
 product_tbl AS p  --注文テーブルproduct_tb lに p とテーブル名をつける
ON d.product_id = p.id;

 
実行結果

 

LaravelのDBファサードで書いてみる

複雑なSQLならDBファサードのSELECTメソッドで直接SQLを実行したほうがいいかもしれません。

書式例

例えば、Ajax でURLを叩いて getAllOrder メソッドを実行し、JSON形式で返す処理を書く場合


public function getAllOrder(Request $request) {

$list = DB::select(
"SELECT to.id AS order_id, p.name AS product_name, p.price AS product_price from order_tbl as o ".
"INNER JOIN detail_tbl AS d on o.id = d.order_id ".
"WHERE d.order_id = ? and o.order_time > ? ORDER BY o.order_time ASC", [$request['order_id'], $request['order_time']]
);

return response()->json(['orders' => $list]);
}

 
以上です。

本庄マサノリ

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

>> Twitter をフォローする

 

-周辺知識