查找"NOT EXISTS"之类的条件 [英] Find conditions like 'NOT EXISTS'

问题描述

我的数据库中有2个表...

I have 2 tables in my db...

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

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

公共对象
....
模型 varchar(50)我需要的模型(在这种情况下为'Entita')
model_id int(11)

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

我想这样查询:
select entita.* from entita where NOT EXISTS (select * from publicobjects where publicobjects.model = 'Entita' and publicobjects.model_id = entita.id)

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)

如何在不使用自定义查询的情况下使用Cakephp的模型功能执行此操作?

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

谢谢

推荐答案

我相信您正在尝试从Entita表中查找不在Publicobjects表中的行.假设这是正确的，下面是MySQL的SQL查询来找到它:

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

要使它与CakePHP模型一起使用，需要执行几个步骤.我已经对您的型号名称做了一些假设，但是我可能错了，而且很容易解决.

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.

首先将其添加到Entita模型中:

First add this to the Entita model:

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

现在，您可以像这样检查Publicobjects表中缺少的条目:

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

这篇关于查找"NOT EXISTS"之类的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！