Here is my situation:
I gotta an entity User, with several joined entitites collections:
class User
{
private $id;
/**
* @var UserData
* @ORM\OneToMany(targetEntity="UserData", mappedBy="user")
*/
private $userDatas;
/**
* @var ServiceAccess
* @ORM\OneToMany(targetEntity="ServiceAccess", mappedBy="user")
*/
private $serviceAccesses;
...
}
ServiceAccess has also a OneToMany junction on Offers, with an OrderBy instruction to Doctrine ORM on its level field:
class ServiceAccess
{
private $id;
/**
* Offers
* @ORM\OneToMany(targetEntity="Offer",
* mappedBy="access",
* cascade={"persist","remove","refresh"})
* @ORM\OrderBy({"level" = "DESC"})
*/
private $offers;
/**
* @ORM\ManyToOne(
* targetEntity="User"
* )
* @ORM\JoinColumn(name="user_id", referencedColumnName="id", nullable=false)
*/
private $user;
...
}
My Offer class:
class Offer
{
private $id;
/**
* The user access
*
* @var ServiceAccess
* @ORM\ManyToOne(targetEntity="Fitnext\ServiceAccessBundle\Domain\Entity\UserServiceAccess", inversedBy="offers")
* @ORM\JoinColumn(name="access_id", referencedColumnName="id", nullable=false)
*/
private $access;
/**
* @ORM\Column(name="level", type="integer", nullable=false, options={"default"=0})
*/
private $level;
...
}
I am implementing the DQL query to fetch one User with several dependencies, including its UserDatas, its ServiceAccesses, and the ServiceAccesses.offers.
The main trick here is the condition for the User.userDatas juncture: UserData has a name and a createdAt field, and can contain several rows having the same name. My goal is to query the the latest row for each defined name group. For that, I use a subquery (which was sent to me here on Stackoverflow :) ), which works perfectly.
Also, I have several more classical junctures, in which one on User.serviceAccesses and then one on ServiceAccesses.offers, which causes me the issue, when combined with the UserData subquery...
Here is my QueryBuilder:
// First I define the subquery for the UserDatas juncture condition
$queryDatas = $this->_em->createQueryBuilder()
->select('a')
->from('UserData', 'a')
->leftJoin( 'UserData', 'b', 'WITH', 'a.name = b.name AND a.createdAt < b.createdAt' )
->where( 'b.createdAt IS NULL' )
->andWhere('a.name IN (:names)')
->andWhere('a.user = u')
->orderBy( 'a.createdAt', 'DESC' )
->getDQL();
// Notice the orderBy clause here, which is part of the trick in order to get the latest registration of each named data group, and which is also part of the issue...
// And here is the main QueryBuilder
$this->createQueryBuilder('u')
->where('u.id = :id')
->setParameter('id', $id)
// UserDatas
->leftJoin('u.userDatas', 'd', 'WITH', 'd IN ('. $queryDatas .')')
->addSelect('d')
->setParameter('names', ['height', 'weight'])
// ServiceAccess
->leftJoin('u.serviceAccesses', 'svacc', 'WITH', 'svacc.activated = 1 AND svacc.type = :type' )
->setParameter('type','default')
->addSelect('svacc')
// ServiceAccessOffers
->leftJoin('svacc.offers', 'offers', 'WITH', 'offers.activated = 1' )
->addSelect('offers')
...
This returns me a Doctrine\DBAL\Exception\InvalidFieldNameException:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'u7_.level' in 'order clause'
And Here is the generated SQL code part causing the issue:
FROM users u0_
LEFT JOIN user_data e1_ ON u0_.id = e1_.user_id
AND (e1_.id IN (
SELECT e8_.id
FROM user_data e8_
LEFT JOIN user_data e9_ ON (e8_.name = e9_.name AND e8_.created_at < e9_.created_at)
WHERE e9_.created_at IS NULL AND e8_.name IN ('height', 'weight')
AND e8_.user_id = u0_.id
ORDER BY e8_.created_at DESC, u7_.level DESC
)
)
LEFT JOIN service_access u6_ ON u0_.id = u6_.user_id AND (u6_.activated = 1 AND u6_.type = 'default')
LEFT JOIN offer u7_ ON u6_.id = u7_.access_id AND (u7_.activated = 1)
Doctrine takes the OrderBy clause defined in Access.offers attribute (orderBy level DESC), and adds it to the subquery orderBy!! Which doesn't know about the offers table and its level field of course, as these are not in the subquery scope!
It works as if Doctrine adds the orderBy to the only/first OrderBy it finds in the Query, which in this case can't work..
IMO this is a Doctrine bug.
I'm currently under "symfony/symfony": "^3.4", "doctrine/orm": "2.5.12",.
Any idea someone? Am I the first to encounter this issue?
Thank you very much.
