Converting SQl Query to laravel

I am trying to convert SQLquery to Laravel query . Database used is PostgreSQL

Laravel

$c_list = DB::table('cities')
             ->select('id as city_id', 'name as lang_name', 'ascii_name as city_name', 'iso2', DB::raw("(CASE WHEN iso2 = 'DE' THEN 'Germany' WHEN iso2 = 'CH' THEN 'Switzerland' ELSE 'India' END) AS codeCity"))
             ->whereIn('iso2', array('DE', 'IN', 'FR', 'TA', 'CH'))
             ->orderBy('codeCity','asc')
             ->orderBy('city_name','asc')
             ->get();

ERROR

SQLSTATE[42703]: Undefined column: 7 ERROR: column "codeCity" does not exist LINE 1:
Output is getting when orderby codeCity is removed

richardhulbert

4 years ago

Level 6

Leaving aside the fact that you are encoding data and logic in your query, which is not really a good idea. You could try this:

->orderBy(5,3,'asc)

That might fix your issue.

see this: https://stackoverflow.com/questions/11785622/how-to-use-an-alias-in-a-postgresql-order-by-clause

more generally is to use

->toSQL()

instead of ->get() and that will spit out the SQL you are generating. I sometimes do this and copy the output to a sql editor where I can see what is going on. more clearly

Please or to participate in this conversation.