Delete multiple Rows with One Datawriter statement

[Marcus]

Do you also use one Datawriter statement to delete multiple rows? I have a table:

id, fruit, country
1, apple, usa
2, apple, italy
3, banana, brazil
4, orange, spain

$dw->delete("apple") is actually what I want. Is it possible with xf 1.2?

http://framework.zend.com/manual/1.12/en/zend.db.table.html

Deleting Rows from a Table
You can delete rows from a database table using the delete() method. This method takes one argument, which is an SQL expression that is used in a WHERE clause, as criteria for the rows to delete.

Example #18 Example of deleting rows from a Table

1. $table = new Bugs();
2. 
   
3. $where = $table->getAdapter()->quoteInto('bug_id = ?', 1235);
4. 
   
5. $table->delete($where);

Since the table delete() method proxies to the database adapter delete() method, the argument can also be an array of SQL expressions. The expressions are combined as Boolean terms using an AND operator.

Note: The values and identifiers in the SQL expression are not quoted for you. If you have values or identifiers that require quoting, you are responsible for doing this. Use the quote(), quoteInto(), and quoteIdentifier() methods of the database adapter.

XenForo deletes its attachments as an example also in its Model:

    $db = $this->_getDb();
     $db->delete('xf_attachment',
       'attachment_id IN (' . $db->quote(array_keys($attachments)) . ')'
     );

 

[Chris D]

DataWriters have pretty much a static update condition. This is defined in the _getUpdateCondition() function.

Usually this takes the form of:

return 'some_id = ' . $this->_db->quote($this->getExisting('some_id'));

So effectively every update type procedure that the DataWriter performs - even deletions - will use the clause:

'WHERE some_id = X'

With that in mind, no. You cannot delete multiple rows with one DataWriter statement.

You would either need to loop:

$dw = XenForo_DataWriter::create('YourAddOn_DataWriter_Writer');

foreach ($array AS $id => $value)
{
    $dw->setExistingData($id);

    $dw->delete();
}

Or just write a vanilla query:

$db = XenForo_Application::getDb();
$db->delete('table_name', 'some_id IN (' . $db->quote($arrayOfSomeIds) . ');

 

[Marcus]

Thanks for the detailled explanation. In my case I could use WHERE fruit = "apple". Can't I use this - to stay true with my example

  protected function _getUpdateCondition($tableName)
   {
     return 'fruit = ' . $this->_db->quote($this->getExisting('fruit'));
   }

I did not understand the quote() part. I updated my first post just before your answer, in XenForo they use also the Model to delete some stuff.

 

[Chris D]

That might work.

Only, if you're ever going to update the country column using the DataWriter, it will set both rows to the same country.

The quote() function escapes a string or array appropriately for use in a SQL query.

The delete() function runs a simple delete query. The first parameter is the table. The second parameter is the WHERE clause.

So:

$db->delete('fruit_table', 'fruit = ' . $db->quote('apple'));

Translates to:

DELETE FROM fruit_table WHERE fruit = apple

Generally, if you're not needing to run any postDelete functions in the DataWriter, you can just use simple SQL deletes.