deleteAll with conditions and a delete limit

Question

CakePHP Version: 4.2.6

Xampp:
Apache/2.4.51 (Win64) OpenSSL/1.1.1l PHP/8.0.11
Server version: 10.4.21-MariaDB - mariadb.org binary distribution
PHP version: 8.0.11

Introduction

I'm trying to construct a deleteAll query with the order by and limit clauses.

SQL

When the following sql is run in the xampp sql tab window it deletes just one row in the correct order which is what I wanted it to do.

DELETE FROM `report_dashs` ORDER BY `id` ASC LIMIT 1;

API Documentation

I'd like to replicate this using deleteAll with conditions. I've referenced this in the api documentation:

deleteAll(mixed $conditions)

Parameters mixed $conditions Conditions to be used, accepts anything Query::where() can take.

Returns int Returns the number of affected rows.

What I've tried

On the basis of this I've constructed the following query:

$this->ReportDashs->deleteAll(
    [
        'user_id' => 1001
    ])
    ->order(['id' => 'DESC'])
    ->limit(1);

This throws an error:

Call to a member function order() on int

This line ->order(['id' => 'DESC'])

and deleteAll returns an int.

Summary

I've tried numerous configurations but every attempt has either thrown a syntax error or deleted all the rows. The debug kit sql log is always missing the order and limit clause.

Question

How do you construct the deleteAll query and respect the order by and limit clause.

Note

If the full stack trace will help let me know and I'll post it.

Thanks, Zenzs.

---

Solutions

Attempt with eager loading and a custom finder:

// Controller:

$query = $this->ReportDashs->find('clearSuperuserData', [
    'contain' => ['Users'],
    'client_id' => 1234
])
->delete()
->epilog('ORDER BY id ASC LIMIT 1')
->execute();

// Finder:

public function findClearSuperuserData(Query $query, array $options): object
{
    $query
        ->where(['Users.client_id' => $options['client_id']]);
    return $query;
}

But the Users table was never respected in the where clause.

Attempt without eager loading and a custom finder:

$query = $this->ReportDashs
    ->find()
    ->where(['user_id' => 1003])
    ->delete()
    ->epilog('ORDER BY id ASC LIMIT 1')
    ->execute();

And this deletes the correct rows in order with the limit applied.

Answer 1

Check the API docs for Table::deleteAll(), it's a convenience method for deleting all records that match the given conditions, and it will return the number of affected rows.

For more complex queries you can generally use the query builder, however currently ORDER and LIMIT are not supported for UPDATE and DELETE queries. The last attempt to implement it in a cross-DBMS fashion was unfortunately abandoned.

Currently you can either use completely raw SQL, or in your specific case for MariaDB/MySQL, you could use the query builder's epilog to append the ORDER and LIMIT clauses:

$query = $this->ReportDashs
    ->query()
    ->delete()
    ->epilog('ORDER BY id ASC LIMIT 1');

or

$query = $this->ReportDashs
    ->query()
    ->delete()
    ->epilog(
        $this->ReportDashs
            ->getConnection()
            ->newQuery()
            ->orderDesc('id')
            ->limit(1)
    );

Both would generate

DELETE FROM report_dashs ORDER BY id DESC LIMIT 1

the latter with support for automatic identifier quoting.

Alternatively, if you're feeling adventurous, you could create an extended query compiler that enables compiling the required clauses, \Cake\Database\QueryCompiler::$_deleteParts is what would need to be modified to include the order and limit part.