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

Enable performant $DB recursive queries (CTE, Hierarchical sql, Connect by, etc)

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: Future Dev
    • Fix Version/s: None
    • Component/s: Database SQL/XMLDB

      Description

      There are a couple pages especially involving nested course categories where on big sites it easy to rack up 100's or even 1000 db calls to the same table when you have a deeply nested category structure.

      Most DB's have supported recursive or hierarchical sql for ages and for those that don't the DB method itself could default back to a recursive call internally and produce results in the same way.

      Something like:

      get_records_recursive($table, $connectclause, array $conditions=null,
          $sort='', $fields='*', $maxdepth=0) { 
      

      All implementations should explicitly forbid circular graphs and just stop when they are detected and still return valid results (with a dev debug).

      Sort would only be for siblings at the same leaf level in the tree structure.

      Maybe another optional chunk of tables you can join into the primary table if needed.

      Internally I'd have an implicit 'parent' and 'child' for the connecting join. Maybe have the option to override those table aliases if we need to.

      I'd also include an implicit depth column for free.

      $cats = $DB->get_records_recursive('course_categories', 'parent.id = child.parent', null, 'sortorder');
      

       

       

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            brendanheywood Brendan Heywood
            Participants:
            Component watchers:
            Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated: