dtkjthe4025 2018-02-08 11:36 采纳率: 0%
浏览 85
已采纳

DQL Query Doctrine将连接元素OrderBy子句放在另一个元素的连接条件的子查询中

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.

  • 写回答

1条回答 默认 最新

  • duanna3634 2018-02-14 14:09
    关注

    Well, I feel kind of noob now.. but I solved this issue.. I simply reorganized my QueryBuilder methods order to place the Offers juncture before the other one, so that it generates the first orderBy clause in the right place, instead of adding it to the one created in the subquery.

    It works.

    Still thinking it is kind of weird, as QueryBuilder methods are supposed to work no matter the order they are called..

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 Python语言实验
  • ¥15 SAP HANA SQL 增加合计行
  • ¥20 用C#语言解决一个英文打字练习器,有偿
  • ¥15 srs-sip外部服务 webrtc支持H265格式
  • ¥15 在使用abaqus软件中,继承到assembly里的surfaces怎么使用python批量调动
  • ¥15 大一C语言期末考试,求帮助🙏🙏
  • ¥15 ch340驱动未分配COM
  • ¥15 Converting circular structure to JSON
  • ¥30 Hyper-v虚拟机相关问题,求解答。
  • ¥15 TSM320F2808PZA芯片 Bootloader