Column not found:1054 error even though the column is defined in laravel

Question

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'vpc_order_info' in 'where clause' (SQL: select `sequa_transaction_id` as `vpc_transaction_no`, `merchant_order_id` as `vpc_order_info`, `date_created` as `yesterday`, `card_number` as `vpc_Card`, `amount` as `vpc_Amount`, `authorization_code` as `vpc_authorizeId` from `ipg_sentry_response` where `merchant_id` = 12136901 and `vpc_order_info` like %-% and `response_code` = 1 and `date_created` between 2016-03-22 and 2016-09-31)

I am getting this error message though I have defined the column as given below.

$col = array("sequa_transaction_id as vpc_transaction_no","merchant_order_id as vpc_order_info","date_created as yesterday","card_number as vpc_Card", "amount as vpc_Amount","authorization_code as vpc_authorizeId");

     $records = DB::table('ipg_sentry_response')->select($col)
     ->where('merchant_id', '=', $vpc_Merchant)
             ->where('vpc_order_info' ,'like', "%-%")            
     ->where('response_code', '=', '1')
     ->whereBetween('date_created', array($date,$date2))
     //->whereBetween('date_created', array($date,$date2))
     ->get();

Can anyone please help me with it? I am using Laravel version 5.2.

Unknown column 'vpc_order_info' means didnt defined in your table — devpro
Ensure 'merchant_order_id' exists in the table. check spellings — Emeka Mbah

Sougata Bose Sougata Bose · Accepted Answer · 2016-11-04T06:20:09

The column alias is defined but it is not available to be used in where caluse yet. You can use alias in group, order... you need to use the column name instead of the alias. Try -

 $col = array("sequa_transaction_id as vpc_transaction_no",
        "merchant_order_id as vpc_order_info","date_created as yesterday",
        "card_number as vpc_Card", "amount as vpc_Amount",
        "authorization_code as vpc_authorizeId");

 $records = DB::table('ipg_sentry_response')->select($col)
 ->where('merchant_id', '=', $vpc_Merchant)
 ->where('merchant_order_id' ,'like', "%-%")            
 ->where('response_code', '=', '1')
 ->whereBetween('date_created', array($date,$date2))
 ->get();

You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses. Standard SQL doesn't allow you to refer to a column alias in a WHERE clause.

Column not found:1054 error even though the column is defined in laravel

2 Answers