Cakephp - 仅当id不为null时才加入 [英] Cakephp - join only if id is not null

问题描述

我有一个活动表与3 $ belongsTo参考键。我需要从这些id的连接信息ie显示commenttext - 我不想存储文本两次...（同样的帖子和主题）。

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).

活动表：id | post_id | comment_id | topic_id

在每一行中只设置了post_id OR comment_id或topic_id，其他两个id字段都为NULL。

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.

因此，如果post_id = 55 ，comment_id = NULL，topic_id = 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
    (
        ...

引用id不是NULL？我不想杀死空数组后找到一个php for-each循环。

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.

另一个想法是这个数据库表：id | activitytype_id | refid根据activitytype_id动态绑定必要的表 - 这不起作用...

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...

这是我想要的 - 是可能吗？

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
    (
        ...

/ p>

Thanks in advance! :-)

推荐答案

您需要查询数据库以了解哪些ID是 null 然后再次查询数据库以获取相关数据。

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);

我不会浪费你的时间编写两个查询;让CakePHP在单个查询中获取所有结果。 ：）

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);
}

这篇关于Cakephp - 仅当id不为null时才加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！