CODEWITHSUNDEEP
mysqllaravel-5eloquent
Нилов Сергей
Нилов Сергей

Reputation: 91

With eloquent to make search by 2 fields

Is there a way in Laravel 5.6/MySQL 5.7 with eloquent to make search with condition:

  • need to check if sale_price is set then search in sale_price else in retail_price

Have I use condition like :

  \DB::raw( ‘ isNull( sale_price, retail_price ) > 10’ )

and how to modify the search above if both sale_price and retail_price are null?

Or there is better solution ?

MODIFIED :

I implemented it as scope :

public function scopeGetByDoublePrice($query, $price_from= null, $price_to= null)
{
    // need to check if sale_price is set then search in sale_price else in retail_price

    if ( empty($price_from) and empty($price_to) ) return $query;

    if ( !empty($price_from) ) { // $price_from is set
        $query->whereNotNull('sale_price')->where('sale_price', '>=', $price_from);
        $query->orWhere('retail_price', '>=', $price_from);
    } // if (!empty($price_from) ) { // $price_from is set


    if ( !empty($price_to) ) { // $price_to is set
        $query->whereNotNull('sale_price')->where('sale_price', '<=', $price_to);
        $query->orWhere('retail_price', '<=', $price_to);
    } // if (!empty($price_to) ) { // $price_to is set

    return $query;
}

but setting 2 values 31 and 33 I have different result I expected :

https://i.sstatic.net/Wg78A.jpg I expected only the first row would be in results set!

In sql-editor I set "()" manually, as :

SELECT sale_price, retail_price FROM articles LEFT JOIN brands AS b on b.id = articles.brand_id LEFT JOIN article_inventories AS ai on ai.article_id = articles.id WHERE ( ( sale_price is not null AND sale_price >= '31' OR retail_price >= '31' ) AND ( sale_price is not null AND sale_price <= '33' OR retail_price <= '33') )

and it works as I need. If there is a way to set "()" in my scope?

Upvotes: 1

Views: 37

Answers (1)

jedrzej.kurylo
jedrzej.kurylo

Reputation: 40909

Use to following constraint:

$price = 10;
$builder->where(function($query) use ($price) {
  $query->whereNotNull('sale_price')->where('sale_price', '>', $price);
  $query->orWhere('retail_price', '>', $price);
});

This will add the following to your query:

AND (sale_price IS NOT NULL and sale_price > 10 OR retail_price > 10)

What's important, you'll find those extra constraints wrapped in parenthesis which will let you avoid the situation, when other constraints are ignored because of the OR.

Upvotes: 1

Related Questions