douwei8911 2018-01-30 10:54
浏览 33
已采纳

在Symfony 2中订购分页结果

I have a question about the order in the pagination in Symfony 2.7.

Before we used pagination we used the PHP function usort to sort some things. But my question now is how could we implement the usort in the doctrine query with the same order like the usort. Which needs to be working with the Paginator. Since when we use now the query (here under) we don't get the proper results.

usort function:

        usort($result, function ($a, $b) {
        $aBegin = $a->getStartDate() ?: $a->getCreatedDate();
        $bBegin = $b->getStartDate() ?: $b->getCreatedDate();
        if ($aBegin < $bBegin) {
            return -1;
        }
        if ($aBegin == $bBegin) {
            return 0;
        }
        if ($aBegin > $bBegin) {
            return 1;
        }

        return 0;
    });

How could we implemented the usort in the following query:

    $build = $this->createQueryBuilder('building');
    $build
        ->addSelect('users', 'furniture')
        ->join('building.users', 'users')
        ->leftJoin('building.furniture', 'furniture')
        ->where('building.id = :id')
        ->setParameter('id', $id)
        ->orderBy('building.getStartDate', 'ASC')
        ->addOrderBy('building.getCreatedDate', 'DESC');

    $paginator = new Paginator($build->getQuery(), $fetchJoinCollection = true);
    $result = $paginator->getQuery()
        ->setFirstResult($offset)
        ->setMaxResults($limit)
        ->getResult();

Thanks!

Doctrine orm: 2.2.3, Symfony version: 2.7

  • 写回答

1条回答 默认 最新

  • dqqt31923 2018-01-30 12:16
    关注

    To add such a condition, you can use a CASE expression in your select clause. You can write something like CASE WHEN b.startDate IS NULL THEN b.createdDate ELSE b.startDate END to have the behaviour described in your usort function.

    That being said, you can't simply add this to your order by clause. You will need to select this value, give it an alias and then add an order by based on the newly selected value. Since you probably don't want to get a mixed result (where your entities would be mixed with scalar values), you can use the HIDDEN keyword to remove the computed field from the result set.

    All put together, it could look like this:

    // $qb your query builder with all your other parameters
    $qb->addSelect('CASE 
        WHEN building.startDate IS NULL 
            THEN building.createdDate 
            ELSE building.startDate
        END 
        AS HIDDEN beginDate');
    $qb->orderBy('beginDate', 'DESC');
    

    Note that while this works, you might encounter performance issues if you have a lot of entries in your table as the whole table is very likely to be scanned entirely for this query to be executed.

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

报告相同问题?

悬赏问题

  • ¥20 基于MSP430f5529的MPU6050驱动,求出欧拉角
  • ¥20 Java-Oj-桌布的计算
  • ¥15 powerbuilder中的datawindow数据整合到新的DataWindow
  • ¥20 有人知道这种图怎么画吗?
  • ¥15 pyqt6如何引用qrc文件加载里面的的资源
  • ¥15 安卓JNI项目使用lua上的问题
  • ¥20 RL+GNN解决人员排班问题时梯度消失
  • ¥60 要数控稳压电源测试数据
  • ¥15 能帮我写下这个编程吗
  • ¥15 ikuai客户端l2tp协议链接报终止15信号和无法将p.p.p6转换为我的l2tp线路