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

Adding both user/course tag elements to report causes errors

    XMLWordPrintable

Details

    Description

      Report builder course & user entities both now report on tags (MDL-73842). There is a problem however when a report uses both entities (e.g. the Badges report source: MDL-73988) and tries to report on tags data from each:

      This is because each uses the same default aliases for the {tag} and {tag_instance} tables. There are two solutions:

      1. All calling code that uses both the course and user entities to call set_table_alias(...) on the entities to resolve any clashes manually; or
      2. Change the default table aliases for both, making it transparent to calling code (this is what is done for the {context} table alias in each entity too)

      dmlreadexception
      Error reading from database
      File: /lib/dml/moodle_database.php
      Line: 486
      Stack trace:
       
      Not unique table/alias: 'ti'
       
                  SELECT COUNT(1)
                    FROM (SELECT b.name AS c0_name, c.fullname AS c1_fullname, c.id AS c1_courseid, cctx.id AS c1_ctxid, cctx.path AS c1_ctxpath, cctx.depth AS c1_ctxdepth, cctx.contextlevel AS c1_ctxlevel, cctx.instanceid AS c1_ctxinstance, cctx.locked AS c1_ctxlocked, t.name AS c2_name, t.rawname AS c2_rawname, u.firstname AS c3_firstname, u.middlename AS c3_middlename, u.lastname AS c3_lastname, t.name AS c4_name, t.rawname AS c4_rawname
                            FROM mdl_badge b LEFT JOIN mdl_course c
                      ON c.id = COALESCE(b.courseid, 1) LEFT JOIN mdl_context cctx
                                 ON cctx.contextlevel = 50
                                AND cctx.instanceid = c.id LEFT JOIN mdl_tag_instance ti
                          ON ti.component = 'core'
                         AND ti.itemtype = 'course'
                         AND ti.itemid = c.id LEFT JOIN mdl_tag t
                          ON t.id = ti.tagid LEFT JOIN mdl_badge_issued bi
                      ON bi.badgeid = b.id LEFT JOIN mdl_user u
                      ON u.id = bi.userid LEFT JOIN mdl_tag_instance ti
                          ON ti.component = 'core'
                         AND ti.itemtype = 'user'
                         AND ti.itemid = u.id
                           WHERE 1=1
                                 
                         ) rbalias33
      [array (
      )]
      Error code: dmlreadexception
      * line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
      * line 1273 of /lib/dml/mysqli_native_moodle_database.php: call to moodle_database->query_end()
      * line 1671 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
      * line 1744 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
      * line 1954 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
      * line 170 of /reportbuilder/classes/table/base_report_table.php: call to moodle_database->count_records_sql()
      * line 2132 of /lib/tablelib.php: call to core_reportbuilder\table\base_report_table->query_db()
      * line 262 of /lib/table/classes/external/dynamic/get.php: call to table_sql->out()
      * line 261 of /lib/externallib.php: call to core_table\external\dynamic\get::execute()
      * line 81 of /lib/ajax/service.php: call to external_api::call_external_function()
      

      Attachments

        Issue Links

          Activity

            People

              pholden Paul Holden
              pholden Paul Holden
              David Carrillo David Carrillo
              Jun Pataleta Jun Pataleta
              CiBoT CiBoT
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 45 minutes
                  45m

                  Clockify

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