Details

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

      Description

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

      One feed per course, using the language code.

        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