CODEWITHSUNDEEP
phpsqllaravel
Sam J.
Sam J.

Reputation: 353

orWhere vs Where Laravel 5.1

I have the following code:

$results = \DB::table('pack')
            ->Join('users as u1', 'u1.id', '=', 'pack.userid')
            ->Join('skills as s1', 's1.packid', '=', 'pack.packid')
            ->where('u_status', '=', "0")
            ->where('pack.amount', '<', 100)
            ->where('pack.amount', '>', 10)                 
            ->where('pack_status', '=', "2")
            ->where('pack.title', 'LIKE', "%$text%")
            ->orWhere('pack.description', 'LIKE', "%$text%")
            ->orWhere('pack.skills', 'LIKE', "%$text%")
            ->orWhere('s1.name', 'LIKE', "%$text%")
            ->orderBy('packid', 'desc')
            ->orderBy('featured', 'desc')
            ->groupBy('packid')
            ->take(40)
            ->get();
return $results;

Everything is working except ->where('amount', '<', 100) and ->where('amount', '>', 10).

It does not exclude either of those, and shows amounts above and below the numbers I set. If I remove the orWhere() it works fine.

Am I using orWhere() and where() correctly?

Upvotes: 4

Views: 1527

Answers (2)

patricus
patricus

Reputation: 62278

The issue is that you've combined ORs and ANDs without grouping them properly. Think about the following conditions:

$bool1 = false && false || true; // =true
$bool2 = false && (false || true); // =false

So, to get this to work properly, you need to group your conditions properly. You can group conditions by passing a closure to the where() or orWhere() methods. I'm guessing you want to group the $text conditions together, so your code would look like:

$results = \DB::table('pack')
    ->join('users as u1', 'u1.id', '=', 'pack.userid')
    ->join('skills as s1', 's1.packid', '=', 'pack.packid')
    ->where('u_status', '=', "0")
    ->where('pack.amount', '<', 100)
    ->where('pack.amount', '>', 10)                 
    ->where('pack_status', '=', "2")
    ->where(function($query) use ($text) {
        $query->where('pack.title', 'LIKE', "%$text%")
            ->orWhere('pack.description', 'LIKE', "%$text%")
            ->orWhere('pack.skills', 'LIKE', "%$text%")
            ->orWhere('s1.name', 'LIKE', "%$text%");
    })
    ->orderBy('packid', 'desc')
    ->orderBy('featured', 'desc')
    ->groupBy('packid')
    ->take(40)
    ->get();

return $results;

Upvotes: 6

smartrahat
smartrahat

Reputation: 5609

If your database row matches any of the orWhere it will show the row. In your code you have 

->orWhere('pack.description', 'LIKE', "%$text%")
->orWhere('pack.skills', 'LIKE', "%$text%")
->orWhere('s1.name', 'LIKE', "%$text%")

If you row matches any of them it will ignore other where or orWhere

Upvotes: 0

Related Questions