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

Duplicate custom field alias when user entity is reused with custom profile fields

XMLWordPrintable

    • MOODLE_401_STABLE
    • MOODLE_401_STABLE
    • MDL-77705-401
    • Hide
      1. Log in as admin
      2. Navigate to Development > Debugging in site administration
      3. Set Debug messages to Developer
      4. Navigate to Users > Accounts > User profile fields in site administration
      5. Create a new Text input profile field
      6. Edit your own profile
      7. Set the test field value to "One" and save
      8. Create a test user
        • Set the test field value to "Two"
      9. Create a course and enrol test user.
      10. Go to the Participants page of the course.
      11. Navigate to profile of test user (from the Participants page of the course)
      12. Press Notes > Add a new note
      13. Enter some content and save
      14. Navigate to Reports from user menu
      15. Create new report from Notes report source
      16. Include default setup = NO
      17. In report editor add the following columns:
        • Note > Content
        • Recipient > Full name
        • Recipient > Test field
        • Author > Full name
        • Author > Test field
      18. Confirm the report displays correct data for the note previously created (test user/field and admin user/field values are as intended)
      19. Add the following condition:
        • Recipient > Test field: Is equal to Two
      20. Press Apply
      21. Add the following condition:
        • Author > Test field: Is equal to One
      22. Press Apply
      23. Confirm that with the conditions both applied the same note you previously created is still being reported on
      24. Expand report Debug info
      25. Confirm the {user_info_data} table is joined twice in the report (once per profile field)
      Show
      Log in as admin Navigate to Development > Debugging in site administration Set Debug messages to Developer Navigate to Users > Accounts > User profile fields in site administration Create a new Text input profile field Edit your own profile Set the test field value to "One" and save Create a test user Set the test field value to "Two" Create a course and enrol test user. Go to the Participants page of the course. Navigate to profile of test user (from the Participants page of the course) Press Notes > Add a new note Enter some content and save Navigate to Reports from user menu Create new report from Notes report source Include default setup = NO In report editor add the following columns: Note > Content Recipient > Full name Recipient > Test field Author > Full name Author > Test field Confirm the report displays correct data for the note previously created (test user/field and admin user/field values are as intended) Add the following condition: Recipient > Test field: Is equal to Two Press Apply Add the following condition: Author > Test field: Is equal to One Press Apply Confirm that with the conditions both applied the same note you previously created is still being reported on Expand report Debug info Confirm the { user_info_data } table is joined twice in the report (once per profile field)

      There is an edge case where the user entity is used twice in the same report (only example being the notes report source currently), and there is a custom user field that is added to the report, either as a column or condition/filter, for both entity instances

      It leads to the following DML exception, because each tries to use the same table alias:

      dmlreadexception
      		 
      Error reading from database
      		 
      File: /lib/dml/moodle_database.php
      		 
      Line: 494
      		 
      Stack trace:
      		 
       
      		 
      Not unique table/alias: 'upfs3'
      		 
       
      		 
                  SELECT COUNT(1)
      		 
                    FROM (SELECT np.content AS c0_content, np.format AS c0_format, u.firstname AS c1_firstname, upfs3.data AS c2_data, au.firstname AS c3_firstname, upfs3.data AS c4_data
      		 
                            FROM mdl_post np LEFT JOIN mdl_user u
      		 
                         ON u.id = np.userid LEFT JOIN mdl_user_info_data upfs3
      		 
                             ON upfs3.userid = u.id
      		 
                            AND upfs3.fieldid = 3 LEFT JOIN mdl_user au
      		 
                         ON au.id = np.usermodified LEFT JOIN mdl_user_info_data upfs3
      		 
                             ON upfs3.userid = au.id
      		 
                            AND upfs3.fieldid = 3
      		 
                           WHERE np.module = ? AND (COALESCE( CAST(upfs3.data AS SIGNED) , 0) = ?) AND (COALESCE( CAST(upfs3.data AS SIGNED) , 0) = ?)
      		 
                                 
                         ) rbalias16
      		 
      [array (
      		 
        0 => 'notes',
      		 
        1 => 0,
      		 
        2 => 0,
      		 
      )]
      		 
      Error code: dmlreadexception
      		 
      * line 494 of /lib/dml/moodle_database.php: dml_read_exception thrown
      		 
      * line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
      		 
      * line 1371 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->query_end()
      		 
      * line 1679 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
      		 
      * line 1752 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
      		 
      * line 1962 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
      		 
      * line 177 of /reportbuilder/classes/table/base_report_table.php: call to moodle_database->count_records_sql()
      		 
      * line 371 of /reportbuilder/classes/table/custom_report_table.php: call to core_reportbuilder\table\base_report_table->query_db()
      		 
      * line 85 of /reportbuilder/classes/output/renderer.php: call to core_reportbuilder\table\custom_report_table->out()
      		 
      * line 495 of /lib/outputrenderers.php: call to core_reportbuilder\output\renderer->render_custom_report_table()
      		 
      * line 191 of /reportbuilder/classes/external/custom_report_exporter.php: call to plugin_renderer_base->render()
      		 
      * line 121 of /lib/classes/external/exporter.php: call to core_reportbuilder\external\custom_report_exporter->get_other_values()
      		 
      * line 73 of /reportbuilder/classes/output/custom_report.php: call to core\external\exporter->export()
      		 
      * line 50 of /reportbuilder/classes/output/dynamictabs/editor.php: call to core_reportbuilder\output\custom_report->export_for_template()
      		 
      * line 87 of /lib/classes/external/dynamic_tabs_get_content.php: call to core_reportbuilder\output\dynamictabs\editor->export_for_template()
      		 
      * line ? of unknownfile: call to core\external\dynamic_tabs_get_content::execute()
      		 
      * line 251 of /lib/external/classes/external_api.php: call to call_user_func_array()
      		 
      * line 83 of /lib/ajax/service.php: call to core_external\external_api::call_external_func

      Caused by MDL-75733

        1. 401.png
          289 kB
          Ron Carl Alfon Yu
        2. master.png
          278 kB
          Ron Carl Alfon Yu

            pholden Paul Holden
            pholden Paul Holden
            Carlos Castillo Carlos Castillo
            Sara Arjona (@sarjona) Sara Arjona (@sarjona)
            Ron Carl Alfon Yu Ron Carl Alfon Yu
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated:
              Resolved:

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 4 hours, 12 minutes
                4h 12m

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