Asked  7 Months ago    Answers:  5   Viewed   39 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

53

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 ...)
Wednesday, March 31, 2021
 
BetaRide
answered 7 Months ago
86

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,

Wednesday, March 31, 2021
 
Sujith
answered 7 Months ago
53

As I have Succeeded in inserting as following

       DB::connection('mongodb')->collection('subscribers')->insert(array(
           '_id' => $subscriber->device_id,
           'subscriptions' => array(array('list_id' => $subscriber->list1_id)),
          ));

For pushing into Subscriptions array I used following code

Subscription::where('_id',$subscriber->device_id)->push('subscriptions', array('list_id' => $subscriber->list1_id)); 
Saturday, May 29, 2021
 
Stefan
answered 5 Months ago
47

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, June 6, 2021
 
Rudie
answered 5 Months ago
74

Also show this answer and for your variant:

$subQuery = DB::table('orders')->selectRaw('driver_id, created_at, COUNT(driver_id) AS total_delieveries')
    ->where('is_paid', 0)
    ->where('order_status', '5')
    ->whereBetween('created_at', [$first_Day, $last_Day])
    ->groupBy(DB::raw('DATE_FORMAT(created_at ,"%Y-%m-%d"),driver_id'));

$q = DB::table(DB::raw('('.$subQuery->toSql().') as o1'))
    ->selectRaw('o2.driver_id,total_delieveries,DATE_FORMAT(o1.created_at ,"%Y-%m-%d") AS created_at')
    ->join('orders as o2', 'o1.driver_id', '=', 'o2.driver_id')
    ->groupBy('o1.created_at')
    ->mergeBindings($subQuery)
    ->get();
Friday, August 13, 2021
 
kmunky
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :