CakePHP3 クエリビルダによる入れ子状JOINテーブルのCOUNTの方法について

Question

分からない事

以下のようなSQLをCakePHP3で取得したいと思っていますが、
公式API及びCookBookを見ても方法が分かりません。
ご存知の方がおられたら教えてください。

想定されるDBは3つテーブルがあり、
A (hasMany)-> C (hasMany)-> Dという順番で結合しています。
Aテーブルはマスターテーブル
CテーブルはAとDを繋ぐためのテーブル
Dテーブルはワークテーブル
といった形です。

マスターテーブルAのIDで、ワークテーブルDのレコード数を数えたい　といったものです。
クエリビルダでなくとも、他の方法での解決方法があればそれでもかまいません。

どうぞよろしくお願いいたします。

コード

select A.id, A.name, count(B.id) from A
    left join 
        (select C.id, D.a_id from D
            left join 
                C on D.id = C.d_id
        ) as B
    on A.id = B.b_id
group by A.id;

• テーブル
  |A.id|A.name|
  |C.id|C.d_id|
  |D.id|D.a_id|

Answer 1

前提を確認させてください。
A (hasMany)-> C (hasMany)-> Dという順番で結合しています。
CテーブルはAとDを繋ぐためのテーブル
Dテーブルはワークテーブル　ですね！

CREATE TABLE A_muster (
id int(11) NOT NULL,
name varchar(48) DEFAULT NULL,
PRIMARY KEY (id)
)

CREATE TABLE C_tbl (
a_id int(11) NOT NULL,
d_id int(11) DEFAULT NULL,
PRIMARY KEY (a_id)
)

CREATE TABLE D_trn (
trn_id int(11) NOT NULL,
id int(11) DEFAULT NULL,
PRIMARY KEY (trn_id)
)
サンプルのデータ
A_muster
id name
1 NM1
2 NM2
3 NM3
4 NM4
5 NM5

C_tbl
a_id d_id
1 11
2 12
3 13
4 14
5 15

D_trn
trn_id id
101 11
102 11
103 12
104 13
105 14
106 14

これからAに対するDの数は
SELECT
A.id,
A.name,
count(D.id)
FROM
A_muster AS A
inner JOIN C_tbl AS C ON A.id = C.a_id
left JOIN D_trn AS D ON C.d_id = D.id
group BY A.id
で
Aid　　A.名称　　D.件数
1 NM1 2
2 NM2 1
3 NM3 1
4 NM4 2
5 NM5 0

もしも　０件を　数えないなら
SELECT
A.id,
A.name,
count(D.id)
FROM
A_muster AS A
inner JOIN C_tbl AS C ON A.id = C.a_id
inner JOIN D_trn AS D ON C.d_id = D.id
group BY A.id
ですが　いかがでしょうか

cakePHP3では

use Cake\Datasource\ConnectionManager;

<?php // DB接続を取得 $connection = ConnectionManager::get('default'); //　SQL $sql = 'SELECT A.id,A.`name`,count(D.id) AS cnt'; $sql.= 'FROM A_muster AS A '; $sql.= 'inner JOIN C_tbl AS C ON A.id = C.a_id '; $sql.= 'left JOIN D_trn AS D ON C.d_id = D.id '; $sql.= 'group BY A.id '; // 実行 $query = $connection->execute($sql)->fetchAll('assoc'); foreach ($query as $row) { ?>

            <td><?= $row['id'] ?></td>
            <td><?= $row['name'] ?></td>
            <td><?= $row['cnt'] ?></td>

<?php } ?>

で　できます。

Comments

[yokatone]

すみません.... 順番としてはA->D->Cのほうが適切でした。
ご提示いただいた情報を参考に作ってみたところ、うまくSQLを作れました。ありがとうございます。

SQLを直接記述する方法も教えてくださりありがとうございます。
また、このときですが、今作っているものではビヘイビアを使って論理削除を行っています。
もちろん同様にこのSQLに論理削除の式を入れておけばよいのですが、
なるべくModel情報はシステム内で共通化しておきたいと考えていますので、
質問ばかりで申し訳ございませんが、
$Model->find()メソッドなどでCakePHPらしく実現する方法をご存知でしたら教えてください。