I don´t understand Joining tables

Need Help
1

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!

2

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

3

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

4

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.

5

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