CODEWITHSUNDEEP
phpsqllaravel-5
nishil bhave
nishil bhave

Reputation: 632

Laravel Eloquent query builder getting confused with 'or' operator in sql

How to write laravel eloquent for the below SQL query ? username,first_name,last_name and email are column names.

 select * from `message_users` where `username` like %somthing% and (`first_name` like %somthing% or `last_name` like %somthing%) and `email` like %something%

I've tried this 

$queryObj= $this;
        if(!empty($filter['username']))
            $queryObj = $queryObj->where('username','like','%'.$filter['username'].'%');
        if(!empty($filter['name'])){
            $queryObj = $queryObj->where('first_name','like','%'.$filter['name'].'%');
            $queryObj = $queryObj->orWhere('last_nam','like','%'.$filter['name'].'%');
        }

        if(!empty($filter['email']))
            $queryObj = $queryObj->where('email','like','%'.$filter['email'].'%');

        $result = $queryObj->orderBy('id','DESC')->paginate($limit);
        return $result;

but this builds query without round brackets. Which don't give me a correct result!

select * from `message_users` where `username` like %somthing% and `first_name` like %somthing% or `last_name` like %somthing% and `email` like %something%

Upvotes: 0

Views: 271

Answers (1)

follio
follio

Reputation: 547

You can use Advanced where clauses - parameter grouping . The query inside closure should be contained within the parenthesis group. 

    if(!empty($filter['name'])){
        $queryObj->where(function ($query) use ($filter) {
            $query->where('first_name','like','%'.$filter['name'].'%')
                  ->orWhere('last_name','like','%'.$filter['name'].'%');
        });
    }

Upvotes: 1

Related Questions