How to Create Multiple Where Clause Query Using Laravel Eloquent

How to Create Multiple Where Clause Query Using Laravel Eloquent?

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 ...)

Laravel eloquent multiple WHERE with OR AND OR and LIKE?

Use like and not like with % in where() and orWhere() methods:

->where('column', 'like', '%pattern%')

https://laravel.com/docs/5.3/queries#where-clauses

If you need to use multiple AND do like this:

->where($condition)
->where($anotherCondition)

If you want to use OR do this:

->where($condition)
->orWhere($anotherCondition)

To combine multiple AND and OR do parameter grouping:

->where('name', '=', 'John')
->orWhere(function ($query) {
$query->where('votes', '>', 100)
->where('title', '<>', 'Admin');
})

Laravel multiple WHERE clauses

You have 2 possible solutions.

Instead of:

->where(['rowstate', '<>', 'Ready'], ['DATE_FORMAT(due_date, "%d-%m-%y")', '<', $today_])

you can use

->where('rowstate','<>','Ready')->where(DB::raw('DATE_FORMAT(due_date, "%d-%m-%y"','<', $today_);

or

you can use array syntax like this:

->where([
['rowstate', '<>', 'Ready'],
[DB::raw('DATE_FORMAT(due_date, "%d-%m-%y")'), '<', $today_]
]);

So to sum up you were missing enclosing your data into outer array, and you need to use DB::raw in case you don't use raw column names

Eloquent Laravel multiple like where and where clause

You can define a function for where clause, like this:

$locs = DB::table('operatories as op')
->leftJoin(DB::raw('locations al'), function($join) {
$join->on('op.Location', '=', 'al.location');

})
->where(function($q) {
$q->where('op.opname','LIKE','%NP%');
$q->orWhere('op.opname','LIKE','%OPEN%');
})
->where('al.isOfficeClosed', '=','0');

By the way,

select * from operatories op
left join locations al on op.location = al.location
where op.opname like '%NP%' or op.opname like '%OPEN%'
and al.isOfficeClosed = 0;

is different than (mind the parenthesis)

select * from operatories op
left join locations al on op.location = al.location
where (op.opname like '%NP%' or op.opname like '%OPEN%')
and al.isOfficeClosed = 0;

Multiple where clause, Laravel

The Problem is, a varchar that is null is null and not a varchar with a value. So it can not be checked with = or != 'string'. So you have to check if answer_type is != skipped or null

$history = AnswerHistory::where('question_id', '=', $id)
->where(function ($query) {
$query->where('answer_type', '!=', "skipped")
->orWhereNull('answer_type');
})
->get();

laravel eloquent with multiple where conditions issue

try changing your eloquent to this,

$get_monthly_payments_lastmonth=AppPayment::where('payment_annum','=','monthly')
->whereMonth('created_at', '=', Carbon::now()->subMonth()->month)
->get();

this would give give you the last month records where the payment_annum is monthly

Laravel collection multiple where conditions

Collection where method doesn't accept an array of conditions like eloquent does. But you can chain multiple where conditions.

return $collection->where('destination.country', 'china')
->where('doc.description', 'business');

Example

$data = [
['name' => 'john', 'email' => 'john@gmail.com'],
['name' => 'john', 'email' => 'jim@gmail.com'],
['name' => 'kary', 'email' => 'kary@gmail.com'],
];

$collection = collect($data);

$result = $collection->where('name', 'john');
// [{"name":"john","email":"john@gmail.com"},{"name":"john","email":"jim@gmail.com"}]


$result = $collection->where('name', 'john')->where('email', 'john@gmail.com');
// [{"name":"john","email":"john@gmail.com"}]

How to add multiple where clause on Eloquent ORM Laravel?

Just add one more where.

$testq= DB::table('attendances')
->where('user_id', '=', $userinput)
->where('logon', '=', $newdate)
->get();

http://laravel.com/api/4.2/Illuminate/Database/Eloquent/Builder.html#method_where

$this where(string $column, string $operator = null, mixed $value =
null, string $boolean = 'and')

Add a basic where clause to the query.



Related Topics



Leave a reply



Submit