case listview - not sorting properly

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

I have a user that can not sort cases properly…
by date it simply does not work…

by case number does not work either.

Most other ok ( I checked a few) but she is having issues…

any idea?
…running 7.8.6

2

Could it be browser related? Can you check a different browser on the same computer?

3

Not browser related, happen with chrome and ie

but I just found out that it will show the problem only when looking at the cases assigned to a particular user…???
if we query cases assigned to any other user, the list is correct.

4

Strange…

I’m just guessing here, but maybe you can try narrowing it down to a single case? I suppose there could be some data in the case that is not getting processed correctly and breaks the sort.

Or you could try putting the logs in DEBUG mode and having a look at the queries generated, and then prehaps try them directly in MySQL to see how they sort.

5

yes I did put the system in debug got the sql query.

it is really amazing. I can not figure out how to solve this one…
here is the query (this is MSsql not mysql)…

DECLARE @topCount INT SET @topCount = 26 SELECT TOP (@topCount) * FROM
( SELECT  cases.id ,cases_cstm.gp_docnumber_c,cases_cstm.tproduct_c, cases.account_id , cases.assigned_user_id , cases.case_number , cases.name  ,
  accounts.name account_name , accounts.assigned_user_id account_name_owner  , N'Accounts' account_name_mod, cases.priority , cases.state , 
  cases.status , cases.type  , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner  , N'Users' assigned_user_name_mod,
  cases.date_entered , cases.created_by  , ROW_NUMBER()
                                    OVER (ORDER BY cases.date_entered ASC) AS row_number
                                    FROM cases  LEFT JOIN cases_cstm ON cases.id = cases_cstm.id_c   LEFT JOIN  accounts accounts ON cases.account_id=accounts.id AND accounts.deleted=0
AND accounts.deleted=0  LEFT JOIN  users jt1 ON cases.assigned_user_id=jt1.id AND jt1.deleted=0
AND jt1.deleted=0 where ((cases.status in (N'Open_New',N'Open_Assigned') ) AND ( cases.assigned_user_id in (N'25945e1e-7857-1699-f717-50366f501241')))  AND cases.deleted=0    ) AS a      WHERE row_number > 0

the query in Mssql management studio gives the same result…wrong order…
Now if you switch the cases.assigned_user_id to anyone else the order is correct!

I compared the user record via suitecrm and via straight sql (users and users_cstm) nothing catch my eye, it looks very similar to the others…

my test instance with data from august does not show the problem, only production environment
tomorrow I will try having more than one user in the “in” statement…
I will also restore the production database to my test instance to see if the problem follows data (which I expect)

6

At least, knowing the query is also broken in MSSQL excludes any SuiteCRM bug and makes your search for the problem much more focused.

Maybe you can try table repairs and/or index rebuilding.

Other than that, the approach I was thinking about - but I admit this can be difficult to do in practice - is to try and isolate a specific record causing the problem. So instead of looking at this as “it sorts incorrectly with this assigned user”, you would think “one (or more) of the rows returned with this assigned user cause an incorrect sort”.

To really pursue this hypothesis you would have to be able to experiment with your data, something that is probably only possible in a clone… you could try assigning all that user’s records to a different user, and see if the problem is “transferred” to the second user.

Then you can try assigning only half the records, and see which user (which half of the records) carries the problem with it. Then you keep dividing in half, and in a few iterations you should know exactly which row is faulty.

If this is caused by a faulty row, of course… that’s just an hypothesis. :slight_smile:

7

Facing same issue on sorting… any solution??

As i checked query is generating proper result in backend(when i manually running query in sql server), but when processing on listview it sorting wrong

8

Solution:

I really don’t know this is bug or something else, but i fixed in following way

  1. First Check which datbasemanager you are using in “include/database” directory, In my case it was mssqlmanager.php
  2. Edit the file, and check function limitQuery and handleUnionLimitQuery
  3. in same function wherever you find “WHERE row_number > $start” append with “order by row_number”.