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

I believe using laravel 6 or above you can use correlated sub queries in addSelect() clause as

Product::addSelect(['latest_price' =>
                        Price::select('price')
                            ->whereColumn('product_id', 'products.id')
                            ->orderBy('id', 'desc')
                            ->limit(1)
])->orderBy('latest_price','desc')
  ->paginate(9);
  

So from above we will get products data along with latest price column from database so you can apply sorting on latest_price

Edit If you have limited columns to select like sale_price you can add another sub query but if you want to select whole row from price table then see another approach using join/exists

Product::addSelect(['sale_price' =>
                        Price::select('sale_price')
                            ->whereColumn('product_id', 'products.id')
                            ->orderBy('id', 'desc')
                            ->limit(1),
                    'regular_price' =>
                        Price::select('regular_price')
                            ->whereColumn('product_id', 'products.id')
                            ->orderBy('id', 'desc')
                            ->limit(1),

])->orderBy('sale_price','desc')
  ->orderBy('regular_price','desc')
  ->get();

Answer 2

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();