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
How to Update Code That Uses the Deprecated Each() Function
When Should I Use Prepared Statements
Create Subdomains on the Fly With .Htaccess (PHP)
Remove a Child With a Specific Attribute, in Simplexml For PHP
Creating the Singleton Design Pattern in PHP5
Parse Error: Syntax Error, Unexpected End of File in My PHP Code
Replace Keys in an Array Based on Another Lookup/Mapping Array
How to Convert Xml into Array in PHP
How to Convert Json String to Array
PHP Display Image Blob from MySQL
What Are the PHP Operators "" and ":" Called and What Do They Do
PHP Fatal Error: Using $This When Not in Object Context
Passing Base64 Encoded Strings in Url
Why Can't I Run Two MySQLi Queries? The Second One Fails
Difference Between Require, Include, Require_Once and Include_Once