How to use limit and order by in a delete query in Zend2

Question

I have following sql query and I need to write it in zend2.

DELETE FROM test_table WHERE user_id = 2 ORDER BY id ASC LIMIT 10

I tried with this:

$sql = new Sql($this->dbAdapter);
$delete = $sql->delete();
$delete->from('test_table');
$delete->where(array('user_id' => 4));

$delete->order('Id ASC');
$delete->limit(10);

$stmt = $sql->prepareStatementForSqlObject($delete);
$results = $stmt->execute();

but it does not work when I add those two lines:

$delete->order('Id ASC');
$delete->limit(10);

Answer 1

Guess you are using SQL adapter. But, if you have a look on the SQL ZF delete() implementation, you will see that there is no order() or limit() methods. So you can't call them...

Answer 2

You can try the following:

$sql = new Sql($this->dbAdapter);
$delete = $sql->delete();
$delete->from('test_table');
$delete->where(array('user_id' => 4));
// get sql string
$deleteQuery = $sql->getSqlStringForSqlObject($delete);
// append the order by and limit
$deleteQuery .= " ORDER BY id ASC LIMIT 10";
// execute query
$result = $this->dbAdapter->query($deleteQuery, 'execute');

OR

$deleteQuery = "DELETE FROM test_table WHERE user_id = 2 ORDER BY id ASC LIMIT 10";
$result = $this->dbAdapter->query($deleteQuery, 'execute');