php CakePHP 删除多个条件

CakePHP Delete with Multiple Conditions

提问by Mike Miller

This is driving me mad. Why does Cake need to make simple things overly complex..

这让我发疯。为什么Cake要把简单的事情变得复杂。。

I am hoping to get Cake to generate SQL that looks like this..

我希望让 Cake 生成看起来像这样的 SQL..

I expect the SQL that runs to be

我希望运行的 SQL 是

DELETE `ProductVouchers` 
FROM `product_vouchers` AS `ProductVouchers`   
WHERE `ProductVouchers`.`id` = 16 
AND `ProductVouchers`.`client_id` IS NULL; 

I am using delete()like this

我使用delete()这样的

$this->ProductVouchers->delete([
    'ProductVouchers.id'=>$id,
    'ProductVouchers.client_id'=>"NULL"
]);

The log shows

日志显示

DELETE `ProductVouchers` 
FROM `product_vouchers` AS `ProductVouchers`   
WHERE `ProductVouchers`.`id` IN (16, 'NULL')

Trying

试

$this->ProductVouchers->delete([
   'ProductVouchers.id'=>$id,
   'ProductVouchers.client_id'=>NULL
]);

Log shows

日志显示

DELETE `ProductVouchers` 
FROM `product_vouchers` AS `ProductVouchers`   
WHERE `ProductVouchers`.`id` IN (16, NULL) 

Trying:

试：

 $this->ProductVouchers->delete([
    'ProductVouchers.id'=>$id,
    'ProductVouchers.client_id IS NULL'
 ]);

Log shows nothing which is even more wrong!

日志什么也没显示，这更不对！

EDIT:

编辑：

As pointed out in the answer below the delete()method is incorrect as it only accepts the primary key as an integer. So using deleteAll()

正如下面的答案所指出的，该delete()方法是不正确的，因为它只接受主键作为整数。所以使用deleteAll()

 $this->ProductVouchers->deleteAll([       
   'ProductVouchers.id'=>$id,
   'ProductVouchers.client_id'=>'NULL'
 ]);

This also produces nothing in the log or any error. Trying:

这也不会在日志中产生任何内容或任何错误。试：

 $this->ProductVouchers->deleteAll([
   'ProductVouchers.id'=>$id,
   'ProductVouchers.client_id'=>NULL
 ]);

This produces...

这产生...

 DELETE `ProductVouchers` 
 FROM `product_vouchers` AS `ProductVouchers`   
 WHERE `ProductVouchers`.`id` = (17)

Which is both wrong and weird (whats the point of the brackets??)

哪个既错误又奇怪（括号的意义是什么？？）

回答by AD7six

Model::delete expects an id

Model::delete 需要一个 id

Delete is a method for deleting one row, by primary key value. The method signature for Model::deletedoes not expect conditions:

Delete 是一种通过主键值删除一行的方法。Model::delete的方法签名不期望条件：

delete( ) public

Removes record for given ID. If no ID is given, the current ID is used. Returns true on success.

Parameters

integer|string $id optional null - ID of record to delete

boolean $cascade optional true

删除( ) 公共

删除给定 ID 的记录。如果没有给出 ID，则使用当前 ID。成功返回真。

参数

整数|字符串 $id 可选 null - 要删除的记录的 ID

布尔 $cascade 可选 true

The behavior when called passing an array is that it won't work, one way or another (in the case here, the array values of the conditions array are understood to be ids - and used to find arecord to delete - it would delete at most one row).

调用传递数组时的行为是它不会以一种或另一种方式工作（在这种情况下，条件数组的数组值被理解为 ids - 并用于查找要删除的记录 - 它会删除最多一行）。

Model::deleteAll expects conditions

Model::deleteAll 期望条件

deleteAllis a method for deleting rows by conditions:

deleteAll是一种按条件删除行的方法：

/**
 * Deletes multiple model records based on a set of conditions.
 *
 * @param mixed $conditions Conditions to match
 * @param bool $cascade Set to true to delete records that depend on this record
 * @param bool $callbacks Run callbacks
 * @return bool True on success, false on failure
 * @link http://book.cakephp.org/2.0/en/models/deleting-data.html#deleteall
 */
    public function deleteAll($conditions, $cascade = true, $callbacks = false) {

The correct call for the logic in the question would be:

对问题中逻辑的正确调用是：

$model->deleteAll(
    [
        'ProductVouchers.id' => 16, 
        'ProductVouchers.client_id' => null
    ],
    $cascade,
    $callbacks
);

This also produces nothing in the log or any error

这也不会在日志中产生任何内容或任何错误

Calling deleteAll with $cascadetrue (the default) means:

使用$cascadetrue（默认值）调用 deleteAll意味着：

• Find all records matching the conditions
• Delete them one by one

• 查找所有符合条件的记录
• 一一删除

If no records are found matching the conditions, there will be no delete statement.

如果没有找到符合条件的记录，则不会有删除语句。

This condition fragment:

这个条件片段：

'ProductVouchers.client_id'=>'NULL'

Will generate this sql:

会生成这个sql：

WHERE ProductVouchers.client_id = 'NULL'

I.e. it will return rows where client_id is the string "NULL" - hence there is no delete statement because there are norows with a client_id set to the string "NULL".

即它将返回 client_id 是字符串“NULL”的行 - 因此没有删除语句，因为没有client_id 设置为字符串“NULL”的行。

Which is both wrong and weird (whats the point of the brackets??)

哪个既错误又奇怪（括号的意义是什么？？）

Actually that statement is expected ifthere is a record with that primary key and client_id set to null (there will be a select statement immediately before it to find by id and client_id value). The parentheses are of no functional relevance, these two statements are equivalent:

实际上，如果有一条主键和 client_id 设置为 null 的记录（在它之前会有一个 select 语句通过 id 和 client_id 值查找），那么该语句是预期的。括号没有功能相关性，这两个语句是等效的：

DELETE FROM `product_vouchers` WHERE `ProductVouchers`.`id` = (17)
DELETE FROM `product_vouchers` WHERE `ProductVouchers`.`id` = 17

How to delete by conditions?

如何按条件删除？

To generate the equivalent of this sql:

要生成此 sql 的等效项：

DELETE FROM `product_vouchers` AS `ProductVouchers`   
WHERE `ProductVouchers`.`id` = 16 
AND `ProductVouchers`.`client_id` IS NULL; 

Simply disable $cascade:

只需禁用$cascade：

$model->deleteAll(
    [
        'ProductVouchers.id' => 16, 
        'ProductVouchers.client_id' => null
    ],
    false # <- single delete statement please
);

回答by sjagr

Simply reading the docs, the deletemethod only takes the identifier in the first parameteras an integer by design.

只需阅读文档，该delete方法仅将第一个参数中的标识符设计为整数。

It looks like the Cake team may have accepted array formats but only use the array values to supply into the delete query (thus ignoring the keys and using the identifier every time.)

看起来 Cake 团队可能已经接受了数组格式，但只使用数组值提供给删除查询（因此每次都忽略键并使用标识符。）

I believe you are looking for the deleteAllmethod, which will accept the custom conditions that you've supplied in your question:

我相信你正在寻找的deleteAll方法，这将接受你在你的问题提供了自定义条件：

$this->ProductVouchers->deleteAll(['ProductVouchers.id'=>$id,'ProductVouchers.client_id'=>"NULL"]);

回答by Mike Miller

The issue is related to a $belongsTovariable in the model ProductVouchersmodel. While this is set I cant apply a criteria to the foreign key without setting the $cascadeargument to false like this:

该问题与$belongsTo模型ProductVouchers模型中的变量有关。虽然设置了这个，但我不能在不将$cascade参数设置为 false 的情况下将标准应用于外键，如下所示：

 $this->ProductVouchers->deleteAll([
   'ProductVouchers.id'=>$id,
   'ProductVouchers.client_id IS NULL'
 ],false);

This produces working SQL

这会产生工作 SQL

DELETE `ProductVouchers` FROM `product_vouchers` AS `ProductVouchers`             
LEFT JOIN `onepulse_dev`.`clients` AS `Client`
ON (`ProductVouchers`.`client_id` = `Client`.`clients_id`)  
WHERE `ProductVouchers`.`id` = 25 AND `ProductVouchers`.`client_id` IS NULL

EDIT:

编辑：

Follow @AD7six's comprehensive answer to remove the unnecessary join.

按照@AD7six 的综合答案删除不必要的连接。

 $this->ProductVouchers->deleteAll([
   'ProductVouchers.id'=>$id,
   'ProductVouchers.client_id'=>NULL
 ],false);

回答by tuffz

Try follow:

尝试遵循：

$id = 100;
$conditions = array(
    'OR' => array(
        'ProductVouchers.id' => $id,
        'ProductVouchers.client_id IS NULL'
    )
);

$this->ProductVouchers->deleteAll($conditions);

Should to create following SQL Query:

应该创建以下 SQL 查询：

SELECT `ProductVouchers`.`id` FROM `cakephp2`.`ProductVouchers` AS `product_vouchers` WHERE ((`ProductVouchers`.`id` = 100) OR (`ProductVouchers`.`client_id` IS NULL)) GROUP BY `ProductVouchers`.`id`
DELETE `ProductVouchers` FROM `cakephp2`.`ProductVouchers` AS `product_vouchers` WHERE `ProductVouchers`.`id` IN (1, 8, 100)

The "IN (1, 8, 100)" is the result of the previous called SELECT statement.

“IN (1, 8, 100)”是前面调用的 SELECT 语句的结果。

回答by Love Kumar

Try Thisfor delete all with multiple conditions

试试这个删除所有具有多个条件的

$this->Model->deleteAll(array('Model.field_name'=>'field_value'));