I don´t understand Joining tables

I’m using Cake version 2.5.4 I have two tables.

A call arbols which consists of the following fields: id (int 11) nombre (varchar (255) especie:id (int 11)

The second table called fotos consists of the following fields:

id (int 11)
foto (varchar 255)
foto_dir (varchar 255)
arbol_id (int 11)
Although the Arbol model is related to the Especie model, I leave it aside to the latter because it is not a reason for consultation. The Arbol Model has a hasMany relationship with the Foto model.

In the Arbol model I have the following:

public $hasMany = array(
‘Foto’=> array(
‘className’ => ‘Foto’,
‘foreignKey’ => ‘arbol_id’,
‘dependent’ => true
)
);

In the Foto model I have the following:
public $belongsTo = array(
‘Arbol’=> array(
‘className’=>‘Arbol’,
‘foreign_key’=>‘arbol_id’
)
);
Now, inside ArbolsController in public function view ($ id = null) I want to do the following SQL query:

SELECT * FROM arboles as a join fotos as f on a.id=f.arbol_id
So I return all the photos related to an id of a particular tree passed as parameter in view If this query is done using MySQL

$registros=mysqli_query($conexion," select * from arboles as a join fotos as f on a.id=f.arbol_id")
it works. But if I want to do it using the query method in such ways:

$registros = $this->Arbol->query(“select * from arboles as a INNER JOIN fotos as f ON a.id=f.arbol_id”);
$registros = $this->Arbol->query(“select * from arboles as a INNER JOIN fotos as f ON a.id=f.arbol_id”);
It does not work Reading the Cookbook I see there is a way to make joins. http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html

I dont understand her

I would appreciate it if you can explain it to me.

From already thank you very much!

You need to search on google or yahoo joins and practice some first.

Did you read “Joining tables”?

$options[‘joins’] = array(
array(‘table’ => ‘channels’,
‘alias’ => ‘Channel’,
‘type’ => ‘LEFT’,
‘conditions’ => array(
Channel.id = Item.channel_id’,
)
)
);

$Item->find(‘all’, $options);

In your case type => INNER

In this way I managed to solve my problem:

$joins = array(
array(
‘table’ => ‘fotos’,//Table name
’alias’ => ‘Foto’, //Model name
’type’ => ‘INNER’, // type of join
’conditions’ => array(
Arbol.id = Foto.arbol_id’
) //Condition for join
)
);

$this->Arbol->find(‘all’, array(
‘joins’ => $joins,
‘conditions’=> array(Arbol.id => $id),
‘fields’ => array()
))

Thank you very much for your very explanatory answers.

It’s not me , it’s CakePHP documentations :wink: