CODEWITHSUNDEEP
phpactiverecordcakephp
dianovich
dianovich

Reputation: 2287

CakePHP joining tables when a relationship exists

I have a Message model that hasMany Librarian.

The problem I am having is that when I try to join a table to the Librarian table, that table has not been joined yet - i.e. the join I create appears before the relationship join is created.

$this->Message->find('all', array(
  'joins' => array(
    array(
      'table' => 'users',
      'alias' => 'User',
      'conditions' => array('User.id = Librarian.id')
    )
  )
));

This generates a query along these lines:

SELECT `Message`.`id`, `Message`.`librarian_id`, 
   `Message`.`Librarian`.`id`, `Librarian`.`user_id` 
    FROM `contact_messages` AS `Message` 
    INNER JOIN users AS `User` ON (`User`.`id` = `Librarian`.`user_id`) 
    LEFT JOIN `librarians` AS `Librarian` 
    ON (`Message`.`librarian_id` = `Librarian`.`id`)  
    WHERE `Message`.`id` = 3

I get the error

Unknown column 'Librarian.user_id' in 'on clause'

How can I join to a hasMany table after it has already been included in the build query?

Cheers

Upvotes: 1

Views: 235

Answers (2)

dianovich
dianovich

Reputation: 2287

I'm not sure how well I explained myself.

I should probably have broken down the problem into the exact relationships:

Message hasMany Librarian
Librarian belongsTo User

which means i'm able to set recursive = 2 to get User data.

Granted this is expensive but is the natural thing to do in this case.

Upvotes: 0

David Yell
David Yell

Reputation: 11855

I tended to tackle this by actually binding a relationship into the code as I went along. It's a hacky method and quite old, very 1.2.

This was using bindModel() which you can read about on here, http://book.cakephp.org/view/3/The-Manual#!/view/78/Associations-Linking-Models-Together

The documentation for the model method is here, http://api12.cakephp.org/class/model#method-ModelbindModel

The idea basically being that you can temporarily bind two models together even if no relationship exists, or if they have a distant relationship. I have done it, and it does work. I have some code somewhere, but not to hand.

Also if you are using newer stuff, be sure to check out Containable() as this, as I understand it, encompasses a little more of the model's bindings. http://book.cakephp.org/#!/view/1323/Containable

Upvotes: 1

Related Questions