Nested Result while using CakePhp Containable

Question

I am not sure about the question title but tried to explain more below. Thanks in advance for all who helps me.

I sampled the tables to simplify my questions.

DB Tables

News: news_id, title
Comments: comment_id, news_id, content, created (timestamp)

Users are posting comments to the news. In a search with the input of "date interval 01.10.2015 - 05.11.2015", the result should include: the news which has been commented between this interval. And comment_count should be shown next to the title on the screen. This comment_count is not whole comment_count, only the count between this dates.

To do that, I ran the query first in comments. Then count comments. Then group by news_id.

$Data = $this->Comment->find('all', array(
        'fields' => array(
            'News.news_id','News.title'
        ),
        'conditions' => array(
            "Comment.created >=" => date('Y-m-d', strtotime("-1 days")) //yesterday's commented news
        ),
        'contain' => array(
            'News' => array(
                'Comment => array(
                    'fields' => array('COUNT(Comment.id) as comment_count'),
                )
            )
        ),
        'group' => array('News.title')
    ));

The result is below with too much nesting:

Array
(
    [0] => Array
        (
            [News] => Array
                (
                    [id] => 27
                    [title] => sample news title
                    [News] => Array
                        (
                            [id] => 27
                            [title] => sample news title
                            [Comment] => Array
                                (
                                    [0] => Array
                                        (
                                            [News_id] => 27
                                            [Comment] => Array
                                                (
                                                    [0] => Array
                                                        (
                                                            [Comment_count] => 1
                                                        )
                                                )
                                        )
                                )
                        )
                )
        )
)

In conclusion, to reach comment_count, I have to write the nestings manually. However I would like to reach it in the same level with first [title] node.

$JsonData [] = array(
            "id" =>  $Item["News"]["id"],
            "title" =>  $Item["News"]["title"],
            "comment_count" =>  $Item["News"]["News"]["Comment"][0]["Comment"][0]["comment_count"]
            );

How can I reduce the life-long nesting?

Thanks in advance.

score 1 · Answer 1 · answered Nov 29 '15 at 11:11

First of all, you should name your table id like id not news_id. For your question, I'm not sure from what controller you are displaying news, IMO this should be done in NewsController. Here is how you can retrieve your news with comments count from previous day:

$Data = $this->News->find('all', array(
'fields' => array(
    'News.id', 
    'News.title',
    'COUNT(Comment.id) as comment_count'
),
'group' => array('News.id'),
'joins' => array(
    array(
        'table' => 'comments',
        'alias' => 'Comment',
        'conditions' => array(
            'News.id = Comment.news_id',
            'Comment.created >=' => date('Y-m-d', strtotime("-1 days"))
        )
    )
)

));

This should give you resulting array like this:

array(
(int) 0 => array(
    'News' => array(
        'id' => '1',
        'title' => 'New news'
    ),
    (int) 0 => array(
        'comment_count' => '2'
    )
),
(int) 1 => array(
    'News' => array(
        'id' => '2',
        'title' => 'Seconds news'
    ),
    (int) 0 => array(
        'comment_count' => '1'
    )
),
)

Then you can get your comment count(assuming through foreach loop):

... "comment_count" => $Data[0]['comment_count'] ...

You should look counterCache it might be useful to you.

Hi @skywalker, Thanks for your great help. Actually, in my db, it was already id instead of news_id but I gave it here to simplify my problem. It seems my problem solved but is there anyway without using join. I would prefer to use Cake's existing model-relationships. Thanks. — hrnsarac, Nov 29 '15 at 20:49
As I said, you can try with counterCache, but that way you can't get comments for given period of time, only number of total comments for given news. I am not sure if you can do this other then using `join`. — skywalker, Nov 29 '15 at 21:18
In that result, I don't need the comment details. I need only commentCount with the given date interval. Let me try counterCache. Thanks. — hrnsarac, Nov 30 '15 at 07:22

Nested Result while using CakePhp Containable

1 Answers1