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?

Does this answer your question? [Laravel leftJoin only last record of right table](https://stackoverflow.com/questions/38093970/laravel-leftjoin-only-last-record-of-right-table) — HTMHell, Jan 29 '21 at 19:28

score 1 · Answer 1 · edited Jan 29 '21 at 21:45

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

Thanks, M Khalid Junaid that give me the 1/2 solution, but if Prices' table has others columns (like sale_price and regular_price), how can I get them both. ordered by sale_price — amine, Jan 29 '21 at 20:26
I have updated my answer for your query and posted another which uses different approach — M Khalid Junaid, Jan 29 '21 at 20:58

score 1 · Accepted Answer · edited Jan 29 '21 at 21:44

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

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

2 Answers2