CODEWITHSUNDEEP
laravel
TheUnreal
TheUnreal

Reputation: 24492

Laravel Where and OrWhere multiple conditions

I have the following query:

$items = UserItems::join('items','items.id','=','user_items.item_id')
            ->where('user_id','=',$this->id)
            ->where('quantity','>',0)
            ->where('items.type',"stones")
            ->orWhere('items.type',"fish")
            ->get();

My problem is that this query returns the items of ALL users instead of the given user_Id.. It's because the orWhere applies on all of the where's but I need it to apply only on this:

->where('items.type',"stones")
                ->orWhere('items.type',"fish")

How I can fix the query to return only the given $this->id user items?

Upvotes: 4

Views: 4938

Answers (3)

Muhammad Haris
Muhammad Haris

Reputation: 53

when we have to use any variable in where/orwhere clause when using function then we have to pass those in use variable

    `$acc_exist = Account::where('group_id', $fgn_grp_id)->
    where('company_id', session('company_id'))->
    where(function ($query) use($acc_name, $acc_num) {
    $query->where('name', $acc_name)->orWhere('number', $acc_num);
    })->first();`

Upvotes: 1

camelCase
camelCase

Reputation: 5608

Laravel supports Advanced Where Clauses like such:

$items = UserItems::join('items','items.id','=','user_items.item_id')
    ->where('user_id','=',$this->id)
    ->where('quantity','>',0)
    ->where(function($query) {
        $query->where('items.type',"stones")
            ->orWhere('items.type',"fish");
    })
    ->get();

I guess another option (cleaner, easier to read IMO), as opposed to using orWhere, could be to use the whereIn operator like:

$items = UserItems::join('items','items.id','=','user_items.item_id')
    ->where('user_id','=',$this->id)
    ->where('quantity','>',0)
    ->whereIn('items.type', ["stones", "fish"])
    ->get();

Upvotes: 6

The Alpha
The Alpha

Reputation: 146269

Use a closure, something like the following:

$userId = $this->id;
$items = UserItems::join('items','items.id','=','user_items.item_id')
->where('user_id','=', $userId)
->where('quantity','>',0)
->where(function($query) {
    $query
    ->where('items.type',"stones")
    ->orWhere('items.type',"fish");
})
->get();

Upvotes: 0

Related Questions