Cakephp - joins only if id is not equal to zero

Question

I have an activity table with 3% reference keys. I need connection information from this identifier, that is, to display the text of comments - I do not want to store the text twice ... (the same for mail and subject).

Action Table: id | post_id | comment_id | topic_id
Each line contains only post_id OR comment_id OR topic_id, the other two id fields are NULL.

So, if post_id = 55, comment_id = NULL, topic_id = NULL, I get the following:

Array ( [0] => Array ( [Activity] => Array ( [id] => 1 [post_id] => 55 [comment_id] => [topic_id] => ) [Post] => Array ( [id] => 55 [name] => Post #1 [description] => This is Post #1. ... ) [Comment] => Array ( [id] => [post_id] => [titel] => [description] => ... [created] => [modified] => ) [Topic] => Array ( [id] => ... [created] => [modified] => ) ) [1] => Array ( ... 

Is there a way to join only if the reference identifier is NOT NULL? I don't want to kill empty arrays after searching with php for each loop.

Another idea is a database table: id | activitytype_id | refid to combine with dynamic linking of the required table depending on activitytype_id. - It didn't work ...

What do I want - is it possible?

 Array ( [0] => Array ( [Activity] => Array ( [id] => 1 [post_id] => 55 [comment_id] => [topic_id] => ) [Post] => Array ( [id] => 55 [name] => Post #1 [description] => This is Post #1. ... ) ) [1] => Array ( [Activity] => Array ( [id] => 2 [post_id] => [comment_id] => 2 [topic_id] => ) [Comment] => Array ( [id] => 2 [post_id] => 4 [titel] => Blabla [description] => This is the comment description ... [created] => 2011-01-01 01:30:00 [modified] => 2011-01-01 01:30:00 ) ) [2] => Array ( ... 

Thanks in advance!: -)

Answer 1

You will need to query the database to find out which identifiers are null , and then query the database a second time to capture the associated data.

 $activity = $this->Activity->read(null, 1); // some logic to find foreign key with non-null value $activity[$model] = $this->{$model}->read(null, $id); 

I would not waste time on two questions; let CakePHP get all the results in one query. :)

 $activity = $this->Activity->findById(1); 

Just add this to your model to filter out empty values ​​from the results:

 public function afterFind($results, $primary = false) { return Hash::filter($results); } 

Answer 2

You can always create connections manually. You want to make an INNER JOIN instead of a LEFT JOIN made by a cake. You can also do afterFind () to remove this something like this.

in your model where you use find method

 function afterFind($results){ foreach($results as $k => $result){ if (empty($result['Post']['id'])){ unset($results[$k]['Post']); } if (empty($result['Comment']['id'])){ unset($results[$k]['Comment']); } if (empty($result['Topic']['id'])){ unset($results[$k]['Topic']); } } } 

Connection is a more direct solution.