CODEWITHSUNDEEP
cakephp
Reuben
Reuben

Reputation: 4276

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:

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

Upvotes: 3

Views: 5437

Answers (2)

Reuben
Reuben

Reputation: 4276

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

Upvotes: 9

minaz
minaz

Reputation: 5790

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

Upvotes: 3

Related Questions