CODEWITHSUNDEEP
symfonydoctrine-ormquery-builder
Ben Stinton
Ben Stinton

Reputation: 411

Symfony2 - Query Builder multiple orderBy

I have the following code

public function queryAssociationsSorted($id){
    $qb = $this->createQueryBuilder('a')
            ->leftJoin('a.category', 'c')
            ->where('a.job = :id')
            ->setParameter('id', $id)
            //->addOrderBy('c.rank', 'DESC')
            //->addOrderBy('a.updated', 'DESC')    
            ->add('orderBy','c.rank DESC THEN a.updated DESC')       
            ;

    return $query = $qb->getQuery();
}

and this or the commented out options both only sort by a.updated. I have looked at other posts on this subject and can't find a solution. Can anyone suggest where I am going wrong?

Upvotes: 3

Views: 12617

Answers (3)

Ben Stinton
Ben Stinton

Reputation: 411

Over a year later and I've stumbled across the correct answer in this answer by @red777

This will help you:

$adapter = new DoctrineORMAdapter($queryBuilder, false); Had the same problem this morning. By default Pagerfanta is treating your query as one with joins. Setting second argument to false makes it use simple query handling.

In Kunstmaan Bundle, in AdminListConfiguration class, you have to overide function that is creating Pagerfanta, if you want to sort simple entity.

The syntax in the query had always been correct, but the error crept in in the DoctrineOrmAdapter. Adding false as the second parameter sorted the problem

Upvotes: 0

Albert Casadessús
Albert Casadessús

Reputation: 294

If I understand correctly, and this is the result yu want:

id - rank - updated

xx - 4 - 2014-01-01

xx - 3 - 2014-01-02

xx - 3 - 2014-01-01

This actually works:

$qb = $this->createQueryBuilder('a')
            ->leftJoin('a.category', 'c')
            ->where('a.job = :id')
            ->setParameter('id', $id)
            ->addOrderBy('c.rank', 'DESC')
            ->addOrderBy('a.updated', 'DESC')    
            ;

return $query = $qb->getQuery();

Upvotes: 7

thesearentthedroids
thesearentthedroids

Reputation: 620

I'm not sure how doctrine works with multiple sort by..

why dont you try to use dql or even raw sql query?

http://docs.doctrine-project.org/en/2.1/reference/dql-doctrine-query-language.html

http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html

Edit:

something like that will work I think:

->add('orderBy','c.rank DESC, a.updated DESC')

If you still have no success the raw sql query will save you ;)

Upvotes: 1

Related Questions