CODEWITHSUNDEEP
mysqldoctrine-ormsymfony
Syerad
Syerad

Reputation: 113

Delete entry with lowest ID

I just want to delete the row with the lowest ID.

I'm trying this:

$query = 'DELETE FROM PATH\TO\ENTITY ORDER BY id ASC LIMIT 1';
$query = $this->entityManager->createQuery($query);
$query->execute();

And getting this error:

[Syntax Error] line 0, col 53: Error: Expected end of string, got 'BY'

Maybe I'm using the wrong approach. Any suggestions how to delete the entry with the lowest id in one database call?

Upvotes: 1

Views: 139

Answers (3)

Rawburner
Rawburner

Reputation: 1395

$query = 'DELETE FROM table ORDER BY id ASC LIMIT 1';
$stmt = $this->entityManager->getConnection()->prepare($query);
$stmt->execute();

You cannot delete or update from entityManager. First you have to select/find the entity from Repository and then remove it. My suggestion works for raw SQL query instead.

Upvotes: 1

Kwido
Kwido

Reputation: 1382

Define an alias for your entity as you use DQL. See: Doctrine - By DQL

$query = $this->entityManager->createQuery('SELECT e FROM MyEntity e ORDER BY e.id ASC');
$query->setMaxResults(1); // LIMITS 1
$entities = $query->getResult();

if (count($entities) > 0) {
    $query = $this->entityManager->createQuery('DELETE FROM MyEntity e WHERE e.id = :identifier');
    $query->setParameter("identifier", $entities[0]->getId());
    $query->execute();
}

Replace entityAlias with the first letter of your entity classname, which is the most common practice with Doctrine.

// Edit - as @Timurib stated DQL doesn't know the LIMIT. Should've used setMaxResults.

// Edit2 - As ORDER BY is not supported by the DELETE statement, but only the WHERE clause. We're now using another query to return the identifier before deleting. See DQL - Statements

Upvotes: 1

Timurib
Timurib

Reputation: 2768

As Kwido said, you miss the entity alias. But the query will still not be able to execute.

First, DQL does not support LIMIT expression. It is MySQL-specific feature, is not an ANSI SQL. Other platform drivers have an own implementations of this behavior, all of them provided by common interface: setFirstResult()/setMaxResult() of Query object.

Second, DQL does not support DELETE with ORDER BY clause (see language EBNF). It is non-standard feature too, but can not be implemented for other drivers, so Doctrine does not allow it.

If you need to execute this query, you will have to use a native SQL.

Upvotes: 2

Related Questions