How to write LEFT JOIN in DELETE QUERY?

Question

Will you give me an example of a delete request using a left join using Doctrine?

Answer 1

It's impossible. See that: http://trac.doctrine-project.org/ticket/2142

You should use the subquery in the where clause: http://www.doctrine-project.org/documentation/manual/1_2/en/dql-doctrine-query-language:subqueries

Try something like this:

$q = Doctrine_Query::create() ->delete('TableB b') ->where('b.id NOT IN (SELECT b.id FROM TableB b \ INNER JOIN b.TableA a WHERE b.date > NOW() AND a.channel_id = 10)') ->execute(); 

Answer 2

No need to write a query in simple sql, you can use QueryBuilder for this:

 $entityManager = $this->getEntityManager(); $queryBuilder = $entityManager->createQueryBuilder(); $subQueryCityDataSources = $queryBuilder->getEntityManager()->createQueryBuilder() ->select('cds') ->from(CityDataSource::class, 'cds') ->where('cds.dataSource = :dataSource') ; $queryBuilder->delete($entityClass, 'd'); $queryBuilder->where($queryBuilder->expr()->in('d.cityDataSource', $subQueryCityDataSources->getDQL())); $queryBuilder->setParameter('dataSource', $dataSource); $query = $queryBuilder->getQuery(); $query->execute(); 

This generates this request:

 DELETE AppBundle\Entity\ConcreteData\News d WHERE d.cityDataSource IN ( SELECT cds FROM AppBundle\Entity\CityDataSource cds WHERE cds.dataSource = :dataSource ) 

Important note: you must add all parameters to the external request.