ansaurus

Question

[CakePHP] How to left outer join next tables?

Answer 1

+1 A:

You can do joins in cake like so:

$joins = array(
     array('table' => 'publication_numerations',
       'alias' => 'PublicationNumeration',
       'type' => 'LEFT',
       'conditions' => array(
          'Publication.id = PublicationNumeration.publication_id',
       )
     )
);

$this->Publication->find('all', array('joins' => $joins));

cdburgess 2010-08-26 17:06:36

tnx, this is useful, but i need a bit more help...it's because sql that is generated on your way returns 59 rows and that is ok. but in cakephp application, on index view it returns 53 rows. here is my code:$joins = array( array('table' => 'publication_numerations', 'alias' => 'PublicationNumeration', 'type' => 'LEFT', 'conditions' => array( 'Publication.id = PublicationNumeration.publication_id' ) ) );$this->Publication->find('all', array('joins' => $joins));$this->set('publications', $this->paginate());$this->set(compact('publications'));can you tell me what i'm doing wrong here?

2010-08-26 20:35:33

Basically it means that some Publications have more than one PublicationNumerations. So when the PublicationNumerations are returned, some Publications are displayed more than once. What you need to do is figure out what data you really want and adjust the SQL accordingly.

cdburgess 2010-08-27 01:08:26

Answer 2

A:

Tryed with cake bake model/controller/view, but all the time it joins publications and properties, without publication_numerations.

So, create associations manually - Associations: Linking Models Together. It does not seems that you need 'joins'. By setting associations correctly you will be able to get the list you need by using find('list') in your controller - http://book.cakephp.org/view/1017/Retrieving-Your-Data#find-list-1022.

bancer 2010-08-26 23:54:43

can you check my models? i have updated my original post with them. for me they looks ok; that's why i asked for help here... tnx!

2010-08-27 10:59:16

It is not clear from your update what models associations have you posted. Add the model name for each stack. But I guess the first stack is for `publications` table. It seems for me that there is an error in hasMany association. You have 'PublicationProperty' there but the table name is `properties` so the correct class name should be 'Property'. Post the associations for all 3 models, please.

bancer 2010-08-27 15:45:55

ok, there are all three models. code is rendered by forum, so it's missing escape tag, <?php

2010-08-27 19:49:55

If PublicationNumeration hasAndBelongsToMany you will need to have another table with both foreign keys: 'publication_id' and 'publication_numeration_id'.PublicationNumeration hasMany association assumes that there is a foreign key 'publication_numeration_id' in 'publications' table but there is none.

bancer 2010-08-27 22:42:36

ansaurus

tags:

views:

answers:

[CakePHP] How to left outer join next tables?

related questions