Error: Expected Doctrine\ORM\Query\Lexer::T_WITH, got 'ON'

20
votes

I have written the following code for fetching data from database: 

function getnotificationAction()
{
    $session = $this->getRequest()->getSession();
    $userId = $session->get('userid');

    $entitymanager = $this->getDoctrine()->getEntityManager();
    $notification = $entitymanager->getRepository('IGCNotificationBundle:Notifications');
    $userNotification = $entitymanager->getRepository('IGCNotificationBundle:Usernotifications');
    $query = $entitymanager
                 ->createQuery("SELECT n.notificationid, n.title,n.notificationmessage, u.creationdate, u.notificationid, u.messagestatus From IGCNotificationBundle:Notifications AS n JOIN IGCNotificationBundle:Usernotifications AS u ON u.notificationid = n.notificationid WHERE u.userId = :userId ORDER BY n.creationdate DESC")->setParameter('userId', userId);

    $notifications = $query->getResult();

    return $this->render('IGCNotificationBundle:Default:notification.html.twig', array('notifications' => $notifications));
} }

But it is giving:

[Syntax Error] line 0, col 203: Error: Expected Doctrine\ORM\Query\Lexer::T_WITH, got 'ON' 500 Internal Server Error - QueryException 1 linked Exception: QueryException »

2
phpsymfonydoctrine-orm
may be remove AS IGCNotificationBundle:Notifications n JOIN IGCNotificationBundle:Usernotifications u - zzlalani
Hi thanks for reply i have tried this but facing the same error - Vishal Mokal
As far as I see it you're trying to execute SQL... you need to rewrite it to DQL (Docrine Query Language). Please update your question with entities' structure... - Jovan Perovic
hi @jperovic i dont know how to convert this in DQL - Vishal Mokal
can any one convert this sql query in dql query - Vishal Mokal

2 Answers

43
votes
[Syntax Error] line 0, col 203: Error: Expected Doctrine\ORM\Query\Lexer::T_WITH, got 'ON' 500 Internal Server Error - QueryException 1 linked Exception: QueryException »

I think you should replace your keyword 'ON' with a 'WITH' .

extract from doc :

Joins between arbitrary entities are now possible in DQL by using the syntax FROM Foo f JOIN Bar b WITH f.id = b.id.

1
votes

Please use the bellow code its work for you

function getnotificationAction() {
    $session = $this->getRequest()->getSession();
    $userId = $session->get('userid');
    $entitymanager = $this->getDoctrine()->getEntityManager();
    $notification = $entitymanager->getRepository('IGCNotificationBundle:Notifications');
    $userNotification = $entitymanager->getRepository('IGCNotificationBundle:Usernotifications');
    $query = $entitymanager->createQuery("SELECT n.notificationid, n.title,n.notificationmessage, u.creationdate, u.notificationid, u.messagestatus From IGCNotificationBundle:Notifications AS n JOIN IGCNotificationBundle:Usernotifications AS u WITH u.notificationid = n.notificationid WHERE u.userId = :userId ORDER BY n.creationdate DESC")->setParameter('userId', userId);
    $notifications = $query->getResult();
    return $this->render('IGCNotificationBundle:Default:notification.html.twig', array('notifications' => $notifications));
}