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

Query to get a list of tags in block_blog_tags.php broken with PostgreSQL

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Trivial
    • Resolution: Fixed
    • Affects Version/s: 1.6.1
    • Fix Version/s: 1.6.4, 1.7
    • Component/s: Blog
    • Labels:
      None
    • Environment:
      All
    • Database:
      PostgreSQL
    • Affected Branches:
      MOODLE_16_STABLE
    • Fixed Branches:
      MOODLE_16_STABLE, MOODLE_17_STABLE

      Description

      The SQL query used in block_blog_tags.php to return a list of tags doesn't work with PostgreSQL. The first problem is the quoted values site or public. PostgreSQL expects these to be single-quoted. The second problem is the GROUP by clause, which errors if all the columns in tags aren't referred to.

      The first fix is trivial. The second may possibly change the behaviour of the query. In any case, here's the changed query that works:

      $sql = 'SELECT t.*, COUNT(DISTINCT bt.id) as ct ';

      $sql .= FROM {$CFG->prefix}tags as t, {$CFG->prefix}blog_tag_instance as bt, {$CFG->prefix}post as p ;

      $sql .= 'WHERE t.id = bt.tagid ';

      $sql .= 'AND p.id = bt.entryid ';

      $sql .= 'AND (p.publishstate = 'site' or p.publishstate='public') ';

      $sql .= AND bt.timemodified > {$timewithin} ;

      $sql .= 'GROUP BY bt.tagid, t.id, t.type, t.userid, t.text ';

      $sql .= 'ORDER BY ct DESC, t.text ASC ';

      $sql .= LIMIT {$this->config->numberoftags} ;

        Attachments

          Activity

            People

            • Assignee:
              skodak Petr Skoda
              Reporter:
              imported Imported
              Tester:
              Nobody
              Participants:
              Component watchers:
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:
                Fix Release Date:
                7/Nov/06