mysqlで複雑なクエリを使う時のメモ。
サンプルテーブル
・お店 omises
| id | name |
|---|---|
| 100 | 八百屋 |
| 101 | 魚屋 |
・商品 syouhins
| id | omise_id | name | ninki |
|---|---|---|---|
| 500 | 100 | キャベツ | 2 |
| 501 | 100 | レタス | 1 |
| 502 | 101 | 鯛 | 2 |
| 503 | 101 | マグロ | 2 |
| 504 | 100 | りんご | 4 |
| 505 | 102 | 豚肉 | 3 |
普通に読み出す
まずはお店のテーブルを読み出します。
SELECT * FROM omises
Array
(
[0] => Array
(
[id] => 100
[name] => 八百屋
)
)
[1] => Array
(
[id] => 101
[name] => 魚屋
)
)
)
お店の名前と商品名を結合して読み出す
ここで大事なのは、omises にも syouhins にも name フィールドがある。
よって、 as 句を使って別名にしておかないとnameフィールドがうまく取れないので注意。
今回は omises.name を tenmei に。syouhins.name を goods に変更。
SELECT omises.name as tenmei , syouhins.name as goods FROM omises INNER JOIN syouhins ON omises.id = syouhins.omise_id';
Array
(
[0] => Array
(
[tenmei] => 八百屋
[goods] => キャベツ
)
[1] => Array
(
[tenmei] => 八百屋
[goods] => レタス
)
[2] => Array
(
[tenmei] => 魚屋
[goods] => 鯛
)
[3] => Array
(
[tenmei] => 魚屋
[goods] => マグロ
)
[4] => Array
(
[tenmei] => 八百屋
[goods] => りんご
)
)
お店の名前が八百屋、商品がりんごのものを読み出す。
今回も omises.name を tenmei に。syouhins.name を goods に変更。
SELECT omises.name as tenmei , syouhins.name as goods FROM
omises INNER JOIN syouhins ON omises.id = syouhins.omise_id WHERE omises.name = "八百屋" AND syouhins.name = "りんご"
Array
(
[0] => Array
(
[tenmei] => 八百屋
[goods] => りんご
)
)
お店の名前が八百屋、がいくつの商品を持っているか数える。
SELECT count(syouhin.id) FROM
omises INNER JOIN syouhins ON omises.id = syouhins.omise_id WHERE omises.name = "八百屋"
Array
(
[0] => Array
(
[count(syouhins.id)] => 3
)
)
商品はあるが、関連するお店が無いものみIDを取得
豚肉はどこのお店でも売られていませんよー
#leftjoinのが速いらしい。
SELECT omise_id FROM syouhins LEFT JOIN omises ON syouhins.omise_id = omises.id WHERE omises.id is null;
#スロークエリ（上のクエリと同じ結果）
#SELECT omise_id FROM syouhins where not exists(select * from omises where syouhins.omise_id=id );
Array
(
[0] => Array
(
[omise_id] => 102
)
)
商品もある、関連するお店もあるIDのみを取得
group by にすることで重複を取り除いている
SELECT omise_id FROM syouhins LEFT JOIN omises ON syouhins.omise_id = omises.id WHERE omises.id group by omises.id
Array
(
[0] => Array
(
[omise_id] => 100
)
[1] => Array
(
[omise_id] => 101
)
)
人気が2の商品をどこのお店がいくつ持っているか。多く持っている順に並び替え。
select
id , count("ninki") as ninki_count
from
syouhins where ninki = 2 group by omise_id order by ninki_count desc;
ただし、これだと最新100の商品から取得。
みたいな感じで LIMIT句を使えない。
LIMIT句を使う場合は サブクエリを使う必要がある。
しかし、実験した結果以下のようになった。
LIMITを200件に絞り込んだ場合。
速い PHP7 + MYSQL5 の複合処理
遅い MYSQL5 サブクエリ
よってmysql とphp で複合的に処理する。
以下はcakephp3の書き方
<?php
$tmp = $this->Syouhins->find()
->where([
'ninki' => 2,
])
->limit(200)
->all();
$res = [];
foreach ($tmp as $v) {
if(!empty($res[$v->omise_id])){
$res[$v->omise_id]++;
} else {
$res[$v->omise_id] = 1;
}
}
arsort($res);
?>
cakephp3 で使う場合
直接SQLを使うので、
use Cake\Datasource\ConnectionManager;
をやっとく。
$sql = 'SELECT count(syouhins.id) FROM
omises INNER JOIN syouhins ON omises.id = syouhins.omise_id WHERE omises.name = "八百屋"';
$connection = ConnectionManager::get('default');
$tmp = $connection->execute($sql)->fetchAll('assoc');
pr($tmp);
クエリによって
->fetchAll('assoc');
がついたりつかなかったりするので注意。
cakephp3 で PDOを使って、まとめてデータを取得
例えば、omise_id 別にデータを取得したい場合。
データを取得してからゴリゴリphpで加工せずとも良い。
まずは
use PDO;
をやっとく。
$sql = "SELECT
omise_id,
name
FROM
syouhins";
$tmp = $connection->execute($sql)->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);
pr($tmp)
上記をやると・・・
Array
(
[100] => Array
(
[0] => Array
(
[name] => キャベツ
)
[1] => Array
(
[name] => レタス
)
[2] => Array
(
[name] => りんご
)
)
[101] => Array
(
[0] => Array
(
[name] => 鯛
)
[1] => Array
(
[name] => マグロ
)
)
[102] => Array
(
[0] => Array
(
[name] => 豚肉
)
)
)
といった感じでお店IDをキーにしてまとめて表示することができる。
これを応用することで日付別なども可能になる。
奥深いですね。MYSQL。