CakePHP3 Cannot Associate and Extract Specific Records from Other Tables

Asked 2 years ago, Updated 2 years ago, 53 views

Thank you for your help.I am currently working on a post site with CakePHP3.I am thinking of displaying the contents of the search based on a keyword on the ranking page.I would like to display the nickname of the comment contributor along with the comments made by other users, but I am having trouble getting the nickname information.

▽Environment ▽
AWS Cloud 9: Free frame
MySQL:ver 5.7.26
CakePHP:ver3.8.2
PHP:ver 7.2.19

▽Current MySQL table structure▽

mysql>show create table users\G
*************************** 1.row******************************
       Table—users
Create Table: CREATE TABLE `users`(
  `id`int(11) NOT NULL AUTO_INCREMENT,
  `email`varchar(255) NOT NULL,
  `password`varchar(255) NOT NULL,
  `nickname`varchar(30) NOT NULL,
  `profile_comment`varchar(100) NOT NULL,
  `created`datetime DEFAULT NULL,
  `modified`datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.00sec) 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,
  `manufacturer`varchar(50) NOT NULL,
  `ice_name`varchar(50) NOT NULL,
  `ice_fraver`varchar(50) NOT NULL,
  `price_no_tax`int (5) unsigned NOT NULL,
  `buy_year`int (4) unsigned NOT NULL,
  `buy_month`int(2) unsigned NOT NULL,
  `image_file`varchar(255) DEFAULT NULL,
  `created`datetime DEFAULT NULL,
  `modified`datetime DEFAULT NULL,
  `simple_comment`varchar(20) NOT NULL,
  `desc_comment`varchar(100) 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=14 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=37 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

▽/src/Controller/IcesController.php▽

class IcesController extensions AppController
{
    public function initialize()
    {
    parent::initialize();
    $this->Auth->allow(['index', 'view', 'search']);
    $this->loadModel('Comments');
    $this->loadModel('Users');

    }
Partial omission
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->where ($where)
            - > contain (['Users', 'Comments')
            ->all();
            $this->set('manufacturer', $manufacturer);
            $this->set('keyword', $keyword);
            $this->set('ices',$ices);
            $this->render('ranking');
    }
    }

It is currently described above.

Search by either manufacturer or combination of manufacturer and ice_fraver, manufacturer and simple_comment in the Ices table and
Information in the Ices table corresponding to ranking + information in the linked Comments table,
In addition, the purpose is to get the nickname of the Users table tied to the user_id of the Comments table that you commented on.


I think I need to change the association-related description already. https://book.cakephp.org/3.0/ja/orm/associations.html
https://book.cakephp.org/3.0/ja/orm/retrieving-data-and-resultsets.html#eager-loading-associations
I have checked the contents of ↑, but I would like to know how to describe the request.
I don't know what and how to change the code I have already written.

How should we change the description from the current state?
Please let me know.

php mysql cakephp

2022-09-30 19:49

1 Answers

Self-resolved.

/src/Model/Table/CommentsTable.php

$this->belongsTo('Users',[
            // 'foreignKey' = > 'id', // ☆ 1
            US>'joinType'=>'INNER'
        ]);

Remove ☆1 and
to /my_pt_lesson/src/Template/Ices/ranking.ctp

<p>BY<?=$this->Html->link(h($ice->comments[0]->user->nickname),
['controller'=>'Users', 'action'=>'view', 
$ice->comments[0]->user['id']])?></p>

I was able to get a username related to the comment by describing it like ...
I didn't get a reply, but if you've seen it even once,
Sorry for the trouble.


2022-09-30 19:49

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.