CODEWITHSUNDEEP
mysqlcakephp
Lucas
Lucas

Reputation: 11

I do not 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!

Upvotes: 1

Views: 49

Answers (2)

Harshal Shinde
Harshal Shinde

Reputation: 43

There is another way by you can join the tables in cakephp i.e customized joins

Create an array of tables whom you want to join, such as

$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()
))

This will return all the photos information related to an id

Upvotes: 0

ToX 82
ToX 82

Reputation: 1074

You shouldn't be using the query method directly if you can avoid it. What you can do is using a standard find, joining your tables with containable (or linkable).

Something like this should work:

$registros = $this->Arbol->find('all', array(
    'contain' => 'Foto'
));

http://book.cakephp.org/2.0/en/models/retrieving-your-data.html

Upvotes: 0

Related Questions