Level 4
Ok, think i've figured it out checking for both discounted and real price
$q->where('pd.price', '<=', 999)
->orWhere('products.price', '<=', 999)
Oct 5, 2019
2
Level 4
Where condition on aggregated query doesnt work, but orderBy works...
I have the following query where i'm sorting products by price (asc and desc) and i also want to filter them between a price range but also taking into account if there's any discount for them. The sorting part works, but when i throw in the price range condition it blows up. Any thoughts?
$q
->selectRaw( 'products.*,
CASE
WHEN pd.price IS NULL THEN products.price
ELSE LEAST(products.price, pd.price)
END as price' )
->leftJoin( 'products_discounts as pd', function ( $join ) {
$join->on( 'products.id', '=', 'pd.product_id' )
->on( 'pd.priority', '=', \DB::raw( '(SELECT min(priority) FROM products_discounts WHERE product_id = pd.product_id)' ) )
->where( function ( $query ) {
$query->whereDate( 'pd.date_start', '>=', Carbon::today() )
->orWhereNull( 'pd.date_start' );
} )
->where( function ( $query ) {
$query->whereDate( 'pd.date_end', '<=', Carbon::today() )
->orWhereNull( 'pd.date_end' );
} )
->whereNotNull( 'pd.limit' );
} );
// here it throws up 'column price is ambiguous'
$q->where('price', '<=', 999);
$q
->when( $sortType === 'price-up', function ( $qq ) {
$qq->orderBy( 'price', 'asc' );
} )
->when( $sortType === 'price-down', function ( $qq ) {
$qq->orderBy( 'price', 'desc' );
} );
Sorry, can't format this the way it should be... dunno why.
Regards
Level 4
Forget the previous one. This works
$query
->whereRaw( "CASE WHEN pd.price IS NULL THEN products.price >= 1 ELSE pd.price >= 1 END" );
$query
->whereRaw( "CASE WHEN pd.price IS NULL THEN products.price <= 999 ELSE pd.price <= 999 END" );
Please or to participate in this conversation.