Level 56
If an application is not linked to any executable it wont be returned (because of the join clause).
May 19, 2016
18
Level 1
->groupBy()->count() returns wrong value
I have problem joining other table, and grouping it by ID, Both queries returns same amount of collection, yet, if i try to get (count()), one which is grouped by, returns 18, while other returns 101, (I have 101 rows in database). Any ideas why count doesn't work properly, and how should I fix it?
$result = Application::where(function ($query) use ($string) {
$query->orWhere('name', 'LIKE', $string)->orWhere('description', 'LIKE', $string);
});
$result2 = Application::leftJoin('executables', 'executables.application_id', '=', 'applications.id')->where(function ($query) use ($string) {
$query->orWhere('applications.name', 'LIKE', $string)->orWhere('applications.description', 'LIKE', $string);
})->select('applications.*', 'executables.name AS exe_name')->groupBy("applications.id");
$total = $result->count();
$total2 = $result2->count("applications.id");
dd($total, $total2);
Level 1
@pmall Well, It's linked correctly, collection return 101, items, yet, count fails to return exact value, I know that all data is linked because: 1 I have UI, which display everything as it suppose to be. 2. Data is auto-generated for testing, and execuctales are linked to application via application_id, so there is no fails. It seems like there is some bug in eloquent, or I missing something out in context..
edit: also, to mention auto-generated data is exactly 101 application, and each application has random amount of executables between 10 and 20. I would like to state, there there is no way that application has at least 10 executables per application. Also, I tested in terminal, ID's are correct and there is nothing outside that range on database.
Level 56
Sorry but I don't understand your problem. Be more precise please.
Level 1
@pmall My problem is : I have 2 results, they return collection of 101 items, each result->count(), returns different integer, while it should be exact same.
count has to be 101, Now I get mystical 18 for $result2->count("applications.id");
Level 56
Must be a quirk somewhere in your code. What if you dd the results of both queries.
Level 1
@pmall I tried, dd, results, ->get(), ->toArray, ->count(); only count is different besides columns returned.
also I would like to state, that I pass empty string for filter, so it returns 100% of results from database. Again, both models, returns 101 item collection, yet both models returns different count, after adding that to end of the model builder. The code is above, if you see anything any quirk Please share it with me. Yet Honestly I don't think that's the case, because of collection item return.
Level 56
Show the output of toSql() method so we can see the difference between both queries
Level 1
"select * fromapplicationswhere (nameLIKE ? ordescription` LIKE ?)
limit 500
offset 0"
"select applications.*, executables.name as exe_name from applications
left join executables
on executables.application_id = applications.id
where (
applications.name LIKE ?
or executables.name LIKE ?
or applications.description LIKE ?)
group by applications.id
limit 500
offset 0" `
Level 56
The you are limiting the number of rows. Not surprising at all the count are different.
Level 1
@pmall It's not related in any way, reasons why.
- I limit after I retrieve all data, as stated several times..
- I limit by 500, and I have 101 rows of applications.
- I tried without limit it returns exact same result. So I'm not sure what's surprising for you..
Level 56
I limit by 500, and I have 101 rows of applications.
No because left join will return a line for each application/executables couples, so if it gets over 500 couples, not all the application will be present.
I limit after I retrieve all data
No as there is a limit clause in your query output.
Dont be agressive here, I just trying to get whats going on here.
Level 1
$applications2 = $result2->get();
$total2 = count($applications2); //returns correct value. I will assume there is bug in eloquent.
Level 56
I will assume there is bug in eloquent.
No, basic methods like this are well tested since a long time, there most likely be a problem with your query/usage of count.
Level 1
@pmall you have code I use above, light me up than...
Level 56
No all info is present, there's some limit clause coming from nowhere, don't know what is the content in the tables etc
Level 1
@pmall You did see the query outputs , what's wrong with you? Limit is used after I get count, so it's not related in any case. All code related with count is provided above. if you don't understand please move along. And don't waste my time with your ignorance.
Level 1
I know this is too late, but just in case anyone bump into the same problem, this actually has to do with the mysql query build under the hood. When there is a query with ->groupBy() and ->count(), you are expecting the query to be something like SELECT COUNT(*) FROM (SELECT ... FROM ... GROUP BY ...) t, but the actual query built is SELECT COUNT(*) FROM ... GROUP BY ... which yield different result.
To solve this, you can use DB::query()->fromSub($query, 't')->count() or $query->toBase()->getCountForPagination() as suggested https://github.com/laravel/framework/issues/22883#issuecomment-438521002
4 likes
Level 1
@ykchan Thanks! You've actually made my day.
Please or to participate in this conversation.