SQL、集約関数(SUM)を利用する時に注意すること(続き)

https://hacknote.jp/archives/55069/ に記載した,集約関数実装の続きです.

LeftJoinでテーブルをもともとのテーブルAの横にBとCのテーブルを追加しようとした時、 A has many B and Cという状況で、Aに対してCの値のレコードが2つ、Bのレコードが1つJoinする状況を考えます.

このとき,JoinしたBのカラムに対して集約関数(SUM)を実装してしまうと,値が2倍になってしまうので,予めSubqueryを用意しておきます.

cakePHPのクエリビルダを用いて実装すると以下のようになるはずです.

$subQuery = TableRegistry::getTableLocator()
    ->get('B')
    ->find("all")
    ->select(
        [
            "a_id" => "B.a_id",
            "total" =>  $query->func()->sum("B.value"),
        ]
    )
    ->group("a_id");

このBを予め集約しておくSubQueryをAにLeftJoinします.

$query = TableRegistry::getTableLocator()
    ->get('A')
    ->find("all")
    ->select(
        [
            "a_id" => "B.a_id",
            "total" =>  $query->func()->sum("B.value"),
        ]
    )
    ->join([
        'b' => [
            'table' => "({$subQuery})", // 先程のSubQueryをJoin
            'type' => 'LEFT',
            'conditions' => 'sub.a_id = A.id',
        ]
    ])
    ->leftJoin([
        "c" => "C",
        "c.a_id" => "A.id"
    ]);