join tables and optimize query

Hello I have three tables. Products, category_product, prices. The relationship between categories-products is Many To Many. And the product-price relation is One To Many.

I need to sort products by price. The latest product price must be selected. I also have to get the price of the most expensive product in the category.

I wrote the following code:

$latestPrices = Price::select('product_id', DB::raw('MAX(created_at) as last_price_created_at'))
	->groupBy('product_id');

$products = $category->products();

$products->joinSub($latestPrices, 'latest_prices', function ($join) {
	$join->on('products.id', '=', 'latest_prices.product_id');
});

$highestPrice = $latestPrices->groupBy('prices.price')->max('price');

if(request()->input('sortByPrice')){
	$products->orderBy('prices.price');
}

$products = $products->paginate(30);

if (request()->ajax()) {
            return response()->json($products);
}
return view('Front.pages.store.showCategory', compact( 'products' ));

when clicking on sort by price, I get the following error:

message: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'prices.price' in 'order clause' (SQL: select `products`.*, `category_product`.`category_id` as `pivot_category_id`, `category_product`.`product_id` as `pivot_product_id` from `products` inner join `category_product` on `products`.`id` = `category_product`.`product_id` inner join (select `product_id`, MAX(created_at) as last_price_created_at from `prices` group by `product_id`) as `latest_prices` on `products`.`id` = `latest_prices`.`product_id` where `category_product`.`category_id` = 1 and `status` = 1 and `parent_id` is null and `show_price` = 1 order by `prices`.`price` desc limit 30 offset 0)"

please help me

nekooee

3 years ago

Best Answer

Level 1

Finally I was able to solve the problem like this:

$latestPrices = Price::whereRaw('created_at IN (select MAX(created_at) FROM prices GROUP BY product_id)');

$highestPrice = $latestPrices->max('price');

$products = $category->products();

$products->joinSub($latestPrices, 'latest_prices', function ($join) {
	$join->on('products.id', '=', 'latest_prices.product_id');
});

if(request()->input('sortByPrice')){
	$products->orderBy('latest_prices.price');
}

In total, 4 queries are executed. If you have an easier solution to write this code, without using DB::Raw, please write.

Please or to participate in this conversation.