Find conditions 'NOT EXISTS' for hasMany relationships

This question is similar to Find conditions like 'NOT EXISTS' except that it's working with a hasMany relationship.

The tables are:

question开发者_如何学Cs id bigint ...

answers id bigint question_id bigint ...

The relationship is questions hasMany answers.

The query is to look for question ids that have no answers.

SQL might look like

select id from questions where not exists 
    (select * from answers where answers.question_id = questions.id)

The quick way is to just run a query() with the statement, but I'd like to know if there is a CakePHP way.

I'd like to avoid a NOT IN scenario, since that may result in two hits to the database; One to get all question ids for questions that do have answers, and the second to get all question ids for questions that don't have answers.

The other way might be to put the whole where clause in the conditions array as a single entry. I'm just not sure if that is best practice.

---

Without having to alter the database, I ended up using the following query:

$questions = $this->Questions->find('all', array(
    'fields' => array('id'),
    'conditions' => array('not exists '.
        '(select id from answers '.
        'where answers.question_id = '.
        'Question.id)'
     )
));

---

The best way to do this is to have a count field in your posts table. Cake has a built in feature called counter cache.

class Post extends AppModel
{
    var $name = "Post";
    var $hasMany = array('Answer'=>array('counterCache'=>true));
}

You will need to add answer_count to your posts table. This column will be updated AUTOMATICALLY on adding and deleting of associated records.

Then your query is a simple find:

$this->Post->find('all', array('conditions' => array('answer_count' => 0)));

Documentation found here: http://book.cakephp.org/view/1033/counterCache-Cache-your-count