I am currently working on a post site with CakePHP3.
of the scores in the article currently posted and comments and scores posted by other users.
Based on the score, we are trying to reflect the average value as a star rate.
Two tables are supposed to be referenced by columns for averaging.
Based on the above structure, we have tried some descriptions in AVG functions.
I didn't get the results I wanted, and I'm still fighting.
▽Environment ▽
AWS Cloud 9: Free frame
MySQL:ver 5.7.26
CakePHP:ver3.8.2
PHP:ver 7.2.19
■What you want to achieve
I would like to give the average score for the following two points.
Associate with specific article information (Ices table)
Comment Information from Other Users (Comments Table)
(column name) repeat_rate
(column name) stock_rate
Average each and
I would like to output it to the view side.
IIn the Ices table,
Posted User Articles + Posted User
Add the above two points you feel (repeat_rate, stock_rate) and
The Commets table contains
Another user commented on the article + scored the above two points
You can now put it in.
■What's troubling you
of what you want to accomplish
I don't know how to write in CakePHP.
How should I add and modify the code below?
Please let me know.
▽ DB has a total of three tables: users, ices, and comments▽
mysql>show create table users\G
*************************** 1.row******************************
Table—users
Create Table: CREATE TABLE `users`(
`id`int(11) NOT NULL AUTO_INCREMENT,
// Some omitted
PRIMARY KEY (`id`)
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql>show create tables\G
*************************** 1.row******************************
Table:ices
Create Table: CREATE TABLE `ices`(
`id`int(11) NOT NULL AUTO_INCREMENT,
`user_id`int(11) NOT NULL,
// Some omitted
`repeat_rate`int(11) DEFAULT NULL, ← I want to refer to it as the average of "repeat rate"
`stock_rate`int(11) DEFAULT NULL, ← I would like to refer to it as the average of "stock rate"
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, ← I want to refer to it as the average of "repeat rate"
`stock_rate`int(11) DEFAULT NULL, ← I would like to refer to it as the average of "stock rate"
`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)
▽This is the model part of CakePHP▽
/src/Model/Table/UsersTable.php
class UsersTable extensions 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 extensions 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',
US>'joinType'=>'INNER'
]);
$this->hasMany('Comments',[
'foreignKey' = > 'ice_id'
]);
}
/src/Model/Table/CommentsTable.php
class CommentsTable extensions 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',
US>'joinType'=>'INNER'
]);
$this->belongsTo('Users',[
US>'joinType'=>'INNER'
]);
}
▽ This is the controller part of 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'])
// The closest description I can think of right now was the method below.
->select('rate'=>'AVG(Comments.repeat_rate + Ices.repeat_rate)']
->select ($this ->Ices)
->order(['rate'=>'DESC'])
->all();
$this->set('manufacturer', $manufacturer);
$this->set('keyword', $keyword);
$this->set('ices', $this->paginate($ices));
$this->render('ranking');
}
->select(['rate'=>'AVG(Comments.repeat_rate + Ices.repeat_rate)']
Although
was the closest description I could think of,
In this documentation,
Average repeat_rate in the Comments table only and
Average repeat_rate in the Ices table only and
Add the above two points to the end,
Because of this process, it is different from the calculation method originally desired.
Sorry for the inconvenience, but please let me know.
© 2024 OneMinuteCode. All rights reserved.