Doctrine 2 - ORM

Bug in generation sql when using join on extended entity

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Invalid
  • Affects Version/s: 2.2
  • Fix Version/s: None
  • Component/s: DQL, ORM
  • Security Level: All
  • Labels:
    None
  • Environment:
    doctrine-2.2
    commit adec530c131e419cd16622de79c3eae13dbbfff4
    PHP 5.3.6

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

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Alisher Alimov added a comment -

Any idea?

Show
Alisher Alimov added a comment - Any idea?
Hide
Permalink
Fabio B. Silva added a comment -

code format

Show
Fabio B. Silva added a comment - code format
Fabio B. Silva made changes -
Field Original Value New Value
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 		I have several entities:
*Client
*Payment
*PaymentDeposit
*PaymentWithdraw

PaymentDeposit and PaymentWithdraw extends from entity Payment

Here is the code

{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
}
{code}

When I try to get result with such query I got sql error

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

{code}

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)

{code}
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
{code}

Problem is here
{code}
LEFT JOIN payment_deposit t2_ ON c1_.id = t2_.client_to
LEFT JOIN payment_withdraw t4_ ON c1_.id = t4_.client_from
{code}

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
Hide
Permalink
Benjamin Eberlei added a comment -

The problem is probably "@ORM\OneToMany(targetEntity="PaymentDeposit", mappedBy="clientTo")". You should specify "Transaction" here, since the "clientTo" field is on Transaction, not on PaymentDeposit.

I will look into why this happens and if we can fix this, but judging from the description this could be a tricky issue.

Show
Benjamin Eberlei added a comment - The problem is probably "@ORM\OneToMany(targetEntity="PaymentDeposit", mappedBy="clientTo")". You should specify "Transaction" here, since the "clientTo" field is on Transaction, not on PaymentDeposit. I will look into why this happens and if we can fix this, but judging from the description this could be a tricky issue.
Hide
Permalink
Benjamin Eberlei added a comment -

Join in this case wuold have to be

SELECT c1_.id,  -- other columns
FROM client c1_ 
LEFT JOIN payment t3_ ON c1_.id = t3_.id 
LEFT JOIN payment_deposit t2_ ON t3_.id = t2_.client_to 
LEFT JOIN payment t5_ ON c1_.id = t5_.id 
LEFT JOIN payment_withdraw t4_ ON t5_.id = t4_.client_from 
WHERE  t3_.transaction_status_id = 1 AND t5_.transaction_status_id = 1

So Doctrine would need to reorder the joins if TargetEntity#MappedBy is inherited.

Show
Benjamin Eberlei added a comment - Join in this case wuold have to be SELECT c1_.id, -- other columns FROM client c1_ LEFT JOIN payment t3_ ON c1_.id = t3_.id LEFT JOIN payment_deposit t2_ ON t3_.id = t2_.client_to LEFT JOIN payment t5_ ON c1_.id = t5_.id LEFT JOIN payment_withdraw t4_ ON t5_.id = t4_.client_from WHERE t3_.transaction_status_id = 1 AND t5_.transaction_status_id = 1 So Doctrine would need to reorder the joins if TargetEntity#MappedBy is inherited.
Hide
Permalink
Benjamin Eberlei added a comment -

This is not supported, you have to use as targetEntity, the class where the mappedBy association is in.

Show
Benjamin Eberlei added a comment - This is not supported, you have to use as targetEntity, the class where the mappedBy association is in.
Benjamin Eberlei made changes -
Status Open [ 1 ] Resolved [ 5 ]
Resolution Invalid [ 6 ]
Benjamin Eberlei made changes -
Workflow jira [ 13657 ] jira-feedback [ 15331 ]
Benjamin Eberlei made changes -
Workflow jira-feedback [ 15331 ] jira-feedback2 [ 17195 ]
Benjamin Eberlei made changes -
Workflow jira-feedback2 [ 17195 ] jira-feedback3 [ 19450 ]
This list may be incomplete, as errors occurred whilst retrieving source from linked applications:
  • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-1795, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

People

  • Assignee:
    Benjamin Eberlei
    Reporter:
    Alisher Alimov
Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: