Cakephp complex find condition with count on child table

Question

I have two tables topics and posts. Relation: topics hasmany posts. In both table, there is a status field (Y, N) to moderate the content. In my page, I want to list all not moderated topics for which at least one post status is N or topic status itself is N. Is it possible to do with find function in cakephp2.0. Im using Containable behavior.

I need to apply pagination too.

Answer 1

If i understand correctly you can use:

$conditions => array ('OR' => array ('Topic.status' => 'N', 'Post.status' => 'N'));

Answer 2

Well I haven't tested it but following should work

   $this->recursive = -1;        //necessary to use joins

   $options['joins'] = array(
   'table' => 'posts',
   'alias' => 'Post',
   'type' => 'left',
   'conditions' => array('Topic.id = Post.topic_id', 'Post.status = N') //updated code
   );
   $options['group'] = array('Topic.id HAVING count('Topic.id') >= 1 OR Topic.status = N');
   $this->Topic->find('all', $options);

Answer 3

This is one solution:

1. Search on the Post model for (Posts with N status) OR (Posts which belong to Topics with N status) and store the topic_id
2. Now search on the Topic model for topics with ID on the list

Something like this:

# TopicsController.php

$ids = $this->Topic->Post->find('list', array(
    'fields' => array('Post.topic_id')
    'conditions' => array(
        'OR' => array(
            'Post.status' => 'N',
            'Topic.status' => 'N',
        )
    )
));

$this->paginate = array(
    'conditions' => array('Topic.id' => (array)$ids),
    'order' => array('Topic.created' => 'DESC')
);

$topics = $this->paginate('Topic');

Since you're searching on the Posts model, CakePHP will join the parent Topic data and you can filter by both statuses.