Symfony3/Doctrine : ORDER BY CASE / Specific value

问题:

I'm searching a solution to order my query with query builder.

I want to order my query by ASC but with a specific value at first place. I know how to do this in SQL with order by case... but i found few solution that don't really work with doctrine.

For example : i have a table with : A, B, D, C, F, E.

I want to sort like this : F, A, B, C, D, E

I found something like that in other topic but it doesn't work for me :

$qb->addSelect("(CASE When c.type like 'foo%' Then 1 WHEN c.type like 'foo1%' THEN 1 ELSE 0 END) AS HIDDEN type");

Is it possible to do something like this directly in the orderBy of a QueryBuilder (Doctrine) ?

Thanks for your reply

--- EDIT ---

I found the problem, it come from the 'group_by' option in my form. So here is my function in my repository :

public function findBolQB($username)
    {
        return $this
                ->createQueryBuilder('p')
                ->innerJoin('p.program','prg')
                ->innerJoin('prg.bol','b')
                ->innerJoin('prg.user','user')
                ->innerJoin('p.part','part')
                ->addSelect("(CASE When user.username like :name Then 0 ELSE 1 END) AS HIDDEN userChancla")
                    ->setParameter(':name', $username)
                ->orderBy('userChancla')
                ->addOrderBy('b.code','ASC')
                ->addOrderBy('part.name','ASC');

    }

Don't forget the orderBy('userChancla') or it would not work !

The list is use in a form and is group by user so in my form builder my code is :

->add('bolEnBois', EntityType::class, array(
            'class' => 'IssouChanclaBundle:Bol',
            'placeholder' =>'--- Bol---',
            'required' => false,
            'attr'=>array('class'=>'select2'),
            'query_builder' => function(BolRepository $a)use($options)
                {
                    return $a->findBolQB($options['user']);
                },
            'group_by' => function(Bol $bol)
                {
                    return $bol->getUser()->getFirstName().' '.$article->getUser()->getLastName();
                },
        ))

Hope it would help some of you :)

回答1:

You can add multiple order by like this,

 $qb->addOrderBy('A', 'DESC')
    ->addOrderBy('B', 'DESC')
    ->addOrderBy('C', 'DESC');

...and so on. You will need to decide whether it is Ascending ASC or Descending DESC.

Hope this helps.