Converting SQl to laravel-CodePudding

I am trying to convert Sql query to laravel query . Daytabase used is postgresql

Laravel Query

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

Showing error

SQLSTATE[42703]: Undefined column: 7 ERROR: column "codeCity" does not exist LINE 1:

Output is getting when orderby codeCity is removed

DataBase Schema

enter image description here

Without orderby results are getting

$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'))
             ->get();

CodePudding user response：

You can use selectRaw() instead of DB:raw():

DB::table('cities')
         ->select('id as city_id', 'name as lang_name', 'ascii_name as city_name', 'iso2') 
         ->selectRaw("(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();

EDIT (for reply): So weird. But you can try another like this

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

CodePudding user response：

Please try the query without orderBy statements and check if the output collection is empty or not. One reason for this error could be an empty collection as a result. So make sure the query has any results.