I m counting records on the base of user id but giving different results. running two query on is single send in foreach but counting result is different. when i m counting each records of second it gives 3705 and first single query with whereIn it gives me 3692. how can i find the issue.
$users = [248, 255, 260, 299, 300, 301, 259];
$tlw_count = [];
// Single querry Count giving 3692
$tlw = \App\Models\Lead::whereHas('LeadTasks', function ($query) use ($users) {
$query->where('internal_inquiry_status_id', '!=', 3)
->whereIn('created_by', $users);
})->count();
// Foreach loop for individual count giving 3705
foreach ($users as $value) {
$tlw_counted = \App\Models\Lead::whereHas('LeadTasks', function ($query) use ($value) {
$query->where('internal_inquiry_status_id', '!=', 3)
->where('created_by', $value);
})->count();
$tlw_count[$value] = $tlw_counted;
}
dd($tlw_count, $tlw);
Of course it does, the second runs queries for each or the users, while the first only queries where the user id is in that array, so it doesn't query on them all.
You are counting to completely different things, the first counts the results, the other executions in the foreach.
The second approach is a bad practice, it is way slower, uses more queries to the database.
@Tray2 but second query giving right result from the database.
It would be nice if you did a toSql on these (I don't remember how to get bindings) and show the two queries.
@jlrdw here the queries convert by toSql, Single query,
select * from leads where exists (select * from lead_tasks where leads.id = lead_tasks.lead_id and
internal_inquiry_status_id != 3 and created_by in (248, 255, 260, 299, 300, 301, 259));
individual query,
select * from leads where exists (select * from lead_tasks where leads.id =
lead_tasks.lead_id and internal_inquiry_status_id != 3 and created_by = 248);
@fahdshaykh You most likely have some lead_tasks that has more than one user assigned to it, the first query only return the first match, that is the way exists works.
@Tray2 for best practice how can i run single query and fetch all records while when using foreach for users it giving rights results.
@fahdshaykh hard to say exactly since I don't know what the data looks like and what the desired result is.
Please or to participate in this conversation.