[#22891] - Wrong count result when using select distinct in query for BaseDatabaseModel::_getListCount
- Closed
- 20 Dec 2018
- Low
- Build: 3.9.0
- # 22891
Steps to reproduce the issue
- In a model used to feed a list, build a query with a select distinct clause
- Call getItems ensuring that all rows are returned (pe. set list.limit to 1000)
- Call getTotal
Expected result
- The row count of getItems must be equal to the getTotal result
Actual result
- The row count of getItems is lower than getTotal result
System information (as much as possible)
- Ubuntu Linux
- MySQL 5.6
- PHP 7.1
- Joomla! 3.9 (not checked on earlier versions)
Additional comments
In BaseDatabaseModel.php, at line 331 in Joomla 3.9, the clausules of the query used in getItems are cleared and a select count(*) is added, causing the wrong result, as it is not the same SELECT COUNT(*) than SELECT COUNT(DISTINCT id)).
IMHO there are two options to solve that:
- Try to find the primary key of the involved tables, which can be tricky or impossible (views, joins, ...).
- If a distinct is present execute the fallback present in the same function.
Workaraound
The _getListCount function can be overrided in descendant classes when needed.
The way people would usually do this is by overriding the relevant methods in their model ...
either the public method
getTotal() method
or the protected method
_getListCount()
you are not obliged to use the default implementation,
that's why this method has protected access and not private access
That is what i am doing myself
Still i think something needs to be documented at the description of _getListCount() of BaseDatabaseModel about this
brianteeman
- | Labels |
Added:
J3 Issue
|
||
| Status | New | ⇒ | Closed |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2018-12-20 16:31:41 |
| Closed_By | ⇒ | joomla-cms-bot |
Quy
- | Closed_By | joomla-cms-bot | ⇒ | Quy |
Set to "closed" on behalf of @Quy by The JTracker Application at issues.joomla.org/joomla-cms/22891
Personally, I'd suggest that this should be what happens when you start reaching more complex cases such as the one you're describing. The method is intended to cover the majority of common use cases, while I don't doubt that use of DISTINCT might be a frequent thing changing the query building logic really seems like something that should be taken care of in the model that needs the specialized behavior versus trying to change the core framework and affecting all query building logic.