How to Create Multiple Where Clause Query Using La

Question

I'm using the Laravel Eloquent query builder and I have a query where I want a WHERE clause on multiple conditions. It works, but it's not elegant.

Example:

$results = User::where('this', '=', 1)
    ->where('that', '=', 1)
    ->where('this_too', '=', 1)
    ->where('that_too', '=', 1)
    ->where('this_as_well', '=', 1)
    ->where('that_as_well', '=', 1)
    ->where('this_one_too', '=', 1)
    ->where('that_one_too', '=', 1)
    ->where('this_one_as_well', '=', 1)
    ->where('that_one_as_well', '=', 1)
    ->get();

Is there a better way to do this, or should I stick with this method?

Answer 1

In Laravel 5.3 you can use more granular wheres passed as array:

$query->where([
    ['column_1', '=', 'value_1'],
    ['column_2', '<>', 'value_2'],
    [COLUMN, OPERATOR, VALUE],
    ...
])

Personally I haven't found use-case for this over just multiple where calls, but fact is you can use it.

Since June 2014 you can pass an array to where

As long as you want all the wheres use and operator, you can group them this way:

$matchThese = ['field' => 'value', 'another_field' => 'another_value', ...];

// if you need another group of wheres as an alternative:
$orThose = ['yet_another_field' => 'yet_another_value', ...];

Then:

$results = User::where($matchThese)->get();

// with another group
$results = User::where($matchThese)
    ->orWhere($orThose)
    ->get();

The above will result in such query:

SELECT * FROM users
  WHERE (field = value AND another_field = another_value AND ...)
  OR (yet_another_field = yet_another_value AND ...)

Answer 2

$projects = DB::table('projects')->where([['title','like','%'.$input.'%'],
    ['status','<>','Pending'],
    ['status','<>','Not Available']])
->orwhere([['owner', 'like', '%'.$input.'%'],
    ['status','<>','Pending'],
    ['status','<>','Not Available']])->get();

Answer 3

You can use eloquent in Laravel 5.3

All results

UserModel::where('id_user', $id_user)
                ->where('estado', 1)
                ->get();

Partial results

UserModel::where('id_user', $id_user)
                    ->where('estado', 1)
                    ->pluck('id_rol');

Answer 4

As per my suggestion if you are doing filter or searching

then you should go with :

        $results = User::query();
        $results->when($request->that, function ($q) use ($request) {
            $q->where('that', $request->that);
        });
        $results->when($request->this, function ($q) use ($request) {
            $q->where('this', $request->that);
        });
        $results->when($request->this_too, function ($q) use ($request) {
            $q->where('this_too', $request->that);
        });
        $results->get();

Answer 5

public function search()
{
    if (isset($_GET) && !empty($_GET))
    {
        $prepareQuery = '';
        foreach ($_GET as $key => $data)
        {
            if ($data)
            {
                $prepareQuery.=$key . ' = "' . $data . '" OR ';
            }
        }
        $query = substr($prepareQuery, 0, -3);
        if ($query)
            $model = Businesses::whereRaw($query)->get();
        else
            $model = Businesses::get();

        return view('pages.search', compact('model', 'model'));
    }
}

Answer 6

The whereColumn method can be passed an array of multiple conditions. These conditions will be joined using the and operator.

Example:

$users = DB::table('users')
            ->whereColumn([
                ['first_name', '=', 'last_name'],
                ['updated_at', '>', 'created_at']
            ])->get();

$users = User::whereColumn([
                ['first_name', '=', 'last_name'],
                ['updated_at', '>', 'created_at']
            ])->get();

For more information check this section of the documentation https://laravel.com/docs/5.4/queries#where-clauses