0

My title will look like naive but I have to say I read/searched/tested everything possible, but my find() method don't implement the JOIN to related tables in the SQL query. I used it several times in other projects without problems but here...

Here my 2 models (nothing special but the manual definition of the related model) :

class Pflanzen extends AppModel {
    public $useTable = 'pflanzen';
    public $hasAndBelongsToMany  = array(
                        'Herbar'  => array(
                                        'order'=>'Herbar.order ASC',
                                        'joinTable' => 'herbar_pflanzen',
                                        'foreignKey' => 'pflanzen_id',
                                        'associationForeignKey' => 'herbar_id') 
            );
}
class Herbar extends AppModel {
    public $useTable = 'herbar';
    public $hasAndBelongsToMany = array(
              'Pflanzen' => array('joinTable' => 'herbar_pflanzen',
                                  'foreignKey' => 'herbar_id',
                                  'associationForeignKey' => 'pflanzen_id')
           )
}

Here my query in the "Herbar" controller (can't be more normal...) :

$pflanzen = $this->Herbar->Pflanzen->find('all',array(
                          'fields'=>array('Herbar.name','Pflanzen.linkplatter'),
                          'conditions' => array('Pflanzen.linkplatter' => true),
                          'order' => 'Herbar.name',
                          'limit' => 10,
                          'recursive'=>2)
                         );
$this->set('pflanzen',$pflanzen);

and the resulting error in the view :

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Herbar.name' in 'field list'

SQL Query: SELECT `Herbar`.`name`, `Pflanzen`.`linkplatter`, `Pflanzen`.`id` FROM `burgerbib`.`platter_pflanzen` AS `Pflanzen` WHERE `Pflanzen`.`linkplatter` = '1' ORDER BY `Herbar`.`name` ASC LIMIT 10

You can see that their is no JOIN in the SQL. Why ?? What do I wrong ? I would really appreciate your help as I'm searching for hours and do no more see any solutions and didn't find nothing using google. Thanks in advance !!

user1036495
  • 71
  • 1
  • 4
  • The error says that you don't have a column called 'name' in the 'Herbar' table in your database. Can you check if that's the case? – Thomas Sep 10 '12 at 13:23

2 Answers2

0

HABTM doesn't make joined queries, it makes a query for all base records and more queries as needed for each relationship to fill the array. Your condition assumes a join, hence the error.

You can force a join using the 'joins' parameter. http://book.cakephp.org/1.2/en/view/872/Joining-tables

petervaz
  • 13,685
  • 1
  • 15
  • 15
  • Oh thanks !!! I'll look it closer. I probably mixed up with BelongsTo links that are made automatically by Cake in the find(). – user1036495 Sep 10 '12 at 15:27
  • Hi, containable behavior is even better: http://book.cakephp.org/2.0/fr/core-libraries/behaviors/containable.html#using-containable – user1036495 Sep 11 '12 at 07:00
  • OK, I was wrong. Containable behavior is only useful to make conditions on the related models, you cannot condition the results of the main Model on fields of related Models with it. Just use The joins parameter as @petervaz said. Thanks – user1036495 Sep 11 '12 at 09:30
-1

In the End, the better way of doing this is using the containable behaviour. Force Join is only useful when the containable behavior don't respond to the need :

http://book.cakephp.org/2.0/fr/core-libraries/behaviors/containable.html#using-containable

user1036495
  • 71
  • 1
  • 4
  • This is actually wrong, Containable is useful, only if you want make a condition on the related models but you cannot make conditions on the results of the main model with it (no Join). Just use the Join solution as petervaz said. – user1036495 Sep 11 '12 at 09:31