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

Remove duplicate column/filter join on user profile fields in custom reports

XMLWordPrintable

    • Icon: Improvement Improvement
    • Resolution: Fixed
    • Icon: Minor Minor
    • 4.1
    • 4.1
    • Report builder
    • MOODLE_401_STABLE
    • MOODLE_401_STABLE
    • Hide
      1. Log in as admin
      2. Navigate to Users > Accounts > User profile fields in site administration
      3. Create two new Text input profile fields:
        • Short name / Name: PField1
        • Short name / Name: PField2
      4. Select Profile from user menu
      5. Press Edit profile
        • PField1: Orange
        • PField2: Carrot
        • Save
      6. Create a new user:
        • PField1: Red
        • PField2: Apple
        • Save
      7. Navigate to Development > Debugging in site administration
      8. Set Debug messages to Developer
      9. Navigate to Reports from user menu
      10. Create new report from Users report source
        • Ensure Include default setup is unchecked
      11. In report editor, add the following columns:
        • User > First name
        • User > PField2
      12. Confirm first name of each user, along with value of PField2 profile field is shown
      13. Add User > PField2 condition
        • Is equal to: Carrot
        • Apply
      14. Confirm only admin user & Carrot are shown
      15. Expand report Debug info
      16. Confirm the {user_info_data} table is joined only once in the report
      17. Add User > PField1 column
      18. Confirm admin user, Carrot and Orange are shown
      19. Expand report Debug info
      20. Confirm the {user_info_data} table is now joined twice in the report (once per profile field)
      21. Add User > PField1 condition
        • Is equal to: Orange
        • Apply
      22. Confirm admin user, Carrot and Orange are shown
      23. Expand report Debug info
      24. Confirm the {user_info_data} table is still joined twice in the report (once per profile field)
      Show
      Log in as admin Navigate to Users > Accounts > User profile fields in site administration Create two new Text input profile fields: Short name / Name: PField1 Short name / Name: PField2 Select Profile from user menu Press Edit profile PField1: Orange PField2: Carrot Save Create a new user: PField1: Red PField2: Apple Save Navigate to Development > Debugging in site administration Set Debug messages to Developer Navigate to Reports from user menu Create new report from Users report source Ensure Include default setup is unchecked In report editor, add the following columns: User > First name User > PField2 Confirm first name of each user, along with value of PField2 profile field is shown Add User > PField2 condition Is equal to: Carrot Apply Confirm only admin user & Carrot are shown Expand report Debug info Confirm the { user_info_data } table is joined only once in the report Add User > PField1 column Confirm admin user, Carrot and Orange are shown Expand report Debug info Confirm the { user_info_data } table is now joined twice in the report (once per profile field) Add User > PField1 condition Is equal to: Orange Apply Confirm admin user, Carrot and Orange are shown Expand report Debug info Confirm the { user_info_data } table is still joined twice in the report (once per profile field)

      See following report:

      Generated SQL:

      SELECT 
        u.firstname AS c0_firstname, 
        u.middlename AS c0_middlename, 
        u.lastname AS c0_lastname, 
        rbalias1.data AS c1_data 
      FROM 
        {user} u 
        LEFT JOIN {user_info_data} rbalias1 ON rbalias1.userid = u.id 
        AND rbalias1.fieldid = 2 
        LEFT JOIN {user_info_data} rbalias17 ON rbalias17.userid = u.id 
        AND rbalias17.fieldid = 2 
      WHERE 
        u.id != :rbparam0 
        AND u.deleted = 0 
        AND (
          COALESCE(rbalias17.data, :rbparam7) = :rbparam13
        )
      

      The two joins on the {user_info_data} table are identical, and ought to be normalised away

      Setting as blocked by MDL-74140 so we have an easier means of debugging reports

        1. 1_MDL-75733.png
          1_MDL-75733.png
          13 kB
        2. 2_MDL-75733.png
          2_MDL-75733.png
          51 kB
        3. 3_MDL-75733.png
          3_MDL-75733.png
          57 kB
        4. 4_MDL-75733.png
          4_MDL-75733.png
          68 kB
        5. Screenshot_2022-09-13 User profile fields dup column filter.png
          Screenshot_2022-09-13 User profile fields dup column filter.png
          55 kB

            pholden Paul Holden
            pholden Paul Holden
            David Carrillo David Carrillo
            Victor Déniz Falcón Victor Déniz Falcón
            John Edward Pedregosa John Edward Pedregosa
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 1 hour, 55 minutes
                1h 55m

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