0

I'm trying to use ON in my Query Builder but it returns [Syntax Error] line 0, col 122: Error: Expected end of string, got 'ON'.

Code:

public function filterChamados(Request $request)
    {
        $em = $this->getDoctrine()->getManager()->getRepository(Chamados::class)
            ->createQueryBuilder('c')->select('c.id, d.name_fantasy, c.status, c.titulo, c.description')
            ->join(Clients::class, 'd',Join::ON,'c.id_client = d.id');
        if ($request->request->get('status')) {
            $em->where('c.status = :status')
               ->setParameter('status', $request->request->get('status'));
        };
        if (strtoupper(trim($request->get('client')))) {
            $em->andWhere('(d.name_fantasy=:client OR d.razao_social=:client)')
               ->setParameter('client', strtoupper(trim($request->get('client'))));
        };
        if ($request->get('open_date')) {
            $em->andWhere('c.open_date >=:open_date')
                ->setParameter('open_date', $request->get('open_date'));
        }
        if ($request->get('close_date')) {
        $em->andWhere('c.close_date <=:close_date')
            ->setParameter('close_date', $request->get('close_date'));
        }
        $em->getQuery()->getArrayResult();

        return new JsonResponse($em);
    }

If I return its DQL, I get:

SELECT c.id, d.name_fantasy, c.status, c.titulo, c.description FROM App\Entity\Chamados c INNER JOIN App\Entity\Clients d ON c.id_client = d.id WHERE (d.name_fantasy=:client OR d.razao_social=:client)

If I run the SQL directly into PGAdmin, it works. If I change ON to WITH, it does not return errors, but the result comes empty. Plus, I can't run its SQL directly into PGAdmin.

What am I doing wrong?

EDIT:

This is my raw SQL (considering I'm using all fields):

SELECT
    c.id, d.name_fantasy, c. status, c.titulo, c.description
FROM
    chamados c
JOIN
    clients d
ON
    c.id_client_id = d.id
WHERE
    c.status = 2 --:status
AND
    (d.name_fantasy = 'FARMCIA ALGUMA COISA' OR    d.razao_social = 'FARMCIA ALGUMA COISA') -- :client
AND
    c.open_date >= '2019-03-03 10:00' --:open_date
AND
    c.close_date <= '2019-09-03 18:00' --:close_date
  • can you add the raw SQL you want to transform into Doctrine DQL? – ReynierPM Aug 15 at 20:22
  • Hi! I updated my question. – Mateus yesterday
  • Add the involved entities as well or at least an snippet of them – ReynierPM 52 mins ago

Your Answer

By clicking Post Your Answer, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.