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。