Find conditions like 'NOT EXISTS'

Question

Hi all... I have 2 tables in my db...

Entita
id int(11)
descrizione varchar(50)
.....

Publicobjects
....
model varchar(50) the model I need (in this case 'Entita')
model_id int(11)

I would like to make a query like this:
select entita.* from entita where NOT EXISTS (select * from publicobjects where publicobjects.model = 'Entita' and publicobjects.model_id = entita.id)

How can I do this with the model functions of Cakephp without use custom query?

Thanks

Answer 1

I believe you're trying to find rows from the Entita table that are not in the Publicobjects table. Assuming that is correct, here is the SQL query for MySQL to find it:

SELECT `entita`.*
FROM `entita` 
LEFT JOIN `publicobjects` ON (`publicobjects`.`model` = 'entita' 
    AND `publicobjects`.`model_id` = `entita`.`id`)
WHERE `publicobjects`.`model_id` IS NULL

To make this work with CakePHP's models takes a couple of steps. I've made some assumptions about your model names, but I could be wrong and those are easy to fix.

First add this to the Entita model:

<?php
var $hasOne = array('Publicobject' => array(
    'foreignKey' => 'model_id',
    'conditions' => 'Publicobject.model = "Entita"'));

Now, you can check for entries that are missing in the Publicobjects table like this:

<?php
$this->Entita->find('all', array('conditions' => array('Publicobject.model_id IS NULL')));