Laravel Order By With Data Relation

Question

I have realtions data with output like this,

{pi_id:1, pi_name:book1,'{sp_id:1, sp_price:200.000}},

{pi_id:2, pi_name:book2,'{sp_id:2, sp_price:100.000}},

{pi_id:3, pi_name:book3,'{sp_id:3, sp_price:250.000}},

I want to sort the output data by sp_price so it's like this

{pi_id:2, pi_name:book2,'{sp_id:2, sp_price:100.000}},

{pi_id:1, pi_name:book1,'{sp_id:1, sp_price:200.000}},

{pi_id:3, pi_name:book3,'{sp_id:3, sp_price:250.000}},

I've tried making a function on the model but it still doesn't work, I've also tried making it on the blade but it still doesn't work,

view

@foreach ($collection as $colls)
                    @php
                        $fligship = App\Models\product\items::with('linkFrame', 'linkStore')
                            ->where('pi_product_collection_id', $colls->pc_id)
                            ->where('pi_flagship', true)
                            ->first();

                        $price = App\Models\product\items::with([
                            'linkStore' => function ($q) {
                                $q->orderBy('sp_price', 'asc');
                            },
                        ])
                            ->where('pi_product_collection_id', $colls->pc_id)
                            ->where('old_pi_code', 'LIKE', '%' . @$list_img->linkFrame->pf_code . '%')
                            ->get();

                    @endphp
                    <div class="container">
                        <p>{{ @$fligship->pi_name }}</p>
                        <p>{{ @$price }}</p>
                    </div>
                @endforeach

this is my controller

public function product(Request $request, $id){

    // get daybed

    if($id=10){
        $frame = frames::whereIn('pf_product_category_id',[$id,16])
        ->where('pf_status',true)->with('linkProducts')->orderBy('pf_code','asc')
        ->get();
    }else{
        $frame = frames::where('pf_product_category_id',$id)
        ->where('pf_status',true)->with('linkProducts')
        ->get();
    }

    // get collection
    $id_coll = [];

    foreach($frame as $coll){
        array_push($id_coll, $coll->linkProducts[0]->pi_product_collection_id);

    }

    $id_coll = array_unique($id_coll);

    $collection = collections::where('pc_status',true)->whereIn('pc_id',$id_coll)
    ->orderBy('pc_name','asc')->get();

    // $price = items::get()->sortBy(function($query){
    //     return @$query->linkStore->sp_price;
    // })->first();

    //dd($coll);
    return view('frontend/products/product-list',compact('frame','collection'));
}

and this is my model

public function linkStore(){
    return $this->belongsTo(products::class, 'pi_id','sp_product_id')->orderBy('sp_price','asc');
}

thank you

Answer 1

You need to add a join to your query:

$price = App\Models\product\items::with('linkStore')
    ->join('prices', 'items.pi_id', '=', 'prices.sp_product_id')
    ->orderBy('prices.sp_price')
    ->get(),
])

You will have to change items and prices to the actual table names.