laravel join select max of joined column

Question

I have 3 table products, details and detail_colors

I want select max "stock" from "detail_colors" and max "price" from "details"

$products = Product::
           join('details', function (JoinClause $join) {
               $join->on('products.id', '=', 'details.product_id');

           })
           ->join('detail_colors', function (JoinClause $join) {
               $join->on('products.id', '=', 'detail_colors.product_id');
           })
           ->select('products.*', DB::raw('max(details.price) as price'), DB::raw('max(detail_colors.stock) as stock'))

and its not working.

I use laravel 8.*

Answer 1

Can't you use aggregate functions on relationships?

$products = Product::query()
           ->withMax('details', 'price')
           ->withMax('detail_colors', 'stock')

Or you can define the relationship as such:

public function details()
{
    return $this->hasOne(Details::class)->ofMany('price', 'max');
}
public function details()
{
    return $this->hasOne(DetailColors::class)->ofMany('stock', 'max');
}