dqwh2717
2017-04-17 10:52
浏览 162
已采纳

Symfony Doctrine - 查询之后的OrderBy

I'd like to order only the result of the previous main query.

I want the top 10 most viewed articles then ordered it by "addedAt date" DESC.

I'm aware of functions such as orderBy or even addOrderBy but it's not what I need here or I'm not implementing it correctly.

Example to get the top 2 on 5 articles :

  • name : a || views : 100 || addedAt : 10/04/2017
  • name : b || views : 10 || addedAt : 17/04/2017
  • name : c || views : 50 || addedAt : 15/04/2017
  • name : d || views : 25 || addedAt : 12/04/2017
  • name : e || views : 200 || addedAt : 05/04/2017

I extract first the most views :

  1. Article e
  2. Article a

And then orderedIt by addedAt date DESC (most recent) :

  1. Article a
  2. Article e

My query :

public function findMostViewed($limit=10, $asArray=false)
{
        $qb = $this->createQueryBuilder('n')
            ->orderBy('n.nbViews', 'DESC')
            //->addOrderBy('n.addedAt', 'DESC')
            ->getQuery()
            ->setMaxResults($limit);

        return ($asArray) ? $qb->getResult(Query::HYDRATE_ARRAY) : $qb->getResult();
}

If I knew how to do a preSelectQuery it would have probably solve the problem ...

I thought to an SQL query :

SELECT ...
FROM ...
WHERE id IN(SELECT id FROM News ORDER BY nbViews DESC LIMIT 10)
ORDER BY addedAt DESC

图片转代码服务由CSDN问答提供 功能建议

我只想订购上一个主查询的结果。 < p>我希望前10篇观看最多的文章然后通过“addedAt date”DESC进行排序。

我知道 orderBy 等功能,甚至< strong> addOrderBy ,但这不是我在这里所需要的,或者我没有正确实现它。

获得5篇文章前2的示例:

  • 名称:a || 观点: 100 || 已添加: 10 / 04/2017
  • 名称:b || 观点:10 || 已添加时间:17/04/2017
  • 名称:c || 观点:50 || 已添加时间:15/04/2017
  • 名称:d || 观点:25 || 已添加时间:12/04/2017
  • 名称:e || 观点: 200 || addedAt: 05 / 04/2017

    我首先提取最多的观点:

      \ n
    1. 文章e
    2. 文章a

      然后orderAt by addedAt date DESC(最新):

      1. 文章a
      2. 文章e

        我的查询: \ n

         公共函数findMostViewed($ limit = 10,$ asArray = false)
         {
         $ qb = $ this-&gt; createQueryBuilder('n')
          - &gt; orderBy('  n.nbViews','DESC')
         //->addOrderBy('n.addedAt','DESC')
          - &gt; getQuery()
          - &gt; setMaxResults($ limit); 
         \  n return($ asArray)?  $ qb-&gt; getResult(Query :: HYDRATE_ARRAY):$ qb-&gt; getResult(); 
        } 
           
         
         

        如果我知道如何进行preSelectQuery 它可能已经解决了这个问题...

        我想到了一个SQL查询:

          SELECT ... 
        FROM ..  。
        WHEREID IN IN(SELECT id FROM News ORDER BY nbViews DESC LIMIT 10)
        ORDER BY addedAt DESC 
           
         
  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • duanpei4455 2017-04-18 19:12
    已采纳

    Based on Veve's answer, I changed the WHERE IN condition and managed to make it work.

    public function findMostViewed($limit=10, $asArray=false)
    {
            $subqb = $this->createQueryBuilder('nn')
                ->orderBy('nn.nbViews', 'DESC')
                ->getQuery()
                ->setMaxResults($limit);
    
            $qb = $this->createQueryBuilder('n')
                ->where('n.id IN (:ids)')
                ->setParameter('ids', array_values($subqb->getResult()))
                ->orderBy('n.addedAt', 'DESC')
                ->getQuery();
    
            return ($asArray) ? $qb->getResult(Query::HYDRATE_ARRAY) : $qb->getResult();
    }
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • dongshi2588 2017-04-18 07:26

    Maybe if you use first order by 'n.addedAt', and then 'n.nbViews', like this:

    $qb = $this->createQueryBuilder('n')
                ->orderBy('n.addedAt', 'DESC')
                ->addOrderBy('n.nbViews', 'DESC')
                ->getQuery()
                ->setMaxResults($limit);
    
    评论
    解决 无用
    打赏 举报
  • duanlachu7344 2017-04-18 13:22

    You can make a subquery and use it in your main query:

    public function findMostViewed($limit=10, $asArray=false)
    {
            $subqb = $this->createQueryBuilder('nn')
                ->orderBy('nn.nbViews', 'DESC')
                ->getQuery()
                ->setMaxResults($limit);
    
            $qb = $this->createQueryBuilder('n')
                ->where($qb->expr()->in('n.id', $subqb->getDQL())
                ->orderBy('n.addedAt', 'DESC')
                ->getQuery();
    
            return ($asArray) ? $qb->getResult(Query::HYDRATE_ARRAY) : $qb->getResult();
    }
    

    Note the use of nn instead of n in the subquery.

    评论
    解决 无用
    打赏 举报
  • douke9379 2018-08-28 13:24

    It is work too. If you add ->setFirstResult( '1' ) to the code's Israel Rodriguez Sanchez works

    $qb = $this->createQueryBuilder('n')
    ->orderBy('n.addedAt', 'DESC')
    ->addOrderBy('n.nbViews', 'DESC')
    ->getQuery()
    ->setFirstResult( '1' )
    ->setMaxResults($limit);
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题