MediaWiki: how to get the last N edited articles by a user?

Question

Using PHP and MySQL, I am integrating a site with a wiki based on MediaWiki, so that profiles of users in the site have some stats about their contributions in the wiki.

I want to show the last 3 edited articles of a user in a namespace (the main one, id 0), without repeating, and the links to them.

In order to do that, I do more or less the following:

// getting the article IDs
$db->query("SELECT DISTINCT rev_page
        FROM revision r, page p
        WHERE r.rev_page = p.page_id
            AND p.page_namespace = 0
            AND rev_user = {$userId}
        ORDER BY r.rev_timestamp DESC
        LIMIT 3");
// then the IDs are stored in $last_edited_ids...

// getting the names of the articles
$db->query("SELECT page_title
        FROM page WHERE page_id
        IN (" . implode($last_edited_ids, ',') . ")");

The problem is that I am not sure it is correct, although it works most of the time: for some users, an old article shows as the last edited, but the 2nd and 3rd result are correct. That makes me thing I must be doing something wrong, but SQL is not my strong point...

Tip: if you don't know the MediaWiki database scheme (it is rather autoexplicative), you can have a look to it here.

Answer 1

I am not familliar with MediaWiki implementation, but I guess you should ORDER BY something else than rev_id. Maybe there's no guarantee the ids to be assigned chronologically. Isn't there some kind of date field in the revision table?

Then you could:

... ORDER BY r.date DESC ...

Answer 2

The "DISTINCT rev_page" clause is throwing your SQL off. It is applying the DISTINCT to all of the rev_page (page.page_id's), so it will get a random rev_id per page.

Try this query, which combines your two queries into one:

SELECT DISTINCT p.page_title
FROM revision r, page p
WHERE r.rev_page = p.page_id
AND p.page_namespace = 0
AND rev_user = {$userId}
GROUP by p.page_title
ORDER BY MAX(r.rev_id) DESC
LIMIT 3