Laravel leftJoin only last record of right table and order by it

Question

I have two tables. 1) products 2) prices

-------------------------
-       products        -
-------------------------
- id         | int      -
- name       | varchar  -
- created_at | datetime -
- updated_at | datetime -
-------------------------

----------------------------
-          prices          -
----------------------------
- id            | int      -
- product_id    | int      -
- sale_price    | int      -
- regular_price | int      -
- created_at    | datetime -
- updated_at    | datetime -
-----------------------------

I want to search through products and get the last price of each product from prices table. I use this :

class Product extends Model
{
   public function lastPrice()
    {
        return $this->hasOne(Price::class)->orderBy('id', 'DESC');
    }

}

And I get products list with the last price using :

$products=Product::with('lastPrice')->paginate(9);

My question is: I want to order the result by highest/lower price. How I will do that?

Answer 1

You can select latest row from prices table along with product data there are 2 ways which I can think of right now as

 // Approach 1
 Product::join('prices as a', 'products.id', '=', 'a.product_id')
        ->leftJoin('prices as a1', function ($join) {
            $join->on('a.product_id', '=', 'a1.product_id')
                 ->whereRaw(DB::raw('a.id < a1.id'));
           })
        ->whereNull('a1.product_id')
        ->select('products.*', 'a.*')
        ->orderBy('sale_price','desc')
        ->orderBy('regular_price','desc')
        ->get();

// Approach 2 with whereExists
 Product::join('prices as a', 'products.id', '=', 'a.product_id')
        ->whereExists(function ($query) {
           $query->select(DB::raw(1))
                 ->from('prices as b')
                 ->whereRaw(DB::raw('a.product_id = b.product_id'))
                 ->havingRaw('max(b.id) = a.id');
        })
        ->select('products.*', 'a.*')
        ->orderBy('sale_price','desc')
        ->orderBy('regular_price','desc')
        ->get();