Product orderBy price with discounts

Hello.

When browsing a category, i want to be able to sort products by price, also taking in account the discount price if the product has one.

I have a table "products", with a price integer that i do orderBy.

I also have a table "product_discounts" with a price integer and foreign "product_id" to make a relationship between "products" and "product_discounts"

Is there any way i can override the "products" price integer in eloquent with the new value from "product_discounts" if the relationship exists?

Also i know about sortBy, but its not what i am looking for, as i want to paginate the results.

MichalOravec

5 years ago

Best Answer

Level 75

It could be something like this, so you have to join two tables

$products = Product::select('products.*')
    ->leftJoin('product_discounts', 'products.id', '=', 'product_discounts.product_id')
    ->orderByRaw('IFNULL(product_discounts.price, products.price) ASC')
    ->get();

Also you can add eager loading if you want

$products = Product::select('products.*')->with('discount')
    ->leftJoin('product_discounts', 'products.id', '=', 'product_discounts.product_id')
    ->orderByRaw('IFNULL(product_discounts.price, products.price) ASC')
    ->get();

Use your column name for price and also relationship name.

1 like