Cakephp3 クエリービルダーでSELECT *,sum(hoge) as hoge_ttl FROM ~をしたいです。

Question

【やりたいこと】
・下記のようなSQL文をCakephp3 クエリービルダーで実現したいです。

SELECT *,sum(hoge) as hoge_ttl 
FROM hoge_table
WHERE ymd='2020-02-19' group by hoge_id order by temperature,hoge_id;

【試してみたこと】
・下記実行したクエリービルダーですが、もちろんselectしたカラムのみしか取得できませんでした。
selectに一つずつほしいカラムを記述してもいいのですが、それですとcontainで定義したモジュールと紐づかない為、リレーション先の値も取得できませんでした。

$hoge_table = $this->HogeTable->find('all',[
                        'conditions'=>['ymd'=>'2020-02-19'],
                        'contain'=>['HogeUnit'],
                        'group'=>'hoge_id',
                        'order' => 'temperature,hoge_id ASC'
                    ]);
$hoge_table ->select([
          "hoge_ttl "=>$hoge_table ->func()->sum('hoge'),
]);
$hoge_table = $hoge_table ->toArray();

・このようにリレーション先(hoge_unit)の値もしっかり取得できるようにしたいです。

<?php for ($i=0; $i < count($hoge_table ); $i++) { ?>
<tr>
<td><?= $hoge_table [$i]->name ?></td>
<td><?= $hoge_table [$i]->hoge_unit->name ?></td>
</tr>
<?php }?>

とりわけCakephp3 クエリービルダーにて、SQL文でいう「SELECT *,sum(hoge) as hoge_ttl 」の部分を実現できる方法を教えていただけると幸いです。
よろしくお願いいたします。

Answer 1

テーブルのフィールド全部をselectメソッドで指定する方法があります。

特定のフィールドを選択 | クエリービルダー - CakePHP 3.8

select($fields) を呼んで、なおもテーブルのすべてのフィールドを選択したいなら、 次の方法でテーブルインスタンスを select() に渡すことができます。

// 計算された slug フィールドを含む、 articles テーブルのすべてのフィールドを取得
$query = $articlesTable->find();
$query
    ->select(['slug' => $query->func()->concat(['title' => 'identifier', '-', 'id' => 'identifier'])])
    ->select($articlesTable); // articles のすべてのフィールドを選択する

ということで、HogeTableとそのリレーションであるHogeUnitのテーブルクラスをselectメソッドへ与えてやればよいです。

$query
->select('hoge_ttl' => $query->func()->sum('hoge'))
->select($this->HogeTable)
->select($this->HogeTable->HogeUnit->getTarget()) // リレーションのTableオブジェクトを取得してセット

Comments

[aaayii]

お返事遅れてしまい申し訳ございません。
ご回答ありがとうございます。

ご回答を参考にさせていただいた結果、解決しました！
今後ともお力をお借りできればと思います

Answer 2

SQL文でいう「SELECT *,sum(hoge) as hoge_ttl 」

そんな記述はできません。文法エラーです。

インラインビューを使用して下さい。
（サブクエリーでも良いですけど、インラインビューの方が高速だと思います）

SELECT t1.*, t2.hoge_ttl 
FROM hoge_table t1 
     inner join (
       select hoge_id, sum(hoge) as hoge_ttl 
       from hoge_table 
       group by hoge_id
     ) t2
     on t1.hoge_id=t2.hoge_id  
WHERE ymd='2020-02-19'
order by temperature, t1.hoge_id
;

クエリービルダーでのインラインビューの記述は以下を参考に。
CakePHP3でクエリビルダーを使用してサブクエリの書き方を教えてください！

Comments

[aaayii]

お返事遅れてしまい申し訳ございません。
ご回答ありがとうございます！！

また違った処理でこちらは参考にさせていただきました！
ありがとうございます

Answer 3

selectに一つずつほしいカラムを記述してもいいのですが、それですとcontainで定義したモジュールと紐づかない為、リレーション先の値も取得できませんでした。

意味がわかりません。質問にきちんとCREATE TABLEを提示しましょう。

SELECT した列の並び毎にSUM するので、SQLの文法上
SELECT した列の並び毎にSUM... GROUP BY 列の並び毎
を明示するのが鉄の掟です。

SELECT *,sum(hoge) as hoge_ttl 
FROM hoge_table
WHERE ymd='2020-02-19' group by hoge_id order by temperature,hoge_id;

            ↓
SELECT
    temperature
   , hoge_id
   , など各列の並び
  , sum(hoge) as hoge_ttl 
FROM hoge_table
WHERE ymd='2020-02-19'
group by
    temperature
  , hoge_id
  , 各列の並び
order by temperature,hoge_id;

Comments

[aaayii]

お返事遅れてしまい申し訳ございません。
ご指摘ありがとうございます！！

ご指摘いただいた部分はcakephp3のクエリービルダーに関しての内容です。
混同してしまい申し訳ございません