CakePHP3 複数カラムを参照した平均値の取り方がわかりません
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点を最後に足す、
という処理となるため、本来希望する算出方法とは異なってしまいます。
お手数をおかけしますが、ご教示、よろしくお願いいたします。