I'm expecting, it should update deleted_at field when I've executed DELETE
query. And it should add deleted_at is null
condition to end of SELECT query. But its really delete rows with a silly condition. What is wrong?
$query = $this->entityManager->createQueryBuilder()
->delete("AppBundle:ReportRow", "r")
->where("r.date <= :date")
->andWhere("r.balance is null")
->andWhere("r.name = :name")
->setParameters(array("date" => $date->format("Y-m-d"),
"name" => $user->getName()))
->getQuery();
$output->writeln($query->getSQL());
Here is the output:
DELETE FROM report_row
WHERE (date <= ? AND balance_id IS NULL AND name = ?)
AND (report_row.deleted_at IS NULL) // really ?? its delete query dude!
Here is my configuration
doctrine:
...
orm:
...
filters:
softdeleteable:
class: Gedmo\SoftDeleteable\Filter\SoftDeleteableFilter
enabled: true
stof_doctrine_extensions:
orm:
default:
softdeleteable: true
This issue has been submitted to the developers and is currently pending since 2014 at https://github.com/Atlantic18/DoctrineExtensions/issues/1125
Explanation:
When you use the QueryBuilder
to create a DELETE
statement, you bypass the onFlush
event listener which is used to prevent physical deletion of the entry, as you are no longer working within the entity manager when using $em->remove($entity)
, but instead are manually issuing an SQL statement.
The result is that the softdeletable filter is no longer managing the intent of your query but is still aware of the deleted_at
metadata.
To resolve the issue you must use a Query Hint with the SoftDeleteableWalker
as explained in the documentation
$query = $this->entityManager->createQueryBuilder()
->delete("AppBundle:ReportRow", "r")
->where("r.date <= :date")
->andWhere("r.balance is null")
->andWhere("r.name = :name")
->setParameters(array("date" => $date->format("Y-m-d"),
"name" => $user->getName()))
->getQuery();
$query->setHint(\Doctrine\ORM\Query::HINT_CUSTOM_OUTPUT_WALKER,
\Gedmo\SoftDeleteable\Query\TreeWalker\SoftDeleteableWalker::class);
$output->writeln($query->getSQL());
Results:
UPDATE report_row
SET deleted_at = '2017-10-03 15:06:48'
WHERE (date <= ? AND balance_id IS NULL AND name = ?)