What is the best way to generate a delete request in Doctrine?

Question

symfony

You need to remove all conditional values ​​from the associated table.

• News - contains news
  
• NewsBad - contains temporary reports about the unavailability of links to these news
  

The tables are not built correctly, according to business logic, one news item can have only one report, but the NewsBad table allows you to create multiple reports for the same news item.
The SQL code looks like this:

DELETE     news_bad
FROM       smi_ad_news_bad  AS news_bad
INNER JOIN smi_ad_campaigns_news AS news ON news_bad.news_id = news.id
WHERE      news.status != "active"
AND        news_bad.dt < (now() - interval 1 month);

I'm trying to create a delete request:

$this->em
          ->getRepository(NewsBad::class)
          ->createQueryBuilder('nb')
          ->delete(NewsBad::class, 'nb')
          ->join(News::class, 'n')
          ->andWhere('n.status != :status')
          ->andWhere('n.dt < :date')
          ->setParameters([
              'date'   => $date,
              'status' => "active",
          ])
          ->getQuery()
          ->getSql()

But here, there is an error in the connection .... After a long search, it turns out that the doctrine does not know how to join on a delete request and they offer to use several queries
. I tried to write the same query through DQL:

$qb = $this->em->createQuery('
          DELETE     newsBad
          FROM       AppBundle\Entity\NewsBad newsBad
          INNER JOIN AppBundle\Entity\News news ON newsBad.news_id = news.id
          WHERE      news.status != "active"
          AND        news_bad.dt < (now() - interval 1 month);
      ');

      $qb->execute();

Nothing has changed, so it's still impossible to do?
It turned out to be done only through a nested query:

return $this->createQueryBuilder('nb')
          ->delete()
          ->where($qb->expr()->in('nb.news', $this->getEntityManager()
              ->createQueryBuilder()
              ->select('n.id')
              ->from(News::class, 'n')
              ->where($qb->expr()->neq('n.status', "'active'"))
              ->getDQL()))
          ->andWhere('nb.dt < :older')
          ->setParameter('older', new \DateTime('-1 month'))
          ->getQuery()
          ->execute();

This type of recording makes me very sad, maybe you can record it better than the last and only working version?

Answer 1

The doctrine does not know how to make a connection inside a DELETE. this is not supported by all DB vendors. For example, SQLite can't do this: https://www.sqlite.org/lang_delete.html
In my opinion, the best thing you can do is rewrite the query in DQL for readability. Are you sure you need constructs like: $qb->expr()->neq?