Laravel Query Fetch Wrong Data

Question

I have written Laravel Search Query.

$product = Product::where('user_role_id', '=', 3)
                           ->where('user_id', '!=', $user_id)
                           ->where('quantity_in_stock', '>', 0);
                if (!empty($suggestion)) {
                    $product->where('product_name', 'like', '%' . $suggestion . '%')->orWhere('ndc_number', 'like', '%' . $suggestion . '%');
                }
                $search = $product->orderby('id', 'desc')->get();
                $products = $search->toArray();

But my issue is that when I am looking search result I found such results which have user_role_id => 2 But in query I have given equal to 3.

Can any help to sort out this issue.

Thanks

John Brad · Accepted Answer · 2018-08-02 16:40:31Z

1

That is because the query you have is translated to:

SELECT * 
FROM products
WHERE user_role_id = 1 
AND user_id != ? 
AND quantity_in_stick > 0 
AND product_name like %?% 
OR ndc_number like %?%

What I think you're trying to achieve is that the last two conditions are together, and as they are now, they affect the whole set of results as long as the OR statement is included. you want (product_name like %?% or ndc_number like %?%)

$product = Product::where('user_role_id', '=', 3)
                  ->where('user_id', '!=', $user_id)
                   ->where('quantity_in_stock', '>', 0);
if (!empty($suggestion)) {
    //You can re-assign the variable but I can't remember if you really need it or not, try it out 
    $product = $product->where(function($advancedWhere) use($suggestion) { 
        $advancedWhere->where('product_name', 'like', '%' . $suggestion . '%')
                      ->orWhere('ndc_number', 'like', '%' . $suggestion . '%');
    });
}
$search = $product->orderby('id', 'desc')->get();
$products = $search->toArray();

edited Aug 2, 2018 at 16:40

John Brad

4672 gold badges10 silver badges27 bronze badges

answered Aug 2, 2018 at 14:55

abr

2,0941 gold badge22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Brad Over a year ago

Thanks abr. it work. Just you need to give use($suggestion) too in your closure function. thanks.

Barun · Accepted Answer · 2018-08-02 14:51:17Z

0

Assign to the variable after appending more where conditions like:

if (!empty($suggestion)) {
    $product = $product->where('product_name', 'like', '%' . $suggestion . '%')->orWhere('ndc_number', 'like', '%' . $suggestion . '%');
}

answered Aug 2, 2018 at 14:51

Barun

4,4714 gold badges25 silver badges30 bronze badges

Collectives™ on Stack Overflow

Laravel Query Fetch Wrong Data

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related