Cakephp - join only if id is not null

Question

I have an activity table with 3 $belongsTo reference keys. I need the join information from these id's i.e. to show the commenttext - I don't want to store the text twice... (same for post and topic).

Activity table: id | post_id | comment_id | topic_id
In each row only post_id OR comment_id OR topic_id is set, the other two id fields are NULL.

So i.e. if post_id = 55, comment_id = NULL, topic_id = NULL I get this:

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 id is NOT NULL? I don't want to kill the empty arrays after the find with a php for-each loop.

Another idea was this database table: id | activitytype_id | refid to join with dynamic binding the necessary table depending on the activitytype_id. - That didn't work as well...

That's what I want - is that 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 would need to query the database to find out which IDs are null and then query the database a second time to grab the related 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 wouldn't waste your time writing two queries; let CakePHP get all the results in a single query. :)

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

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

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

Answer 2

You can always make the joins manually. What you want is to do INNER JOIN instead of the LEFT JOIN done by cake. You can also do an afterFind() to delete this something like this.

in your model where you are using the 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']);
       }
    }
}

The join is a more direct solution though.