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

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();
}

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);

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.

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);