Hi!
Problem is quite simple. I want to get $publication with $messages (described in relations) for some $company_id.
Below code works great but it doesn’t fill $publication->messages attribute (default for relation).
Is it possible to use nested joins like described below and use result with $publication->messages?
I also tried with() method but I couldn’t manage it.
$criteria = new CDbCriteria;
$criteria->join = '
LEFT OUTER JOIN `tbl_message` AS `messages`
LEFT OUTER JOIN `tbl_account` AS `sender`
INNER JOIN `tbl_person` AS `sender_person`
ON `sender_person`.`id` = `sender`.`person_id`
AND `sender_person`.`company_id` = '.CPropertyValue::ensureInteger($company_id).'
ON `sender`.`id` = `messages`.`sender_id`
LEFT OUTER JOIN `tbl_account` AS `receiver`
INNER JOIN `tbl_person` AS `receiver_person`
ON `receiver_person`.`id` = `receiver`.`person_id`
AND `receiver_person`.`company_id` = '.CPropertyValue::ensureInteger($company_id).'
ON `receiver`.`id` = `messages`.`receiver_id`
ON `messages`.`publication_id` = '.$this->getTableAlias(true, false).'.`id`
AND (`receiver`.`id` IS NOT NULL OR `sender`.`id` IS NOT NULL)
';
$criteria->compare($this->getTableAlias(true, false).'.`id`', $pk);
return $this->find($criteria);