How can I use parent relation attribute in sub query in laravel eloquent

Question

$order_detail = 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', 'qty',
                DB::raw('(unit_price*orders.ex_rate_with_base) as unit_price_new'), 'status');
        },
    ])->get();

Please help,

How can I use the attribute ex_rate_with_base from order table in sub query. I do not want to use DB query. Please solve it with eloquent.

Answer 1

I would have done it like this 👇

Database Structure:

ModelCurrency.php

class ModelCurrency extends Model
{
    public $timestamps = true;
    protected $table = 'currency';
    protected $guarded = [];
}

ModelOrderList.php

class ModelOrderList extends Model{
    public $timestamps = true;
    protected $table = 'order_list';
    protected $guarded = [];
}

ModelOrderDetail.php

class ModelOrderDetail extends Model{
    public $timestamps = true;
    protected $table = 'order_detail';
    protected $guarded = [];


    public function scopeOfUser($query, $user_id){
        return $query->where('user_id', $user_id);
    }

    public function scopeUpdatedAfter($query, $updated_at){
        return $query->where('updated_at', '>', $updated_at);
    }


    public function currency(){
        return $this->hasOne(ModelCurrency::class, 'id', 'currency_id');
    }

    public function order_lists(){
        return $this->hasMany(ModelOrderList::class, 'order_id', 'id');
    }
} 

Function in controller:

//imports
use App\ModelOrderDetail;
use Illuminate\Support\Carbon;

public function prices() {
    $user_id = 1;
    $updated_at = Carbon::yesterday();

    $data = ModelOrderDetail::with('currency', 'order_lists')->ofUser($user_id)->updatedAfter($updated_at)->get();

    foreach ($data as $d){
        $base_rate = $d->currency->base_rate;
        foreach($d->order_lists as $o) $o->new_unit_price = $base_rate * $o->unit_price;
    }

    return response()->json($data);
};

Edit - Output : https://pastebin.com/i53PytSk