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

Move preload SQL functions to new class

    XMLWordPrintable

    Details

    • Testing Instructions:
      Hide

      For the integrator:

      • Please review that there aren't remaining uses of the "old" methods being used in core. It's not crazy to imagine that some ongoing work has introduced new uses over the last days. A quick grep should be enough.

      Once the above is verified:

      • CI is your friend: Unit tests
      Show
      For the integrator: Please review that there aren't remaining uses of the "old" methods being used in core. It's not crazy to imagine that some ongoing work has introduced new uses over the last days. A quick grep should be enough. Once the above is verified: CI is your friend: Unit tests
    • Affected Branches:
      MOODLE_37_STABLE
    • Fixed Branches:
      MOODLE_37_STABLE
    • Pull from Repository:
    • Pull Master Branch:

      Description

      As discussed with Eloy Lafuente (stronk7), the new preload SQL functions are not well placed.

      This issue moves them to a new class at \core\dml\table which provides functions to generate the SQL field select, and to pull data for that table from a mixed result stdClass.

      In short, these functions are intended as helpers to allow you to select data from multiple tables into usable objects in a single query. This is something that we do regularly and we do it in a variety of painful ways.

      This is an attempt to produce a single solution which is reuable, and easy to read.

       
      $posttable = new \core\dml\table('forum_posts', 'p', 'p_');
      $discussiontable = new \core\dml\table('forum_discussions', 'd', 'd_');
      $forumtable = new \core\dml\table('forum', 'f', 'f_');
      $coursetable = new \core\dml\table('course', 'c', 'c_');
       
      $sql = "SELECT " .
          implode(', ', [
            $posttable->get_field_select(),
            $discussiontable->get_field_select(),
            $forumtable->get_field_select(),
            $coursetable->get_field_select(),
          ]) . " FROM " . $posttable->get_from_sql() .
               " JOIN " . $discussiontable->get_from_sql() . " ON d.id = p.discussion " .
               " JOIN " . $forumtable->get_from_sql() . " ON f.id = d.forum " .
               " JOIN " . $coursetable->get_from_sql() . " ON c.id = f.course " .
               " WHERE p.userid = :author";
       
      $data = $DB->get_record_sql($sql, ['author' => $USER->id]);
       
      foreach ($data as $datum) {
          $post = $posttable->extract_fields_from_result($data);
          $discussion = $discussiontable->extract_fields_from_result($data);
          $forum = $forumtable->extract_fields_from_result($data);
          $course = $coursetable->extract_fields_from_result($data);
      }
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                dobedobedoh Andrew Nicols
                Reporter:
                dobedobedoh Andrew Nicols
                Peer reviewer:
                Eloy Lafuente (stronk7)
                Integrator:
                Jun Pataleta
                Tester:
                CiBoT
                Participants:
                Component watchers:
                Jake Dallimore, Jun Pataleta, Ryan Wyllie, Andrew Nicols, Mathew May, Michael Hawkins, Shamim Rezaie, Simey Lameze
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  20/May/19

                  Time Tracking

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