Converting SQl to laravel

Question

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

@Raptor Table Schema image added. Output is getting when orderby codeCity is removed — Satheesh Kumar
– Satheesh Kumar, Commented May 23, 2022 at 7:59
@viettel solutions Still showing error "SQLSTATE[42703]: Undefined column: 7 ERROR: column "codeCity"". Output is getting when orderby codeCity is removed — Satheesh Kumar
– Satheesh Kumar, Commented May 23, 2022 at 8:01
@Raptor while print output after removing orderBy codecity. the results are getting and codeCity is there in output also — Satheesh Kumar
– Satheesh Kumar, Commented May 23, 2022 at 8:41

Viettel Solutions · Accepted Answer · 2022-05-23 08:57:39Z

2

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

edited May 23, 2022 at 8:57

answered May 23, 2022 at 7:09

Viettel Solutions

1,52715 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Satheesh Kumar Over a year ago

Still showing error "SQLSTATE[42703]: Undefined column: 7 ERROR: column "codeCity"". Output is getting when orderby codeCity is removed

Viettel Solutions Over a year ago

@SatheeshKumar We reply in our answer (The comment is a little difficult to write code). You can try it.

Sahand Moghadam · Accepted Answer · 2022-05-23 05:47:02Z

0

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.

answered May 23, 2022 at 5:47

Sahand Moghadam

4674 silver badges9 bronze badges

3 Comments

Satheesh Kumar Over a year ago

Works fine without orderby. But I need Orderby

Sahand Moghadam Over a year ago

Would you please post your query output without orderBy?

Satheesh Kumar Over a year ago

Question updated pls check

Collectives™ on Stack Overflow

Converting SQl to laravel

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related