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 = 'FARMÁCIA ALGUMA COISA' OR    d.razao_social = 'FARMÁCIA 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

Entity Chamados:

/**
 * @ORM\Entity(repositoryClass="App\Repository\ChamadosRepository")
 */
class Chamados
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string")
     */
    private $titulo;

    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Clients", inversedBy="chamados")
     * @ORM\JoinColumn(nullable=false)
     */
    private $id_client;

    /**
     * @ORM\Column(type="integer", options={"default" = 0})
     */
    private $status;

    /**
     * @ORM\Column(type="text", nullable=true)
     */
    private $description;

    /**
     * @ORM\ManyToMany(targetEntity="App\Entity\User", inversedBy="chamados")
     */
    private $user;

    /**
     * @ORM\Column(type="datetime")
     */
    private $open_date;

    /**
     * @ORM\Column(type="datetime", nullable=true)
     */
    private $update_date;

    /**
     * @ORM\Column(type="datetime", nullable=true)
     */
    private $close_date;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Tramite", mappedBy="chamado")
     */
    private $tramites;

    public function __construct()
    {
        $this->user = new ArrayCollection();
        $this->tramites = new ArrayCollection();
    }

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getIdClient(): ?Clients
    {
        return $this->id_client;
    }

    public function setIdClient(?Clients $id_client): self
    {
        $this->id_client = $id_client;

        return $this;
    }

    public function getStatus(): ?int
    {
        return $this->status;
    }

    public function setStatus(int $status): self
    {
        $this->status = $status;

        return $this;
    }

    public function getDescription(): ?string
    {
        return $this->description;
    }

    public function setDescription(?string $description): self
    {
        $this->description = $description;

        return $this;
    }

    /**
     * @return Collection|User[]
     */
    public function getUser(): Collection
    {
        return $this->user;
    }

    public function addUser(User $user): self
    {
        if (!$this->user->contains($user)) {
            $this->user[] = $user;
        }

        return $this;
    }

    public function removeUser(User $user): self
    {
        if ($this->user->contains($user)) {
            $this->user->removeElement($user);
        }

        return $this;
    }

    public function getOpenDate(): ?DateTimeInterface
    {
        return $this->open_date;
    }

    public function setOpenDate(DateTimeInterface $open_date): self
    {
        $this->open_date = $open_date;

        return $this;
    }

    public function getUpdateDate(): ?DateTimeInterface
    {
        return $this->update_date;
    }

    public function setUpdateDate(?DateTimeInterface $update_date): self
    {
        $this->update_date = $update_date;

        return $this;
    }

    public function getCloseDate(): ?DateTimeInterface
    {
        return $this->close_date;
    }

    public function setCloseDate(?DateTimeInterface $close_date): self
    {
        $this->close_date = $close_date;

        return $this;
    }

    /**
     * @return mixed
     */
    public function getTitulo()
    {
        return $this->titulo;
    }

    /**
     * @param mixed $titulo
     * @return Chamados
     */
    public function setTitulo($titulo)
    {
        $this->titulo = $titulo;
        return $this;
    }

    /**
     * @return Collection|Tramite[]
     */
    public function getTramites(): Collection
    {
        return $this->tramites;
    }

    public function addTramite(Tramite $tramite): self
    {
        if (!$this->tramites->contains($tramite)) {
            $this->tramites[] = $tramite;
            $tramite->setChamado($this);
        }

        return $this;
    }

    public function removeTramite(Tramite $tramite): self
    {
        if ($this->tramites->contains($tramite)) {
            $this->tramites->removeElement($tramite);
            // set the owning side to null (unless already changed)
            if ($tramite->getChamado() === $this) {
                $tramite->setChamado(null);
            }
        }

        return $this;
    }
}

Entity Clients:

/**
 * @ORM\Entity(repositoryClass="App\Repository\ClientsRepository")
 */
class Clients
{
    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $name_fantasy;

    /**
     * @ORM\Column(type="string", length=255)
     */
    private $razao_social;

    /**
     * @ORM\Column(type="string", length=128, nullable=true)
     */
    private $contact_email;

    /**
     * @ORM\Column(type="string", length=16, nullable=true)
     */
    private $contact_telephone;

    /**
     * @ORM\OneToMany(targetEntity="App\Entity\Chamados", mappedBy="id_client")
     */
    private $chamados;

    /**
     * @ORM\Column(type="boolean", options={"default"="true"})
     */
    private $active;

    public function __construct()
    {
        $this->chamados = new ArrayCollection();
    }

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getNameFantasy(): ?string
    {
        return $this->name_fantasy;
    }

    public function setNameFantasy(string $name_fantasy): self
    {
        $this->name_fantasy = mb_convert_case($name_fantasy, MB_CASE_UPPER, 'UTF-8');

        return $this;
    }

    public function getRazaoSocial(): ?string
    {
        return $this->razao_social;
    }

    public function setRazaoSocial(string $razao_social): self
    {
        $this->razao_social = mb_convert_case($razao_social, MB_CASE_UPPER, 'UTF-8');

        return $this;
    }

    public function getContactEmail(): ?string
    {
        return $this->contact_email;
    }

    public function setContactEmail(?string $contact_email): self
    {
        $this->contact_email = $contact_email;

        return $this;
    }

    public function getContactTelephone(): ?string
    {
        return $this->contact_telephone;
    }

    public function setContactTelephone(?string $contact_telephone): self
    {
        $this->contact_telephone = $contact_telephone;

        return $this;
    }

    /**
     * @return Collection|Chamados[]
     */
    public function getChamados(): Collection
    {
        return $this->chamados;
    }

    public function addChamado(Chamados $chamado): self
    {
        if (!$this->chamados->contains($chamado)) {
            $this->chamados[] = $chamado;
            $chamado->setIdClient($this);
        }

        return $this;
    }

    public function removeChamado(Chamados $chamado): self
    {
        if ($this->chamados->contains($chamado)) {
            $this->chamados->removeElement($chamado);
            // set the owning side to null (unless already changed)
            if ($chamado->getIdClient() === $this) {
                $chamado->setIdClient(null);
            }
        }

        return $this;
    }

    /**
     * @return mixed
     */
    public function getActive()
    {
        return $this->active;
    }

    /**
     * @param mixed $active
     * @return Clients
     */
    public function setActive($active)
    {
        $this->active = $active;
        return $this;
    }
}

Edit 2: The error is gone and the query is being built properly, but the result is empty.

Controller:

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('c.id_client', 'd');
        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);
    }

Generated query:

[2019-08-21 17:22:31] doctrine.DEBUG: SELECT c0_.id AS id_0, c1_.name_fantasy AS name_fantasy_1, c0_.status AS status_2, c0_.titulo AS titulo_3, c0_.description AS description_4 FROM chamados c0_ INNER JOIN clients c1_ ON c0_.id_client_id = c1_.id WHERE (c1_.name_fantasy = ? OR c1_.razao_social = ?) ["PADARIA","PADARIA"] []

PgAdmin3: If I put both values inside the query and replace " by ', it works, otherwise it returns column "PADARIA" does not exist.

Inside AJAX request: it returns an empty JSON.

  • 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 Aug 19 at 14:53
  • Add the involved entities as well or at least an snippet of them – ReynierPM Aug 20 at 18:10
  • My bad. There it is. – Mateus Aug 20 at 19:16
0

For some reason you have a semicolon where it should not be, try the following function, not saying it will work though:

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('c.Clients', '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);
}

Notice how I have removed the semicolon from the following pieces of code:

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'))));
};

Also notice how I have change the JOIN:

->join('c.Clients', 'd', Join::ON, 'c.id_client = d.id');

UPDATE:

Change this:

if(strtoupper(trim($request->get('client')))) {
    $client = strtoupper(trim($request->get('client')));

    $em->andWhere('d.name_fantasy=:client')
       ->orWhere('d.razao_social=:client')
       ->setParameter('client', $client);
}

Or:

if (strtoupper(trim($request->get('client')))) {
    $em->andWhere('d.name_fantasy=:client OR d.razao_social=:client')
       ->setParameter('client', strtoupper(trim($request->get('client'))));
};

More info here.

  • Hi! I managed to make Doctrine build the query I wanted thanks to your help, but it now returns empty. If I run the same query on PgAdmin3 replacing " by ', it works. I updated my question. – Mateus Aug 21 at 20:50
  • Hi! I was doing some tests and I discovered that it doesn't work because I shouldn't separate the queryBuilder into several $em. If I place everything in one $em->something->somethingelse, it works. The problem is that this way I have to use all fields and filtering something and I don't want that. Could this be a bug? Ps: The raw and DQL queries of both ways are identical. – Mateus Aug 23 at 18:57
  • 1
    Hey, I changed $em to $qb and stored it inside another var: $result = $qb->getQuery()->getArrayResult();. Now it works. :D Thank you very much for your help! – Mateus Aug 26 at 12:50

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.