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?

Solution 1

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?

Solution 2

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();

Comments

[Geetika]

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))"`

[DerStoffel]

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.

[Artamiel]

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.

[DonCallisto]

@Artamiel: yes, of course, I just copy/paste and didn't noticed ;)

[Geetika]

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

[DonCallisto]

@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

[Faisal]

Nice answer. Perfect solutions. Thank you :)

[Lashae]

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();.