Level 53
@xfirebg multiple where clauses will be combined with and operator. You want it to be combined with or operator:
->where('fullname', 'like', "%text1%")
->orWhere('fullname', 'like', "%tex2%")
->orWhere('fullname', 'like', "%text3%")
Mar 5, 2020
7
Level 3
Multiple "where like" not working
Hello. Anyone have idea for this: I have table
+-------------+----------------+---------------------+
| FullName | FormattedValue | RecordTime |
+-------------+----------------+---------------------+
| text1qwe | 170.01 | 09/02/2020 21:46:00 |
+-------------+----------------+---------------------+
| text2asd | 24.15 | 09/02/2020 21:45:00 |
+-------------+----------------+---------------------+
| text3asdd | 3.58 | 09/02/2020 21:44:00 |
+-------------+----------------+---------------------+
| text2asd | 15.60 | 09/02/2020 21:43:00 |
+-------------+----------------+---------------------+
| RcrdNotWant | 1 | 09/02/2020 21:42:00 |
+-------------+----------------+---------------------+
And code
function getdata(Request $request)
{
$start_date = date('d-m-Y 00:00:00');
$end_date = date('d-m-Y 23:59:59');
if($request->start_date != '' && $request->end_date != '')
{
// if user fill dates
$dateScope = array($request->start_date ." 00:00:00", $request->end_date ." 23:59:59");
} else {
// default load page - today
$dateScope = array($start_date, $end_date);
};
$students = Canal::whereBetween('recordtime', $dateScope)
->selectRaw('recordtime')
->selectRaw('max(formattedvalue) filter (where fullname = \'text1\') as clmnname1')
->selectRaw('max(formattedvalue) filter (where fullname = \'tex2\') as clmnname2')
->selectRaw('max(formattedvalue) filter (where fullname = \'text3\') as clmnname3')
->where('fullname', 'like', "%text1%")
->where('fullname', 'like', "%tex2%")
->where('fullname', 'like', "%text3%")
->groupBy('recordtime')
->orderBy('recordtime')
->get();
return Datatables::of($students)
->make(true);
}
The problem is that if I use more than 1 "where like". There is no restult. But if i use more than one "where not like" there is no problem???... I want to exclude records that i dont want like the last one in the table. In my real table the ar emultiple records that I dont want but I dont know their names. I want only to show the records that have text1,text2,text3 in the column "FullName".
Level 3
Thank for the Reply. I tryed this methd too. But htere is a bug with the time range. For today for example i dont have record but it gives me old data for today. And if I change time period it giving me same result...
And the "where not like" is working like how i espect with multiple "where"
I think the problem is Wherebetween that I use for the date range.
Level 56
Adding to @sti3bas response, you can use a closure to group the OR where clauses in a parenthesized group so it won't conflict with your other where conditions:
$students = Canal::whereBetween('recordtime', $dateScope)
->selectRaw('recordtime')
->selectRaw('max(formattedvalue) filter (where fullname = \'text1\') as clmnname1')
->selectRaw('max(formattedvalue) filter (where fullname = \'tex2\') as clmnname2')
->selectRaw('max(formattedvalue) filter (where fullname = \'text3\') as clmnname3')
->where(function ($query) {
// this closure will group the wheres inside it in a parenthesized group
$query->where('fullname', 'like', "%text1%");
$query->orWhere('fullname', 'like', "%tex2%");
$query->orWhere('fullname', 'like', "%text3%");
})
->groupBy('recordtime')
->orderBy('recordtime')
->get();
EDIT added missing semicolons
Level 3
Thanks for reply @rodrigo.pedra ATM with your code it giving me
syntax error, unexpected '$query' (T_VARIABLE)
I'm Working on it
Level 56
I missed the semicolons:
->where(function ($query) {
// this closure will group the wheres inside it in a parenthesized group
$query->where('fullname', 'like', "%text1%"); // missed semicolon
$query->orWhere('fullname', 'like', "%tex2%"); // missed semicolon
$query->orWhere('fullname', 'like', "%text3%"); // missed semicolon
})
Will edit the original snippet for future reference
Level 3
Now it's working great @rodrigo.pedra. Thanks a lot, both!
Level 56
Great! Glad to help
Please or to participate in this conversation.