Cannot Delete User - QueryException: DELETE WHERE u.id = :id

Question

I am working in Symfony - When I am performing an action to delete a user I am receiving the following error messages:

[Semantical Error] line 0, col 7 near 'WHERE u.id =': Error: Class 'WHERE' is not defined.
500 Internal Server Error - QueryException
1 linked Exception:

QueryException » 

1/2 QueryException: DELETE WHERE u.id = :id
2/2 QueryException: [Semantical Error] line 0, col 7 near 'WHERE u.id =': Error: Class 'WHERE' is not defined.

Admin Controller

 /**
 * @Route("/admin/user/delete/{id}", name="admin_user_delete")
 * @Template
 */
public function deleteAction($id)
{
    $dispatcher = $this->container->get('event_dispatcher');

    $this->get('users')->delete($id);

    // create the user event and dispatch it
    $event = new UserEvent($id);
    $dispatcher->dispatch(UserEvents::USER_DELETED, $event);

    $this->get('session')->getFlashBag()->add('notice-success', 'User deleted successfully.');

    return $this->redirect($this->generateUrl('admin_user_list'));
}

User.php Service

public function delete($id)
{
    $this->repository->delete($id);
}

User Repository

public function delete($id)
{
    $qb = $this->getEntityManager()->createQueryBuilder('u');
    $qb->delete()
        ->andWhere($qb->expr()->eq('u.id', ':id'))
        ->setParameter(':id', $id)
        ->getQuery()
        ->getResult();
}

You don't use the : character in the setParameter() call. docs.doctrine-project.org/projects/doctrine-orm/en/latest/… — Jared Farrish
– Jared Farrish, Commented May 19, 2017 at 22:34
The : is actually irrelevant. DQL works with objects not ids. Take a look at the accepted answer here: stackoverflow.com/questions/15555042/… You will need to create a reference to the user object. I think $em->remove($user) followed by a flush would be a cleaner approach. — Cerad
– Cerad, Commented May 19, 2017 at 23:28
Thanks @Cerad - this looks to be right direction. This is a pretty new environment for me - any chance you could guide me in writing it out? I've been playing around but am flunking out. — RKlina
– RKlina, Commented May 19, 2017 at 23:52

JacobW · Accepted Answer · 2017-05-20 00:18:23Z

1

Method to delete a user with the query builder

public function delete($id)
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    return $qb->delete('Bundle:User', 'u')
        ->where('u.id = :id'))
        ->setParameter('id', $id)
        ->getQuery()
        ->getResult();
}

How to delete a user using the built in ORM

public function delete($id) {
    // get access to the entity manager
    $em = $this->getEntityManager();
    // get the user from the repository by id
    // previous version, slightly slower as it executes a query
    // $user = $em->getRepository('Bundle:User')->find($id);

    // Cerads method I just found out about myself, even better version!
    // this doesn't execute a query but gets a reference to the user object 
    // and sets the id
    $user = $em->getReference('Bundle:User', $id);

    // use built in methods to delete the user
    $em->remove($user);
    // update the database with the change
    $em->flush();
}

edited May 20, 2017 at 0:18

answered May 20, 2017 at 0:04

JacobW

8765 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Cerad Over a year ago

In your second approach, replace find with getReference($id). No need to load the entire user object just to delete it. And have you actually tried the first approach? Not positive you have it quite right. Pretty sure you will need something like Bundle:User.

JacobW Over a year ago

You're right on both counts, I just put user not thinking the reading user might not know to access it through the bundle. I actually didn't know about the getReference() call though, that's incredibly useful. Thank you!

RKlina Over a year ago

@JacobW when using your Method, I am receiving Unknown Entity namespace alias 'Bundle'. 500 Internal Server Error - ORMException

Cerad Over a year ago

@RKlina - I think you at the point where you know just enough to get in trouble. Pause for a bit and go back read through the Doctrine chapter: symfony.com/doc/current/doctrine.html Things should start to make a bit more sense the second time round.

RKlina Over a year ago

Indeed. Will revisit and jump back in - very much appreciate the input!

|

Jake Litwicki · Accepted Answer · 2017-05-19 22:36:36Z

0

As Jared Farrish said above, you don't need the colon in the setParameter call.

Should be:

->setParameter('id', $id)

answered May 19, 2017 at 22:36

Jake Litwicki

2322 silver badges5 bronze badges

2 Comments

RKlina Over a year ago

Thank you - reading over posted docs.doctrine... Same errors do remain after fix.

Jared Farrish Over a year ago

As I said in a comment, not an answer. ;)

Collectives™ on Stack Overflow

Cannot Delete User - QueryException: DELETE WHERE u.id = :id

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related