[FIXED] cakephp displays the ID display table.name from two different tables with relations

Issue

I should save the respective ID of the table "dogs" and "cats" in "dogs_cats" and when see the data display the name of dogs and cats.

I have these three tables:

CREATE TABLE IF NOT EXISTS cats (
  id int(11) NOT NULL,
  name varchar(40) NOT NULL,
  surname varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS dogs (
  id int(11) NOT NULL,
  name varchar(40) NOT NULL,
  surname varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS dogs_cats (
  id int(11) NOT NULL,
  dog_id int(11) NOT NULL,
  cat_id int(11) NOT NULL,
  info varchar(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE dogs
ADD PRIMARY KEY (id),
MODIFY id int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE cats
ADD PRIMARY KEY (id),
MODIFY id int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE dogs_cats
ADD PRIMARY KEY (id),
MODIFY id int(11) NOT NULL AUTO_INCREMENT,
ADD FOREIGN KEY(dog_id) REFERENCES dogs(id),
ADD FOREIGN KEY(cat_id) REFERENCES cats(id);

schema DB

pratical ex:

table cats:
id name surname
1  tony may

table dogs:
id name surname
1  pop  gray

table dogs_cats:
id dog_id cat_id info
1  1       1       pop and tony

but visualize:
id name_dog name_cat info
1  pop       tony      pop and tony

Relations

for each ennuple of table "dogs" there may be one or more ennuples of table "dogs_cats": "dogs" hasMany "dogs_cats" and "dogs_cats" belongTo "dogs"

for each ennuple of table "cats" there may be one or more ennuples of table "dogs_cats": "cats" hasMany "dogs_cats" and "dogs_cats" belongTo "cats"

src>Model>Table>DogsTable.php

<?php

use App\Model\Entity\Dog;
use Cake\ORM\Table;
use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Validation\Validator;

use Cake\ORM\Table;

class DogsTable extends Table
{
    public function initialize(array $config)
    {
        $this->addBehavior('Timestamp');


        $this->hasMany('DogsCats',[
            'foreignKey'=>'dog_id'
        ]);
    }


}
?>

src>Model>Table>CatsTable.php

<?php

use App\Model\Entity\Cat;
use Cake\ORM\Table;
use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Validation\Validator;

use Cake\ORM\Table;

class DogsTable extends Table
{
    public function initialize(array $config)
    {
        $this->addBehavior('Timestamp');


        $this->hasMany('DogsCats',[
            'foreignKey'=>'cat_id'
        ]);
    }


}
?>

src>Model>Table>DogsCatsTable.php

<?php

use App\Model\Entity\DogsCat;
use Cake\ORM\Table;
use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Validation\Validator;

use Cake\ORM\Table;

class DogsCatsTable extends Table
{
    public function initialize(array $config)
    {
        $this->addBehavior('Timestamp');


        $this->belongsTo('dogs', [
            'foreignKey' => 'id',
            'joinType'=>'INNER',
        ]);

        $this->belongsTo('cats', [
            'foreignKey' => 'id',
            'joinType'=>'INNER',
        ]);
    }


}
?>

how can I take the fields of tables dogs and cats from controller DogsCatsController.php?

Solution

If your association rule is ok then You can have this simply by using the following in contains(change as per your need ) :

$query = $this->DogsCats
        ->find('all');
$query->contain(  ['Dogs','Cats']
                );
$results = $query->toArray();
$this->set('results', $results );

If you want to have only id , then you can try the following :

$query->contain(
                  [
                    'Dogs'=>['fields' => ['id']],
                   'Cats'=>['fields' => ['id']]
                  ]
                );

Answered By - Shifat