Details
Description
I have several entities:
*Client
*Payment
*PaymentDeposit
*PaymentWithdraw
PaymentDeposit and PaymentWithdraw extends from entity Payment
Here is the code
/** * @ORM\Entity * @ORM\Table(name="client") */ class Client { /** * @var int $id * @ORM\Id * @ORM\Column(type="integer") * @ORM\GeneratedValue(strategy="AUTO") */ protected $id; /** * @var ArrayCollection * @ORM\OneToMany(targetEntity="PaymentDeposit", mappedBy="clientTo") */ private $depositPayments; /** * @var ArrayCollection * @ORM\OneToMany(targetEntity="PaymentWithdraw", mappedBy="clientFrom") */ private $withdrawPayments; } /** * @ORM\Entity * @ORM\Table(name="payment") * @ORM\InheritanceType("JOINED") * @ORM\DiscriminatorColumn(name="type", type="smallint") * @ORM\DiscriminatorMap({ * 1 = "PaymentDeposit", * 2 = "PaymentWithdraw"}) */ class Transaction { /** * @var int $id * @ORM\Id * @ORM\Column(type="integer") * @ORM\GeneratedValue(strategy="AUTO") */ protected $id; /** * @var float * @ORM\Column(name="amount", type="decimal", precision=15, scale=5) */ protected $amount; /** * @var Client * @ORM\ManyToOne(targetEntity="Client", inversedBy="withdrawPayments") * @ORM\JoinColumn(name="client_from", referencedColumnName="id", onDelete="cascade") */ protected $clientFrom; /** * @var Client * @ORM\ManyToOne(targetEntity="Client",inversedBy="depositPayments") * @ORM\JoinColumn(name="client_to", referencedColumnName="id", onDelete="cascade") */ protected $clientTo; /** * @var int * @ORM\Column(type="smallint") */ protected $status; } /** * @ORM\Entity * @ORM\Table(name="payment_deposit") */ class PaymentDeposit extends Transaction { // Additional info } /** * @ORM\Entity * @ORM\Table(name="payment_deposit") */ class PaymentWithdraw extends Transaction { // Additional info }
When I try to get result with such query I got sql error
$queryBuilder = $this->getEntityManager()->createQueryBuilder();
$queryBuilder->select('c')
->from('Client', 'c')
->leftJoin('c.depositPayments', 'd')
->leftJoin('c.withdrawPayments', 'w')
->andWhere($queryBuilder->expr()->eq('d.status', ':status'))
->andWhere($queryBuilder->expr()->eq('w.status', ':status'));
sql error:
PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't2_.client_to' in 'on clause' (uncaught exception) at project/vendor/doctrine-dbal/lib/Doctrine/DBAL/Connection.php line 628
native sql query(generated)
SELECT c1_.id, -- other columns FROM client c1_ LEFT JOIN payment_deposit t2_ ON c1_.id = t2_.client_to LEFT JOIN payment t3_ ON t2_.id = t3_.id LEFT JOIN payment_withdraw t4_ ON c1_.id = t4_.client_from LEFT JOIN payment t5_ ON t4_.id = t5_.id WHERE t3_.transaction_status_id = 1 AND t5_.transaction_status_id = 1
Problem is here
LEFT JOIN payment_deposit t2_ ON c1_.id = t2_.client_to LEFT JOIN payment_withdraw t4_ ON c1_.id = t4_.client_from
extended Entities PaymentDeposit, PaymentWithdraw (tables) doesnt have field clientFrom & clientTo they extend them from entity Payment
P.S. I removed unnecessary fields from Entities to make code example more clearly