Details

    • Type: Sub-task Sub-task
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Component/s: moodle.org
    • Labels:
      None

      Description

      As for Using Moodle - http://moodle.org/useful/rss.xml and http://moodle.org/useful/

      One feed per course, using the language code.

        Gliffy Diagrams

          Activity

          Hide
          Dan Poltawski added a comment -

          I am starting to look at this.

          The title says 'one feed per course', but I had assumed we'd want to allow multiple courses per language (e.g. using moodle & business course). I was just looking into doing this and discovered that will make it significantly harder. So, I intend to limit this to one course per language for now..

          Show
          Dan Poltawski added a comment - I am starting to look at this. The title says 'one feed per course', but I had assumed we'd want to allow multiple courses per language (e.g. using moodle & business course). I was just looking into doing this and discovered that will make it significantly harder. So, I intend to limit this to one course per language for now..
          Hide
          Dan Poltawski added a comment -

          NOTE TO SELF: don't forget about a redirect/solution for the english RSS feed.

          Show
          Dan Poltawski added a comment - NOTE TO SELF: don't forget about a redirect/solution for the english RSS feed.
          Hide
          Dan Poltawski added a comment - - edited

          I've spent far too much time making the existing /useful/ query work on postgres.

          The original query was:

          SELECT fp.id, fd.forum, ctx.id AS ctxid, u.id AS uid
          FROM mdl_forum_posts fp
          JOIN mdl_modules m
          LEFT JOIN mdl_user u ON u.id = fp.userid
          LEFT JOIN mdl_forum_discussions fd ON fd.id = fp.discussion
          LEFT JOIN mdl_course_modules cm ON (cm.course = fd.course AND cm.instance = fd.forum AND cm.module = m.id)
          JOIN mdl_context ctx ON (ctx.instanceid = cm.id AND ctx.contextlevel = 70)
          LEFT JOIN mdl_rating r ON (r.contextid = ctx.id AND fp.id = r.itemid)
          WHERE fd.course = 2
          AND m.name = 'forum'
          AND r.timecreated > 1346738673
          GROUP BY fp.id
          ORDER BY r.timecreated DESC;
          

          I modified to (getting dupes):

          SELECT fp.id, fd.forum, ctx.id AS ctxid, u.id AS uid
                      FROM mdl_forum_posts fp
                      JOIN mdl_user u ON u.id = fp.userid
                      JOIN mdl_forum_discussions fd ON fd.id = fp.discussion
                      JOIN mdl_course_modules cm ON (cm.course = fd.course AND cm.instance = fd.forum)
                      JOIN mdl_modules m ON (cm.module = m.id)
                      JOIN mdl_context ctx ON (ctx.instanceid = cm.id AND ctx.contextlevel = 70)
                      LEFT OUTER JOIN mdl_rating r ON (r.contextid = ctx.id AND fp.id = r.itemid)
                      WHERE fd.course = 2
                      AND m.name = 'forum'
                      AND r.timecreated > 1346738673
                      ORDER BY r.timecreated DESC
          

          And now to avoid the groupby problem (needing all fields in groupby statement), I think i'm going for this subselect approach.

          SELECT fp.id, fd.forum, ctx.id AS ctxid, u.id AS uid
          FROM mdl_forum_posts fp
          JOIN mdl_user u ON u.id = fp.userid
          JOIN mdl_forum_discussions fd ON fd.id = fp.discussion
          JOIN mdl_course_modules cm ON (cm.course = fd.course AND cm.instance = fd.forum)
          JOIN mdl_modules m ON (cm.module = m.id)
          JOIN mdl_context ctx ON (ctx.instanceid = cm.id AND ctx.contextlevel = 70)
          WHERE fd.course = 2
          AND m.name = 'forum'
          AND fp.id IN (SELECT r.itemid FROM mdl_rating r WHERE r.contextid = ctx.id AND r.timecreated > 1346738673)
          

          Show
          Dan Poltawski added a comment - - edited I've spent far too much time making the existing /useful/ query work on postgres. The original query was: SELECT fp.id, fd.forum, ctx.id AS ctxid, u.id AS uid FROM mdl_forum_posts fp JOIN mdl_modules m LEFT JOIN mdl_user u ON u.id = fp.userid LEFT JOIN mdl_forum_discussions fd ON fd.id = fp.discussion LEFT JOIN mdl_course_modules cm ON (cm.course = fd.course AND cm.instance = fd.forum AND cm.module = m.id) JOIN mdl_context ctx ON (ctx.instanceid = cm.id AND ctx.contextlevel = 70) LEFT JOIN mdl_rating r ON (r.contextid = ctx.id AND fp.id = r.itemid) WHERE fd.course = 2 AND m.name = 'forum' AND r.timecreated > 1346738673 GROUP BY fp.id ORDER BY r.timecreated DESC; I modified to (getting dupes): SELECT fp.id, fd.forum, ctx.id AS ctxid, u.id AS uid FROM mdl_forum_posts fp JOIN mdl_user u ON u.id = fp.userid JOIN mdl_forum_discussions fd ON fd.id = fp.discussion JOIN mdl_course_modules cm ON (cm.course = fd.course AND cm.instance = fd.forum) JOIN mdl_modules m ON (cm.module = m.id) JOIN mdl_context ctx ON (ctx.instanceid = cm.id AND ctx.contextlevel = 70) LEFT OUTER JOIN mdl_rating r ON (r.contextid = ctx.id AND fp.id = r.itemid) WHERE fd.course = 2 AND m.name = 'forum' AND r.timecreated > 1346738673 ORDER BY r.timecreated DESC And now to avoid the groupby problem (needing all fields in groupby statement), I think i'm going for this subselect approach. SELECT fp.id, fd.forum, ctx.id AS ctxid, u.id AS uid FROM mdl_forum_posts fp JOIN mdl_user u ON u.id = fp.userid JOIN mdl_forum_discussions fd ON fd.id = fp.discussion JOIN mdl_course_modules cm ON (cm.course = fd.course AND cm.instance = fd.forum) JOIN mdl_modules m ON (cm.module = m.id) JOIN mdl_context ctx ON (ctx.instanceid = cm.id AND ctx.contextlevel = 70) WHERE fd.course = 2 AND m.name = 'forum' AND fp.id IN (SELECT r.itemid FROM mdl_rating r WHERE r.contextid = ctx.id AND r.timecreated > 1346738673)
          Hide
          Dan Poltawski added a comment -

          Well, my new query seem to perform much worse, so thats not it

          Show
          Dan Poltawski added a comment - Well, my new query seem to perform much worse, so thats not it
          Hide
          Dan Poltawski added a comment -

          This is the best i've got so far, but we loose the 'order by last rated' by doing this...

          SELECT DISTINCT fp.*, fd.forum, ctx.id AS ctxid, u.id AS uid
          FROM mdl_forum_posts fp
          JOIN mdl_user u ON u.id = fp.userid
          JOIN mdl_forum_discussions fd ON fd.id = fp.discussion
          JOIN mdl_course_modules cm ON (cm.course = fd.course AND cm.instance = fd.forum)
          JOIN mdl_modules m ON (cm.module = m.id)
          JOIN mdl_context ctx ON (ctx.instanceid = cm.id AND ctx.contextlevel = 70)
          JOIN mdl_rating r ON (r.contextid = ctx.id AND fp.id = r.itemid)
          WHERE fd.course = 2 AND m.name = 'forum' AND r.timecreated > 1336438673
          ORDER BY fp.created DESC;
          

          Show
          Dan Poltawski added a comment - This is the best i've got so far, but we loose the 'order by last rated' by doing this... SELECT DISTINCT fp.*, fd.forum, ctx.id AS ctxid, u.id AS uid FROM mdl_forum_posts fp JOIN mdl_user u ON u.id = fp.userid JOIN mdl_forum_discussions fd ON fd.id = fp.discussion JOIN mdl_course_modules cm ON (cm.course = fd.course AND cm.instance = fd.forum) JOIN mdl_modules m ON (cm.module = m.id) JOIN mdl_context ctx ON (ctx.instanceid = cm.id AND ctx.contextlevel = 70) JOIN mdl_rating r ON (r.contextid = ctx.id AND fp.id = r.itemid) WHERE fd.course = 2 AND m.name = 'forum' AND r.timecreated > 1336438673 ORDER BY fp.created DESC;
          Hide
          Dan Poltawski added a comment -

          SELECT fp.*, fd.forum , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, u.id AS uid,u.picture,u.firstname,u.lastname,u.imagealt,u.email
                      FROM mdl_forum_posts fp
                      LEFT JOIN mdl_user u ON u.id = fp.userid
                      LEFT JOIN mdl_forum_discussions fd ON fd.id = fp.discussion
                      LEFT JOIN mdl_course_modules cm ON (cm.course = fd.course AND cm.instance = fd.forum)
                           JOIN mdl_modules m ON (cm.module = m.id)
                      LEFT JOIN mdl_context ctx ON (ctx.instanceid = cm.id AND ctx.contextlevel = 70)
                      LEFT JOIN mdl_rating r ON (r.contextid = ctx.id AND fp.id = r.itemid)
                      WHERE fd.course = $1
                      AND m.name = $2
                      AND r.timecreated > $3
                      GROUP BY fp.id, fd.forum, ctx.id, u.id
                      ORDER BY MAX(r.timecreated) DESC
          [array (
            0 => '2',
            1 => 'forum',
            2 => 1346742634,
          )]
          

          Show
          Dan Poltawski added a comment - SELECT fp.*, fd.forum , ctx.id AS ctxid, ctx.path AS ctxpath, ctx.depth AS ctxdepth, ctx.contextlevel AS ctxlevel, ctx.instanceid AS ctxinstance, u.id AS uid,u.picture,u.firstname,u.lastname,u.imagealt,u.email FROM mdl_forum_posts fp LEFT JOIN mdl_user u ON u.id = fp.userid LEFT JOIN mdl_forum_discussions fd ON fd.id = fp.discussion LEFT JOIN mdl_course_modules cm ON (cm.course = fd.course AND cm.instance = fd.forum) JOIN mdl_modules m ON (cm.module = m.id) LEFT JOIN mdl_context ctx ON (ctx.instanceid = cm.id AND ctx.contextlevel = 70) LEFT JOIN mdl_rating r ON (r.contextid = ctx.id AND fp.id = r.itemid) WHERE fd.course = $1 AND m.name = $2 AND r.timecreated > $3 GROUP BY fp.id, fd.forum, ctx.id, u.id ORDER BY MAX(r.timecreated) DESC [array ( 0 => '2', 1 => 'forum', 2 => 1346742634, )]
          Hide
          Dan Poltawski added a comment -

          Finally got back to this and its done, e.g.:

          http://clone.moodle.org/useful/?lang=fr
          http://clone.moodle.org/useful/rss.php?lang=fr

          It all gets generated and stored in MUC at the same time as the main frontpage stuff gets generated

          Show
          Dan Poltawski added a comment - Finally got back to this and its done, e.g.: http://clone.moodle.org/useful/?lang=fr http://clone.moodle.org/useful/rss.php?lang=fr It all gets generated and stored in MUC at the same time as the main frontpage stuff gets generated
          Hide
          Dan Poltawski added a comment -

          (there are still loose ends to tie up there like lots of english hardcoded strongs from the existing interface)

          Show
          Dan Poltawski added a comment - (there are still loose ends to tie up there like lots of english hardcoded strongs from the existing interface)
          Hide
          Dan Poltawski added a comment -

          Done.

          Show
          Dan Poltawski added a comment - Done.
          Hide
          Helen Foster added a comment -

          Hi Dan,

          Sorry I don't understand what's been done. http://clone.moodle.org/useful/?lang=fr displays an error and http://clone.moodle.org/useful/rss.php?lang=fr doesn't list anything.

          Is this implemented on moodle.org?

          Show
          Helen Foster added a comment - Hi Dan, Sorry I don't understand what's been done. http://clone.moodle.org/useful/?lang=fr displays an error and http://clone.moodle.org/useful/rss.php?lang=fr doesn't list anything. Is this implemented on moodle.org?
          Hide
          Dan Poltawski added a comment -

          Hi Helen,

          This is because the clone is too old and there aren't enough recent useful posts (MDLSITE-2089). I'm waiting for the clone to be updated ( HQ-344 ) to be recent.

          Show
          Dan Poltawski added a comment - Hi Helen, This is because the clone is too old and there aren't enough recent useful posts ( MDLSITE-2089 ). I'm waiting for the clone to be updated ( HQ-344 ) to be recent.

            People

            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development