Я пытаюсь использовать 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, он не возвращает ошибок, но результат становится пустым. Кроме того, я не могу запустить его SQL напрямую в PGAdmin.
Что я делаю не так?
ИЗМЕНИТЬ:
Это мой необработанный 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.
