Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-75170

Improve sorting of Null values in reportbuilder

XMLWordPrintable

    • MOODLE_400_STABLE, MOODLE_401_STABLE
    • MOODLE_401_STABLE
    • MDL-75170-master-add_order_by_method
    • Hide

      Note: Test on all supported database types, if possible, or at least on PostgreSQL and MySQL

      1. Create a new empty Moodle site
      2. Create three new user accounts, username 'learner1' 'learner2' 'learner3'
      3. Set the middle name of learner1 to 'AKL' and learner2 to 'WLG'
      4. Update the database manually to set the admins middlename to null:  

        update mdl_user set middlename = null where username = 'admin';

      5. Back in Moodle, create a custom report using the users report source. Untick include default setup
      6. Add the username and middlename columns
      7. Sort by middlename ascending, verify table is sorted like this:
        username middlename
        admin  
        learner3  
        learner1 AKL
        learner2 WLG
      1. Sort the middle name descending and verify the table:
        username middlename
        learner2 WLG
        learner1 AKL
        learner3  
        admin  
      Show
      Note: Test on all supported database types, if possible, or at least on PostgreSQL and MySQL Create a new empty Moodle site Create three new user accounts, username 'learner1' 'learner2' 'learner3' Set the middle name of learner1 to 'AKL' and learner2 to 'WLG' Update the database manually to set the admins middlename to null:  update mdl_user set middlename = null where username = 'admin' ; Back in Moodle, create a custom report using the users report source. Untick include default setup Add the username and middlename columns Sort by middlename ascending, verify table is sorted like this: username middlename admin   learner3   learner1 AKL learner2 WLG Sort the middle name descending and verify the table: username middlename learner2 WLG learner1 AKL learner3   admin  

      Note: the final decision was made to implement this in base table libraries, rather than in report builder only - see comments

      Postgres treats null values as if larger than any non-null value - this is not natural for most reports based on counts/integers etc.

      For example if you are showing a report with the number of views a user has, but one user doesn't have any - and the result returns "null" - ordering by the views DESC will order the null value at the top.

      user | views

      dan     null
      john      5
      simon   3
      bob       1

      It's not easy to change this..

      we could dig into the tablelib.php class and change the default like this:

      --- a/lib/tablelib.php
      		 
      +++ b/lib/tablelib.php
      		 
      @@ -601,9 +601,9 @@ class flexible_table {
      		 
                       $column = $DB->sql_order_by_text($column);
      		 
                   }
      		 
                   if ($order == SORT_ASC) {
      		 
      -                $bits[] = $column . ' ASC';
      		 
      +                $bits[] = $column . ' ASC NULLS FIRST';
      		 
                   } else {
      		 
      -                $bits[] = $column . ' DESC';
      		 
      +                $bits[] = $column . ' DESC NULLS LAST';
      		 
                   }
      		 
               }

      but that affects a lot of different reports, is not cross-db compatible, and in some cases sorting null using the default option may in fact be useful...

      I think the reportbuilder class should implement it's own sort handling (rather than relying on tablelib.php) and allow a report datasource or entity to define how the null values are sorted - then have some form of cross-db support to handle it.

        1. 1_MDL-75170_master_mysql.png
          1_MDL-75170_master_mysql.png
          54 kB
        2. 1_MDL-75170_master_postgre.png
          1_MDL-75170_master_postgre.png
          51 kB
        3. 2_MDL-75170_master_mysql.png
          2_MDL-75170_master_mysql.png
          59 kB
        4. 2_MDL-75170_master_postgre.png
          2_MDL-75170_master_postgre.png
          56 kB

            alexmorris Alexander Morris
            danmarsden Dan Marsden
            Paul Holden Paul Holden
            Jun Pataleta Jun Pataleta
            John Edward Pedregosa John Edward Pedregosa
            Votes:
            1 Vote for this issue
            Watchers:
            9 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - 0 minutes
                0m
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 1 day, 6 hours, 41 minutes
                1d 6h 41m

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.