时间戳

首页 > 解决方案 > 语法错误 - 第 0 行，第 122 列：错误：预期的字符串结尾，得到 'ON'

sql - 语法错误 - 第 0 行，第 122 列：错误：预期的字符串结尾，得到 'ON'

问题描述

我试图在我的查询生成器中使用ON但它返回[Syntax Error] line 0, col 122: Error: Expected end of string, got 'ON'.

代码：

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

如果我返回它的 DQL，我会得到：

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)

如果我将 SQL 直接运行到 PGAdmin 中，它就可以工作。如果我将 ON 更改为 WITH，它不会返回错误，但结果为空。另外，我不能直接在 PGAdmin 中运行它的 SQL。

我究竟做错了什么？

编辑：

这是我的原始 SQL（考虑到我正在使用所有字段）：

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

实体查马多斯：

/**
 * @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;
    }
}

实体客户：

/**
 * @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;
    }
}

编辑 2： 错误消失了，查询正在正确构建，但结果为空。

控制器：

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

生成的查询：

[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：如果我将两个值都放在查询中并将"替换为'，它会起作用，否则它会返回column "PADARIA" does not exist。

在 AJAX 请求中：它返回一个空的 JSON。

标签： sqlpostgresqldoctrine-ormdoctrinesymfony4

解决方案

出于某种原因，您在不应该有分号的地方有一个分号，请尝试以下功能，但不要说它会起作用：

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

请注意我是如何从以下代码中删除分号的：

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

另请注意我如何更改JOIN：

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

更新：

改变这个：

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

或者：

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

更多信息在这里。

推荐阅读