Details

    • Type: Sub-task
    • Status: Closed
    • Priority: 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

          Attachments

            Activity

            Hide
            poltawski 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
            poltawski 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
            poltawski Dan Poltawski added a comment -

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

            Show
            poltawski Dan Poltawski added a comment - NOTE TO SELF: don't forget about a redirect/solution for the english RSS feed.
            Hide
            poltawski 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
            poltawski 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
            poltawski Dan Poltawski added a comment -

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

            Show
            poltawski Dan Poltawski added a comment - Well, my new query seem to perform much worse, so thats not it
            Hide
            poltawski 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
            poltawski 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
            poltawski 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
            poltawski 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
            poltawski 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
            poltawski 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
            poltawski 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
            poltawski 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
            poltawski Dan Poltawski added a comment -

            Done.

            Show
            poltawski Dan Poltawski added a comment - Done.
            Hide
            tsala 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
            tsala 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
            poltawski 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
            poltawski 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: