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

Allow various SQL compatibility functions to be written in the sql itself similar to mustache helpers

    XMLWordPrintable

Details

    • Improvement
    • Status: Open
    • Minor
    • Resolution: Unresolved
    • Future Dev
    • None
    • Database SQL/XMLDB
    • None

    Description

      The rough idea is we have some sql like this which is written for postgres:

      SELECT
      STRING_AGG(u.firstname || ' ' || u.lastname, ',') creators
      FROM {user}
      

      This sql could be in code somewhere, or in a customsql report. But it's gonna break when we run it on mysql or oracle. The current correct way to fix it is to rip the sql apart and call

      $sqlchunk = sql_group_concat(u.firstname || ' ' || u.lastname, ',')
      $sql = '....' $sqlchunk . '....';

      I'm just imagining a better world where we can do this all in "sql" and the dml libraries internally translate it into the right sql:

      SELECT
      group_concat(u.firstname || ' ' || u.lastname, ',') creators
      FROM {user}
      

      the exact syntax doesn't matter, it might need to have some sort of tokens to make it all work: 

      SELECT
      {{group_concat(u.firstname || ' ' || u.lastname, ',')}} creators
      FROM {user}
      

      This would make a lot of code much easier and more natural to write. This would apply to a bunch of the sql_* helpers functions

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              brendanheywood Brendan Heywood
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated: