DELETE query not working in DQL doctrine Symfony2

Question

I am to delete a row based on ID, and i used this query to do the task:

 $em = $this->getDoctrine()->getManager();
    $query = $em->createQuery('DELETE buss from StreetBumbApiBundle:BussOwner buss WHERE buss.id = :bussId')
        ->setParameter("bussId", $bussId);
    $list = $query->getResult();

but i am getting this error:

{ "code": 500, "message": "[Semantical Error] line 0, col 7 near 'buss from StreetBumbApiBundle:BussOwner': Error: Class 'buss' is not defined." }

What is wrong i am doing?

DerStoffel · Accepted Answer · 2015-07-02T13:33:29.010

18

You could solve this easily by using the QueryBuilder:

$em = $this->getDoctrine()->getManager();
$qb = $em->createQueryBuilder();
$query = $qb->delete('StreetBumbApiBundle:BussOwner', 'buss')
            ->where('buss.id = :bussId')
            ->setParameter('bussId', "bussId")
            ->getQuery();

$query->execute();

BTW: If you replace getQuery with getDQL you can see how this translates into DQL (or getSQL)

What essentially is wrong in your method is the from in your Delete statement as delete in DQL looks like this:

$em = $this->getDoctrine()->getManager();
$query = $em->createQuery(
              'DELETE StreetBumbApiBundle:BussOwner buss 
               WHERE buss.id = :bussId')
            ->setParameter("bussId", $bussId);

$query->execute();

Also, I am not sure why you use $query->getResult(). What would be the expected result of a delete? $query->execute(); does the job.

Apart from that, if you don't do complex delete statements, and in your case you are deleting an Entity querying with an Id.

Why not:

$em = $this->getDoctrine()->getManager();
$em->remove($entity);

And therefore pass the Entity, not just the Id to your delete function?

edited Jul 02 '15 at 13:33

answered Jul 02 '15 at 06:46

DerStoffel

2,553
2
15
25

i guess your solution is working fine, as its trying to delete that row but because of a foreign key constraint it is giving me this error: `"An exception occurred while executing 'DELETE FROM BussOwner WHERE id = ?' with params [\"2\"]:\n\nSQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`withinseventytwo`.`offerList`, CONSTRAINT `FK_85A42F448195E83B` FOREIGN KEY (`bussId`) REFERENCES `BussOwner` (`id`))"` – Geetika Jul 02 '15 at 07:23
Depends on what you want to do. Do you want to delete the referenced object as well? Then cascade delete (see entity reference definitions in doctrine). Or you cannot. Then you can for example softdelete, with something like a `deletedAt` field which is null by default and has a date if deleted. Then your delete would become an update query for the row to delete. – DerStoffel Jul 02 '15 at 07:26
Nice answer. Perfect solutions. Thank you :) – Faisal Jul 10 '19 at 09:31
Just a tiny but an important reminder. If you take the entity removal option and use `$em->remove($entity);` then do not forget to flush the entity manager to execute with `$em->flush();`. – Lashae Feb 12 '21 at 07:08

DonCallisto · Answer 2 · 2015-07-02T08:47:43.253

3

Alternative solution: your DQL is not a good one, try with this

 $em = $this->getDoctrine()->getManager();
 $query = $em->createQuery(
     'DELETE 
        StreetBumbApiBundle:BussOwner buss 
      WHERE 
        buss.id = :bussId'
      )
      ->setParameter("bussId", $bussId);

 $result = $query->execute();

edited Jul 02 '15 at 08:47

answered Jul 02 '15 at 08:23

DonCallisto

29,419
9
72
100

Just to add a note - since you're not expecting a result of any kind, better chain method `->execute()` after `->setParameter()` to run the query, instead of `getResult()`. Other than that, the query looks just fine. – Artamiel Jul 02 '15 at 08:44
@Artamiel: yes, of course, I just copy/paste and didn't noticed ;) – DonCallisto Jul 02 '15 at 08:44
{ "code": 500, "message": "[Semantical Error] line 0, col 7 near 'buss from StreetBumbApiBundle:BussOwner': Error: Class 'buss' is not defined." } – Geetika Jul 02 '15 at 08:49
@Geetika: Yes, I know his solution is a good one, but mine (alternative) is good too. Don't know why your getting same error, but I checked with my code (didn't do before answer) and I can see that I use this synthax somewhere and it works – DonCallisto Jul 02 '15 at 08:53

DELETE query not working in DQL doctrine Symfony2

2 Answers2