複雑なmysqlをわかりやすくまとめる

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。