[FIXED] Change join contain() condition in Cakephp

Issue

I try to use contain to make an full join of 2 Tables but i don't know how to change the condition:

$select= $this->Friends->find('all')
                ->order(['Friends.id' => 'ASC'])
                ->contain([
                    'Animals'
                ])
                ->where(['animal1_id'=> $animalsid,
                    'confirmed'=>'1'
                ]);
            $this->set(compact('select'));

And so look the SQL:

 SELECT 
  * 
FROM 
  friends Friends 
  INNER JOIN animals Animals ON Animals.id = (Friends.animal2_id) 
WHERE 
  (
    animal1_id = 4 
    AND confirmed = 1
  ) 
ORDER BY 
  Friends.id ASC

The problem is that this Friends.animal2_id i want to change with Friends.animal1_id but i don't know how? Maybe there another methods? I tried to use join but i get just one table not both.

Solution

I am considering that you have belongsTo relationship between the two model friends and animals.

So you just have to change the foreignKey name in the association, so update the association in your Friends model like

$this->belongsTo('Animals', [
    'foreignKey' => 'animal1_id',  //Here the column name you want to use for join
    'className'=>'Animals',
    'joinType' => 'INNER'
]);

EDIT(for comment query):- If you want to select other tables, you can achieve this in two ways

In controller

$this->loadModel('OtherModelName');   //This will load your model to $this object
$other_table_result = $this->OtherModelName->find('all')->toArray();

Method to use raw SQL queries,

$sql = "SELECT * FROM users"; 
$query = $this->connection->prepare($sql); 
$query->execute(); 
$result = $query->fetchAll();

Answered By - bikash.bilz