How to Create Multiple Where Clause Query Using Laravel Eloquent?

Viewed 84 times

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?

Answers

In Laravel 5.3 (and still true as of 7.x) you can use more granular wheres passed as an 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 ...)

Sunday, December 18, 2022

drowneath-35aca70aca40

While attach you can pass an additional array as you have passed.

$item->users()->attach($request->clients, ['role'=>'client']);
$item->users()->attach($request->employees, ['role'=>'employee']);

But In the sync you have to pass pivot value inside the array, I have mention example below.

$item->roles()->sync([1 => ['role' => 'client'], 2 => ['role' => 'employee']);

Check documentation sync part,

Monday, November 14, 2022

davide_gualano

Solution 1: Using a global scope to add the field to your select statements

The one issue with virtual columns is Eloquent will try to update them if you don't prevent it. You can exclude it from the $fillable array and use a global scope in your model to add the virtual column, something like:

protected static function boot()
{
    parent::boot();

    static::addGlobalScope('next_action', function (Builder $builder) {
        $builder->addSelect(DB::raw('DATE_ADD(updated_at, INTERVAL days DAY) AS next_action'));
    });
}

You'll have to keep in mind that this will couple your Model to MySQL since the date functions you're using won't work in databases like sqlite.

Solution 2: Using a MySQL view

Normally what I do when I have multiple calculated fields is create a MySQL view. Create the view with all the calculated fields you want, then you can create a new Eloquent model for that view without having to worry about query scopes.

The one caveat is you of course can't use create/update/delete on this model, but if you're only using the model for display purposes, that shouldn't be a problem.

Sunday, December 4, 2022

vivek_m._chawla

Make use of Parameter Grouping (Laravel 4.2). For your example, it'd be something like this:

Model::where(function ($query) {
    $query->where('a', '=', 1)
          ->orWhere('b', '=', 1);
})->where(function ($query) {
    $query->where('c', '=', 1)
          ->orWhere('d', '=', 1);
});

Sunday, December 25, 2022

miro_a.

Ah, you've joined the ranks of Advanced Where users -- welcome!

$user = Model::
    ->where('user_id', 1)
    ->where('member', 'Yes')
    ->where(function($query) {
        $query
            ->where('cricket', 1)
            ->orWhere('tennis', 1);
            ->orWhere('football', 1);
    })
    ->select(['id', 'user_id', 'member', 'football', 'tennis', 'cricket'])
    ->get()
;

Thursday, October 20, 2022

querenker

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

laravel

eloquent

laravel-query-builder

How to Create Multiple Where Clause Query Using Laravel Eloquent?

Answers

php,laravel,eloquent,laravel-query-builder