Laravel eloquent: Multiply two columns of two different tables

Question

My Order Model

class Order extends Model
{
    protected $fillable = [
        'id','user_id', 'erp_id', 'currency_code','ex_rate_with_base','order_status','status','created_at'
    ];

    protected $hidden = ['updated_at',];

    public function orderList(){
        return $this->hasMany(OrderList::class);
    }   

    public function currency(){
        return $this->belongsTo(Currency::class,'currency_code');
    }

}

My Currency Model

class Currency extends Model
{

    protected $fillable = [
        'currency_code','currency_name', 'currency_symbol', 'ex_rate_with_base', 'update_via', 'status',
    ];
    protected $hidden = [
        'created_at','updated_at','updated_by','created_by',
    ];

    protected $primaryKey = 'currency_code';

    public $incrementing = false;

    public function order()
    {
        return $this->hasMany(Order::class,'currency_code');
    }
}

My OrderList Model

class OrderList extends Model
{
    protected $fillable = [
        'id','order_id', 'product_code', 'qty','unit_price','status',
    ];

    protected $hidden = [
       'created_at' ,'updated_at',
    ];

    public function order(){
        return $this->belongsTo(Order::class);
    }
}

---

In my Order controller I want to run the query:

$order_history_list = Order::where([['user_id', $user->id], ['updated_at','>', $updated_at]])
    ->with([
        'currency' => function ($query) {
            $query->select('currency_code','currency_symbol','ex_rate_with_base');
        },
        'orderList' => function ($query) {
            $query->select('id','order_id', 'product_code', '***order_lists.qty * orders.ex_rate_with_base AS unit_price_with_ex_rate***','status');
        }
    ])->get();

But error is occuring due to the highlighted portion. Error: Unknown column 'order_lists.qty * orders.ex_rate_with_base' in 'field list' Please help me with the correct syntax How can I use column of order table in the sub query ?

Answer 1

Use DB::raw in your select statement and add a join to the 'orderList' $query

    $order_history_list = Order::where([['user_id', $user->id], ['updated_at','>', $updated_at]])
    ->with([
        'currency' => function ($query) {
            $query->select('currency_code','currency_symbol','ex_rate_with_base');
        },
        'orderList' => function ($query) {
            $query->select('id','order_id', 'product_code', DB::raw('order_lists.qty * orders.ex_rate_with_base AS unit_price_with_ex_rate'),'status')
                  ->join('orders','order_lists.order_id', 'orders.id');
        }
    ])->get();