OrderBy is not working with alias in doctrine symfony2

Question

what i need

i need sort alias.

symfony code

$qb->addSelect("(CASE WHEN  (priority LIKE  '%High%') THEN 1
       WHEN  (priority LIKE '%Medium%') THEN 2

       ELSE 3 END) AS  prioritys ");

sorting code

       priority  column name of view/table.

       prioritys  is alias.

      $qb->orderBy('priority','ASC');   //works because here im accessing column from table/view.

      $qb->orderBy('prioritys','ASC');   //doesnot works.

Error on using Alias

Error : [Semantical Error] line 0, col 173 near 'prioritys ASC,': Error: 'prioritys' is not defined.

any suggestion is most welcome.

*doesnot works* means do you get any error? or it is not sorting the results? — M Khalid Junaid, Jul 05 '18 at 06:46
Also you could get the raw query generated by doctrine like `$qb->getSql()` and see if there is some issue or run this raw query on your database to verify — M Khalid Junaid, Jul 05 '18 at 06:50

Gaj · Answer 1 · 2018-10-31T08:48:21.150

0

$qb->orderBy('prioritys','ASC');   // it should work, check symfony2 doc.

As per the major databases, we can use column alias name in order by clause. So check your front end.

If not supported then use CASE expression in order by clause like below

ORDER BY CASE WHEN priority LIKE '%High%' THEN 1 WHEN priority LIKE '%Medium%' THEN 2 ELSE 3 END

edited Oct 31 '18 at 08:48

answered Jul 05 '18 at 06:55

Gaj

888
5
5

I'm not good in front end. I can suggest below coding (if column alias not working in order by clause) $qb->orderBy("(CASE WHEN (priority LIKE '%High%') THEN 1 WHEN (priority LIKE '%Medium%') THEN 2 ELSE 3 END)"); – Gaj Jul 05 '18 at 07:12
soory it giving err Error: Expected end of string, got 'CASE' – afeef Jul 05 '18 at 07:14

OrderBy is not working with alias in doctrine symfony2

1 Answers1