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

Remove duplicate column/filter join on custom fields in reports

    XMLWordPrintable

Details

    • Improvement
    • Resolution: Fixed
    • Minor
    • 4.3
    • 4.1, 4.3
    • Report builder
    • MOODLE_401_STABLE, MOODLE_403_STABLE
    • MOODLE_403_STABLE
    • Hide
      1. Log in as admin
      2. Navigate to Courses > Course custom fields in site administration
      3. Press Add a new category if none exists
      4. Create two new Short text fields:
        • Name / Short name: cfield1
        • Name / Short name: cfield2
      5. Create a new course
        • Course full name: C1
        • cfield1: Orange
        • cfield2: Carrot
      6. Create a second course:
        • Course full name: C2
        • cfield1: Red
        • cfield2: Apple
      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 Courses report source
        • Ensure Include default setup is unchecked
      11. In report editor, add the following columns:
        • Course > Course full name
        • Course > cfield2
      12. Confirm full name of each course, along with value of cfield2 field is shown
      13. Add Course > cfield2 condition
        • Is equal to: Carrot
        • Apply
      14. Confirm only first course(C1) & Carrot are shown
      15. Expand report Debug info
      16. Confirm the {customfield_data} table is joined only once in the report
      17. Add Course > cfield1 column
      18. Confirm first course(C1), Carrot and Orange are shown
      19. Expand report Debug info
      20. Confirm the {customfield_data} table is now joined twice in the report (once per field)
      21. Add Course > cfield1 condition
        • Is equal to: Orange
        • Apply
      22. Confirm first course, Carrot and Orange are shown
      23. Expand report Debug info
      24. Confirm the {customfield_data} table is still joined twice in the report (once per profile field)
      Show
      Log in as admin Navigate to Courses > Course custom fields in site administration Press Add a new category if none exists Create two new Short text fields: Name / Short name: cfield1 Name / Short name: cfield2 Create a new course Course full name: C1 cfield1: Orange cfield2: Carrot Create a second course: Course full name: C2 cfield1: Red cfield2: Apple Navigate to Development > Debugging in site administration Set Debug messages to Developer Navigate to Reports from user menu Create new report from Courses report source Ensure Include default setup is unchecked In report editor, add the following columns: Course > Course full name Course > cfield2 Confirm full name of each course, along with value of cfield2 field is shown Add Course > cfield2 condition Is equal to: Carrot Apply Confirm only first course(C1) & Carrot are shown Expand report Debug info Confirm the { customfield_data } table is joined only once in the report Add Course > cfield1 column Confirm first course(C1), Carrot and Orange are shown Expand report Debug info Confirm the { customfield_data } table is now joined twice in the report (once per field) Add Course > cfield1 condition Is equal to: Orange Apply Confirm first course, Carrot and Orange are shown Expand report Debug info Confirm the { customfield_data } table is still joined twice in the report (once per profile field)

    Description

      This is the same as MDL-75733, but for custom fields (e.g. in courses)

      When a custom field is used a both a column and a filter in a custom report, each join on the {customfield_data} table is identical, and ought to be normalised away

      SELECT 
        c.fullname AS c0_fullname, 
        c.id AS c0_courseid, 
        cctx.id AS c0_ctxid, 
        cctx.path AS c0_ctxpath, 
        cctx.depth AS c0_ctxdepth, 
        cctx.contextlevel AS c0_ctxlevel, 
        cctx.instanceid AS c0_ctxinstance, 
        cctx.locked AS c0_ctxlocked, 
        rbalias3.charvalue AS c1_charvalue, 
        rbalias3.id AS c1_id, 
        rbalias3.contextid AS c1_contextid 
      FROM 
        {course} c 
        LEFT JOIN {context} cctx ON cctx.contextlevel = 50 
        AND cctx.instanceid = c.id 
        LEFT JOIN {customfield_data} rbalias3 ON rbalias3.fieldid = 8 
        AND rbalias3.instanceid = c.id 
        LEFT JOIN {customfield_data} rbalias10 ON rbalias10.fieldid = 8 
        AND rbalias10.instanceid = c.id 
      WHERE 
        c.id != :rbparam0 
        AND (
          rbalias10.charvalue LIKE :rbparam1 ESCAPE '\\'
        )
      

      Attachments

        Issue Links

          Activity

            People

              pholden Paul Holden
              pholden Paul Holden
              Carlos Castillo Carlos Castillo
              David Carrillo David Carrillo
              Kim Jared Lucas Kim Jared Lucas
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 3 hours, 31 minutes
                  3h 31m

                  Clockify

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