質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.04%

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

受付中

回答 1

投稿

  • 評価
  • クリップ 0
  • VIEW 2,298

score 43

 分からない事

以下のような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|
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

0

前提を確認させてください。
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

もしも 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 
}
?>
で できます。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/02/22 13:35

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

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.04%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る