CakePHP3 複数カラムを参照した平均値の取り方がわかりません

Question

CakePHP3で投稿サイトを制作中です。
現在投稿された記事内の点数と、他ユーザより投稿されたコメント＆点数の、
点数部分を元に平均値を出し、スターレートとして反映させようとしています。
平均値を出すためのカラムが2つのテーブルを参照することになっていますが、
上記の構造を前提にAVG関数での記述をいくつか試しましたが、
思うように結果が得られず、現在も格闘中です。

▽環境▽
AWS Cloud9：無料枠
MySQL：ver5.7.26
CakePHP：ver3.8.2
PHP：ver7.2.19

■実現したいこと
下記2点の、点数の平均値を出したいです。
特定記事情報(Icesテーブル)に紐づく
他ユーザからのコメント情報(Commentsテーブル)の

(カラム名)repeat_rate
(カラム名)stock_rate

それぞれの平均を出し、

view側へ、出力したいです。
※Icesテーブルには
投稿したユーザの記事+投稿ユーザが
感じる上記2点の点数(repeat_rate、stock_rate)を入れ、
Commetsテーブルには
その記事に対し、他ユーザがコメント+上記2点の点数を
入れられるようになっています。

■困っていること
「実現したいこと」に記載の内容の
CakePHPでの記述方法がわかりません。
下記コードを元にどのように追記、修正すべきか、
教えていただきたいです。

▽DBのテーブル構成はusers,ices,commentsの計3つです▽

mysql> show create table users \G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
　　//一部省略

  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> show create table ices \G
*************************** 1. row ***************************
       Table: ices
Create Table: CREATE TABLE `ices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
//一部省略
  `repeat_rate` int(11) DEFAULT NULL,　←"リピート率"の平均値として参照したい
  `stock_rate` int(11) DEFAULT NULL,　 ←"ストック率"の平均値として参照したい
  PRIMARY KEY (`id`),
  KEY `ice_fk` (`user_id`),
  CONSTRAINT `ice_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> show create table comments \G
*************************** 1. row ***************************
       Table: comments
Create Table: CREATE TABLE `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ice_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `comment` varchar(100) NOT NULL,
  `repeat_rate` int(11) DEFAULT NULL,　　←"リピート率"の平均値として参照したい
  `stock_rate` int(11) DEFAULT NULL,　　 ←"ストック率"の平均値として参照したい
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `comments_fk` (`user_id`),
  KEY `comments_ices_fk` (`ice_id`),
  CONSTRAINT `comments_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `comments_ices_fk` FOREIGN KEY (`ice_id`) REFERENCES `ices` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

▽CakePHPのModel部分です▽

/src/Model/Table/UsersTable.php
class UsersTable extends Table
{
    public function initialize(array $config)
    {
        parent::initialize($config);
        $this->setTable('users');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');
        $this->addBehavior('Timestamp'); 
        $this->hasMany('Comments',[
            'foreignKey' => 'user_id'
        ]);
        $this->hasMany('Ices', [
            'foreignKey' => 'user_id'
        ]);      
   }

/src/Model/Table/IcesTable.php
class IcesTable extends Table
{
public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('ices');
        $this->setDisplayField('id');
        $this->setPrimaryKey('id');
        $this->addBehavior('Timestamp');
        
        $this->addBehavior('Josegonzalez/Upload.Upload', [
        'image_file' => []
        ]);

        $this->belongsTo('Users', [
            'foreignKey' => 'user_id',
            'joinType' => 'INNER'
        ]);
        $this->hasMany('Comments', [        
         'foreignKey' => 'ice_id'
        ]);
    }

/src/Model/Table/CommentsTable.php
class CommentsTable extends Table
{
    public function initialize(array $config)
    {
        parent::initialize($config);

        $this->setTable('comments');
        $this->setDisplayField('comment');
        $this->setPrimaryKey('id');

        $this->addBehavior('Timestamp');
        
        $this->belongsTo('Ices', [
            'foreignKey' => 'ice_id',
            'joinType' => 'INNER'
        ]);
        
        $this->belongsTo('Users', [
            'joinType' => 'INNER'
        ]);
    }

▽CakePHPのコントローラ部分です▽

/src/Controller/IcesController.php
public function search()
    {
        $ices = $this->Ices->find('all');
        $manufacturer = isset($this->request->query['manufacturer']) ? $this->request->query['manufacturer'] : null;
        $keyword = isset($this->request->query['keyword']) ? $this->request->query['keyword'] : null;

        if($manufacturer){
            $where = ['Ices.manufacturer' => $manufacturer];
            
            if ($keyword) {
            $where['OR']['Ices.ice_fraver LIKE'] = "%$keyword%";
            $where['OR']['Ices.simple_comment LIKE'] = "%$keyword%";
            }
            
            $ices = $this->Ices->find('all');
            $ices->where($where)
            ->contain(['Comments.Users','Users'])
            ->leftJoinWith('Comments')
            ->group(['Ices.id'])
             //現在思いつく一番希望に近い記述が下記の記載方法でした
            ->select(['rerate' => 'AVG(Comments.repeat_rate + Ices.repeat_rate)']   
            ->select($this->Ices)
            ->order(['rerate' => 'DESC'])

            ->all(); 

            $this->set('manufacturer', $manufacturer);
            $this->set('keyword', $keyword);
            
            $this->set('ices', $this->paginate($ices));
            $this->render('ranking');
    }

上記コントローラ内の

->select(['rerate' => 'AVG(Comments.repeat_rate + Ices.repeat_rate)']   

の部分が自身の思いつく限りの一番希望に近い記述ではありましたが、
この記載方法では、
Commentsテーブルの中だけでrepeat_rateの平均値を出し、
Icesテーブルの中だけででrepeat_rateの平均値を出し、
上記2点を最後に足す、
という処理となるため、本来希望する算出方法とは異なってしまいます。
お手数をおかけしますが、ご教示、よろしくお願いいたします。

Comments

[nojimage]

欲しい値は、（icesのrepeat_rate + icesに紐付くcommentsのrepeat_rateの総和）/ (1 + icesに紐付くcommentsの件数) ということでよろしいでしょうか？ repeat_rateは、NULL許可ですが、NULLの場合の取り扱いはどうしますか？0として扱いますか？

[Ryota.I]

お返事ありがとうございます。
指摘されて気づきましたが、repeat_rateとstock_rateは数値を必ず入れてもらう前提に設計しますので、後程テーブルに修正を加えます。

その上で、

icesのrepeat_rate + icesに紐付くcommentsのrepeat_rateの総和）/ (1 + icesに紐付くcommentsの件数)

で平均値を取りたいと考えています。

[multiposter]

https://ja.stackoverflow.com/questions/59798/
https://teratail.com/help#posted-otherservice

Answer 1

まずは、（icesのrepeat_rate + icesに紐付くcommentsのrepeat_rateの総和）/ (1 + icesに紐付くcommentsの件数) をSQLで表現してみましょう

SELECT
  Ices.id,
  (
    (Ices.repeat_rate + SUM(Comments.repeat_rate)) / (1 + COUNT(Comments.repeat_rate))
  ) AS avg_of_repeat_rate
FROM ices as Ices
LEFT JOIN comments as Comments on (Comments.ice_id=Ices.id)
GROUP BY Ices.id;

(Ices.repeat_rate + SUM(Comments.repeat_rate)) / (1 + COUNT(Comments.repeat_rate)) がその式となります。

これをCakePHPのクエリでselectに指定します。

$ices = $this->Ices->find()
    // その他の条件
    ->select($this->Ices)
    ->group([$this->Ices->aliasField('id')])
    ->leftJoinWith('Comments')
    ->select([
       'avg_of_repeat_rate' => '(Ices.repeat_rate + SUM(Comments.repeat_rate)) / (1 + COUNT(Comments.repeat_rate))',
    ])
    // そのままだと、 avg_of_repeat_rate がstringで返ってくるのでfloatにするよう型変換を指定
    ->setTypeMap([
        'avg_of_repeat_rate' => 'float',
    ])
    ->order(['avg_of_repeat_rate' => 'DESC'])
    ->all();

stock_rateは同様にselectメソッドとsetTypeMapに追加すれば良いので割愛します。

補足：

私なら、テーブル設計を変えてスコア保存用に以下のような ratings テーブルを追加して、Ices,Commentsのレートを同じテーブルに保存できるようにします。

// ratings belongs to ices | ices hasOne ratings
// ratings belongs to comments | comments hasOne ratings
CREATE TABLE `ratings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ice_id` int(11) NOT NULL,
  `comment_id` int(11) DEFAULT NULL,
  `repeat_rate` int(11) NOT NULL,
  `stock_rate` int(11) NOT NULL,
  // timestampフィールド、外部キー制約等は割愛
  PRIMARY KEY (`id`)
)

Icesのレートを保存する場合は、ice_idのみセットしcomment_idはNULLで、Commentsのレートを保存する場合は、ice_idとcomment_idをセットします。これであれば、ratingsテーブルに集約関数（AVG等）を掛けるだけで希望の値が取得できるようになります。

Comments

[Ryota.I]

nojima様
丁寧な回答ありがとうございます。
返信が遅くなり申し訳ありません。

今回いただいたお返事の内容を元に実行し、希望する平均値の値のとり方を
自分の環境上でも確認することができました。

また、テーブルの設計を見直し方についても、
非常に参考となる内容を、ご提示いただきありがとうございます。

貴重なお時間を割いていただき返答いただいた内容については、
公式ドキュメント等の資料と照らし合わせをしながら、
より深く、理解していきます。

改めて、ありがとうございました。