Doctrine Query Builder delete all entries from Table

Question

i'm having some trouble deleting all records found by id using the query builder I tried like the bellow code but i'm always getting this error:

 [Semantical Error] line 0, col 53 near 'b.id = :surv': Error: 'b' is not defined. 

The Method:

public function deleteUsers($surveyId) {
    $qb = $this->getEntityManager()->createQueryBuilder();

    return $qb
        ->delete()
        ->from(BaseUser::class, 'a')
        ->leftJoin('a.survey', 'b')
        ->where('b.id = :survey')
        ->setParameter('survey', $surveyId)
        ->getQuery()
        ->execute()
        ;
}

Solution 1

You simply cannot use joins in a delete statement with Doctrine. One chance could be to get the ids which should be deleted of your joined results first and then do a simple 'delete where ids in'. This will work.

Please see this question/answer as well: Doctrine QueryBuilder delete with joins

Solution 2

public function deleteAllEmployees(){
        $query = $this->createQueryBuilder('e')
                 ->delete()
                 ->getQuery()
                 ->execute();
        return $query;
}

Now, inside Controller call this function with the entity manager object like em->deleteAllEmployees(). Don't forget to flush().

Comments

[L.S]

that's like how i wrote too and i got this error: [Syntax Error] line 0, col 49: Error: Expected end of string, got ','

[MounirOnGithub]

Could you tell me what is the value of $surveyId ?

[L.S]

int type with a value of '1'

[L.S]

that was like i did in the end :-D found out i cannot use joins with delete and you need to fetch and do a foreach :-D... took me some time to find that link last night and didn't post an answer how i did it.

[LBA]

you don't have to do a foreach necessarily, you can get all ids of your entities with a first select. and then do a delete where your ids (ids which you just found by select). while @Massimiliano's answer is correct, using delete from where is a lot faster than deleting them one by one.

[Tebe]

it's going to be extra slow

[Ondrej Slinták]

Using ORM doesn't mean you have to use unoptimized queries, especially when your website is going to have some serious traffic. Use the right tool for the job, which in this case is QueryBuilder or DQL.