CODEWITHSUNDEEP
laravellaravel-5.5
Javed
Javed

Reputation: 857

Laravel : Search by min and max value from the table

I am confuse about search with min-max value.In my posts table there is a two field min_price and max_price, on my search there is a couple of thing which I need to covered in search query.

  1. If user search with only max_value, it shows all the posts which price is less than or equal to max_value.

  2. If user search with only min_value, it shows all the posts which price is less than or equal to min_value.

  3. If user search with min_value and max_value, it shows all the posts which price is between min_value and max_value.

  4. If both null, return all posts.

How can I do this ?

My code:

$searchablePost = Post::with(['product','postattribute.attribute.category','user.userDetails'])
                 ->whereIn('product_id', $userApprovalProductIDs)
                ->whereIn('demand_or_supply', $demand_or_supply);

// skip my search query code

$searchedPost = $searchablePost->offset($offset)->limit($limit)->orderBy('id','desc')->get();

How can I do t

Upvotes: 1

Views: 3505

Answers (3)

TalESid
TalESid

Reputation: 2514

Check:
1. if both (min & max values) are available (i.e. not null):
2. if min value is available:
3. if max value is available: 

// if none of them is null
if (! (is_null($min_value) && is_null($max_value))) {
    // fetch all between min & max values
    $searchablePost = $searchablePost->whereBetween('price', [$min_value, $max_value]);
}
// if just min_value is available (is not null)
elseif (! is_null($min_value)) {
    // fetch all greater than or equal to min_value
    $searchablePost = $searchablePost->where('price', '>=', $min_value);
}
// if just max_value is available (is not null)
elseif (! is_null($max_value)) {
    // fetch all lesser than or equal to max_value
    $searchablePost = $searchablePost->where('price', '<=', $max_value);
}

If you have separate fields for min_price & max_price, as mentioned in comment, just change the code as following:

if (! (is_null($min_value) && is_null($max_value))) {
    $searchablePost = $searchablePost
                      ->where('min_price', '>=', $min_value)
                      ->where('max_price', '<=', $max_value);
}
elseif (! is_null($min_value)) {
    $searchablePost = $searchablePost->where('min_price', '>=', $min_value);
}
elseif (! is_null($max_value)) {
    $searchablePost = $searchablePost->where('max_price', '<=', $max_value);
}

Upvotes: 5

Salar Pourfallah
Salar Pourfallah

Reputation: 1582

You can set $min = 0; and $max = infinite_choosen_number; and append whereBetween method to your query, like the below code: 

$searchablePost = Post::with(['product','postattribute.attribute.category','user.userDetails'])
    ->whereIn('product_id', $userApprovalProductIDs)
    ->whereIn('demand_or_supply', $demand_or_supply)
    ->whereBetween('price', ["$min", "$max"])->get();

Reference: https://laravel.com/docs/5.6/queries

Upvotes: 1

Sylvain Bastid
Sylvain Bastid

Reputation: 11

You can't do that with a whereIn, you can do that with a where statement. Something like this

`
$searchablePost = Post::with(['product','postattribute.attribute.category','user.userDetails'])
                 ->whereIn('product_id', $userApprovalProductIDs)
                ->whereIn('demand_or_supply', $demand_or_supply)
->where('price', '>=', $minPrice)
`

Didn't try it so int might fail but here is the way to do it.

Upvotes: 0

Related Questions