Laravel Eloquent Query: Using WHERE with OR AND OR?
Make use of Logical Grouping (Laravel 7.x/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);
});
Combining AND/OR eloquent query in Laravel
You can nest where clauses : http://laravel.com/docs/database/fluent#nested-where
Model::where(function($query)
{
$query->where('a', 'like', 'keyword');
$query->or_where('b', 'like', 'keyword');
})
->where('c', '=', '1');
This should produce : SELECT * FROM models WHERE (a LIKE %keyword% OR b LIKE %keyword%) AND c = 1
How to combine AND and multiple OR conditions in where clause
In order to group conditions you have to use a closure, you can read more here: https://laravel.com/docs/8.x/queries#or-where-clauses
\DB::table('list')
->where('column_A', '>', 100)
->where(function($q) use ($conditions) {
foreach ($conditions as $condition) {
$q->orWhere(...$condition);
}
});
Laravel - How to combine multiple queries as one Eloquent Query
in your Company Model you should have the relation:
public function HrLeaveRequests()
{
return $this->hasMany(HrLeaveRequest::class,'company_id');
}
now you could use withCount: $value=Company::where('company_id',$userCompany)->withCount(['HrLeaveRequests as allLeaves'=>function($query){
$query ->whereYear('created_at', date('Y'));
},
'HrLeaveRequests as pendingLeaves'=>function($query){
$query->where('leave_status', 1)->whereYear('created_at', date('Y'));
},
'HrLeaveRequests as rejectedLeaves'=>function($query){
$query->where('leave_status', 3)->whereYear('created_at', date('Y'));
},
'HrLeaveRequests as approvedLeaves'=>function($query){
$query->where('leave_status', 4)->whereYear('created_at', date('Y'));
},
])->get();
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-clausesIf 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');
})
How to combine WHERE clauses in Eloquent
To group where clauses like that, you need to pass a closure to the where()
method, and add your grouped conditions inside the closure. So, your code would look something like:
Table::where('status', 1)->where(function ($q) {
return $q->where('type', 2)->orWhere('type', 3)->orWhere('type', 4);
});
This will generate the SQL:SELECT * FROM tables WHERE status = 1 AND (type = 2 OR type = 3 OR type = 4)
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 ...)
How to combine these multiple queries in laravel eloquent
$rate = CpfRate::select('cpf_rates.*')
->where('cpf_scheme_id', '=', function ($query) use ($request) {
$query->select('id')
->from('cpf_schemes')
->where("cpf_citizenship_id", $request->cpf_citizenship_id)
->where('cpf_effective_date_id', '=', function($query, $currentDate) {
$query->select('id')
->from('cpf_effective_dates')
->where('effective_from', '<=', $currentDate)
->orderBy("effective_from", 'DESC')
->limit(1);
})
->first();
})
->where("minimum_wage", '<', ceil($totalWage)) // query does not accept floats. should be acceptable as wage tiers should be integers
->where("minimum_age", '<', $request->employee_age)
->orderBy('minimum_wage', 'DESC')
->orderBy('minimum_age', 'DESC')
->first();
I did not tested but what only here can be problem is $currentdate, so if it is problem just use Carbon class to get current date directly in query.
Laravel break and combine Eloquent query
You aren't saving the query result:
$result = $invoices->get();
dd($result);
Laravel 9 using AND or OR in combination which fails
You are missing the first where
about COLUMN1
isn't?
Currently, it produces:
SELECT * FROM table1 WHERE (column2 LIKE 'a' OR column3 LIKE 'b' OR column4 = 'c')
Update your code to include the first where
:DB::table('table1')
->where('column1', 'DATA')
->where(function ($query) use ($request) {
$query->where('column2', 'LIKE', $request->data)
->orWhere('column3', 'LIKE', $request->data )
->orWhere('column4', '=', $request->data ) ;
}
)->get();
And now, it should work as expected (at least, it will produce the query you are expecting).
Related Topics
PHP - Swiftmailer Using Starttls and Self Signed Certificates
How to Get the Absolute Path to the Public_HTML Folder
Why Is Object Oriented PHP with MySQLi Better Than the Procedural Approach
Changes to Upload_Max_Filesize in Ubuntu PHP.Ini Will Not Take Effect
Save Accents in MySQL Database
Newline Not Working in PHP Mail
Customizing My-Account Addresses Fields in Woocommerce 3
MySQL Insert ....On Duplicate Update - Adds One to the Autoincrement
How to Insert JSON Array into MySQL Database
How to Remove Text Between Tags in PHP
Callback Function Return Return($Var & 1)
Converting JSON to CSV Format Using PHP
Php: $_Server Variables: $_Server['Http_Host'] VS $_Server['Server_Name']