I need to create a custom query, without using the doctrine relationship on Symfony 5.4.
I have two entities : Orders and Carrier. This two entities / tables are joined through the column 'id_carrier' (it's the same for both entities). When i execute my query, i get this error:
So i thought to customize the query without passing the association. Can i do this?
I tried also to write the SQL query directly ('SELECT o.* FROM en_orders o INNER JOIN en_carrier c ON c.id_carrier = o.id_carrier), but it not work.
Orders.php
<?php
namespace App\Entity;
use App\Repository\OrdersRepository;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Entity(repositoryClass=OrdersRepository::class)
*/
class Orders
{
/**
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
* @ORM\Column(type="integer")
*/
private $id_order;
/**
* @ORM\Column(type="string", length=255, nullable=true)
*/
private $module;
/**
* @ORM\Column(type="string", length=255)
*/
private $payment;
/**
* @ORM\Column(type="string", length=255, nullable=true)
*/
private $ps_reference;
/**
* @ORM\Column(type="string", length=255, nullable=true)
*/
private $en_reference;
/**
* @ORM\Column(type="string", length=255, nullable=true)
*/
private $details;
/**
* @ORM\Column(type="integer", nullable=true)
*/
private $invoice;
/**
* @ORM\Column(type="decimal", precision=20, scale=6, nullable=true)
*/
private $total_paid_tax_inclu;
/**
* @ORM\Column(type="decimal", precision=20, scale=6)
*/
private $total_products_wt;
/**
* @ORM\Column(type="decimal", precision=10, scale=0, nullable=true)
*/
private $total_shipping;
/**
* @ORM\Column(type="decimal", precision=10, scale=0, nullable=true)
*/
private $carrier_tax_rate;
/**
* @ORM\Column(type="text", nullable=true)
*/
private $order_note;
/**
* @ORM\Column(type="datetime", nullable=true)
*/
private $date;
/**
* @ORM\OneToMany(targetEntity=App\Entity\Carrier::class, mappedBy="id_carrier")
*/
private $id_carrier;
/**
* @ORM\OneToMany(targetEntity=OrderDetail::class, mappedBy="id_order")
*/
private $orderDetails;
/**
* @ORM\Column(type="integer", nullable=true)
* @ORM\ManyToMany(targetEntity="CartProduct", mappedBy="id_cart")
*/
private $id_cart;
/**
* @ORM\OneToOne(targetEntity=State::class, cascade={"persist", "remove"})
* @ORM\JoinColumn(name="id_state", referencedColumnName="id_state")
*/
private $id_state;
/**
* @ORM\OneToOne(targetEntity=Address::class, cascade={"persist", "remove"})
* @ORM\JoinColumn(name="id_address_delivery", referencedColumnName="id_address")
*/
private $id_address_delivery;
/**
* @ORM\OneToOne(targetEntity=Address::class, cascade={"persist", "remove"})
* @ORM\JoinColumn(name="id_address_invoice", referencedColumnName="id_address")
*/
private $id_address_invoice;
public function __construct()
{
$this->orderDetails = new ArrayCollection();
}
public function getIdOrder(): ?int
{
return $this->id_order;
}
public function getModule(): ?string
{
return $this->module;
}
public function setModule(?string $module): self
{
$this->module = $module;
return $this;
}
public function getPayment(): ?string
{
return $this->payment;
}
public function setPayment(string $payment): self
{
$this->payment = $payment;
return $this;
}
public function getPsReference(): ?string
{
return $this->ps_reference;
}
public function setPsReference(?string $ps_reference): self
{
$this->ps_reference = $ps_reference;
return $this;
}
public function getEnReference(): ?string
{
return $this->en_reference;
}
public function setEnReference(?string $en_reference): self
{
$this->en_reference = $en_reference;
return $this;
}
public function getDetails(): ?string
{
return $this->details;
}
public function setDetails(?string $details): self
{
$this->details = $details;
return $this;
}
public function getInvoice(): ?int
{
return $this->invoice;
}
public function setInvoice(?int $invoice): self
{
$this->invoice = $invoice;
return $this;
}
public function getTotalPaidTaxInclu(): ?string
{
return $this->total_paid_tax_inclu;
}
public function setTotalPaidTaxInclu(?string $total_paid_tax_inclu): self
{
$this->total_paid_tax_inclu = $total_paid_tax_inclu;
return $this;
}
public function getTotalProductsWt(): ?string
{
return $this->total_products_wt;
}
public function setTotalProductsWt(string $total_products_wt): self
{
$this->total_products_wt = $total_products_wt;
return $this;
}
public function getTotalShipping(): ?string
{
return $this->total_shipping;
}
public function setTotalShipping(?string $total_shipping): self
{
$this->total_shipping = $total_shipping;
return $this;
}
public function getCarrierTaxRate(): ?string
{
return $this->carrier_tax_rate;
}
public function setCarrierTaxRate(?string $carrier_tax_rate): self
{
$this->carrier_tax_rate = $carrier_tax_rate;
return $this;
}
public function getOrderNote(): ?string
{
return $this->order_note;
}
public function setOrderNote(?string $order_note): self
{
$this->order_note = $order_note;
return $this;
}
public function getDate(): ?\DateTimeInterface
{
return $this->date;
}
public function setDate(?\DateTimeInterface $date): self
{
$this->date = $date;
return $this;
}
public function getIdCarrier(): ?OrderCarrier
{
return $this->id_carrier;
}
public function setIdCarrier(?OrderCarrier $id_carrier): self
{
// unset the owning side of the relation if necessary
if ($id_carrier === null && $this->id_carrier !== null) {
$this->id_carrier->setIdOrder(null);
}
// set the owning side of the relation if necessary
if ($id_carrier !== null && $id_carrier->getIdOrder() !== $this) {
$id_carrier->setIdOrder($this);
}
$this->id_carrier = $id_carrier;
return $this;
}
/**
* @return Collection<int, OrderDetail>
*/
public function getOrderDetails(): Collection
{
return $this->orderDetails;
}
public function addOrderDetail(OrderDetail $orderDetail): self
{
if (!$this->orderDetails->contains($orderDetail)) {
$this->orderDetails[] = $orderDetail;
$orderDetail->setIdOrder($this);
}
return $this;
}
public function removeOrderDetail(OrderDetail $orderDetail): self
{
if ($this->orderDetails->removeElement($orderDetail)) {
// set the owning side to null (unless already changed)
if ($orderDetail->getIdOrder() === $this) {
$orderDetail->setIdOrder(null);
}
}
return $this;
}
public function getIdCart(): ?int
{
return $this->id_cart;
}
public function setIdCart(?int $id_cart): self
{
$this->id_cart = $id_cart;
return $this;
}
public function getIdState(): ?State
{
return $this->id_state;
}
public function setIdState(?State $id_state): self
{
$this->id_state = $id_state;
return $this;
}
public function getIdAddreddDelivery(): ?Address
{
return $this->id_address_delivery;
}
public function setIdAddreddDelivery(?Address $id_address_delivery): self
{
$this->id_address_delivery = $id_address_delivery;
return $this;
}
public function getIdAddressInvoice(): ?Address
{
return $this->id_address_invoice;
}
public function setIdAddressInvoice(?Address $id_address_invoice): self
{
$this->id_address_invoice = $id_address_invoice;
return $this;
}
}
Carrier.php
<?php
namespace App\Entity;
use App\Repository\CarrierRepository;
use Doctrine\ORM\Mapping as ORM;
/**
* @ORM\Table(name="en_carrier")
* @ORM\Entity(repositoryClass=CarrierRepository::class)
*/
class Carrier
{
/**
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
* @ORM\Column(type="integer")
* @ORM\ManyToOne(targetEntity=App\Entity\Orders::class, inversedBy="id_carrier")
* @ORM\JoinColumn(name="id_carrier", referencedColumnName="id_carrier")
*/
private $id_carrier;
/**
* @ORM\Column(type="string", length=255, nullable=true)
*/
private $name;
public function getIdCarrier(): ?int
{
return $this->id_carrier;
}
public function getName(): ?string
{
return $this->name;
}
public function setName(?string $name): self
{
$this->name = $name;
return $this;
}
}
OrdersRepository.php
/**
* @return Orders[] Returns an array of Orders objects
*/
public function getFilteredOrders(): array
{
return $this->createQueryBuilder('o')
->select('o')
->innerJoin('o.en_carrier','c','WITH','o.id_carrier = c.id_carrier')
->getQuery()
->getResult()
;
}
