Eloquent query with COALESCE, IF, complex ON clause

Hello, i'd like to ask, how would you write the following query in Laravel Eloquent?

Please note the COALESCE, IF, and the complex LEFT JOIN ... ON (... AND ...).

SELECT COALESCE(IF(customer_group_id=6, prices.price, NULL), products.price) AS finalPrice, prices.customer_group_id, products.*, product_translations.*
FROM `product_categories`
	LEFT JOIN `products` ON `product_categories`.`product_id` = `products`.`id`
	LEFT JOIN `product_translations` ON `product_translations`.`product_id` = `products`.`id`
	LEFT JOIN `prices` 
		ON (`products`.`id` = `prices`.`product_id` AND `prices`.`customer_group_id` = 6 )
WHERE `product_translations`.`locale` = 'it'
	AND `products`.`online` = 1
	AND `products`.`sellable` = 1
	AND `category_id` = 22

aetonsi

3 years ago

Level 1

By far, what i came up with is the following:

ProductCategory::leftjoin('products', 'product_categories.product_id', '=', 'products.id')
                    ->leftJoin('product_translations', 'product_translations.product_id', 'products.id')
                    ->leftJoin('prices', function($join) use($customer_group_id) {
                        $join->on('products.id', '=', 'prices.product_id')
                             ->on('prices.customer_group_id', '=', DB::raw($customer_group_id));
                      })
                    ->select(
                        DB::raw('coalesce(if(customer_group_id='.$customer_group_id.',prices.price,NULL), products.price) AS finalPrice'),
                        'prices.customer_group_id',
                        'products.*',
                        'product_translations.*'
                    )
                    ->where('product_translations.locale', '=', $locale)
                    ->where('products.online', '=', true)
                    ->where('products.sellable', '=', true)
                    ->where('category_id', '=', $this->id);

SilenceBringer

3 years ago

Level 55

@aetonsi just 1 note about complex join https://laravel.com/docs/9.x/queries#advanced-join-clauses

                    ->leftJoin('prices', function($join) use($customer_group_id) {
                        $join->on('products.id', '=', 'prices.product_id')
                             ->where('prices.customer_group_id', $customer_group_id);
                      })

2 likes

Please or to participate in this conversation.