This was discussed during the 2022-09-06 mentorship meeting. There are two options we can go with:

1. add the number of unreviewed edits to the mentee overview module on flaggedrevs-enabled wikis
2. allow mentors to filter Special:PendingEdits down to only edits by their mentees, so they can focus only on those two edits.

@Dyolf77_WMF said he prefers the first solution, while @Ankan_WMF prefers the second. Perhaps we should do both?

Based on my initial assessment, it looks the first solution will be slightly easier. The second might get tricky, as there is no obvious way how to propagate the mentorship concept to other extensions, such as FlaggedRevs.

For the first solution, here is a query that can be used to generate the relevant numbers:

SELECT
    actor_name,
    COUNT(*) AS pending
FROM flaggedpage_pending
JOIN revision ON ((fpp_page_id = rev_page) AND (fpp_rev_id < rev_id))
JOIN actor ON rev_actor=actor_id
WHERE
    actor_user IN ({user_ids})

GROUP BY actor_id
ORDER BY pending DESC

Looks like the fpp_rev_id column is the rev ID of the stable version (rather than the "pending" version), which is the reason for the fpp_rev_id < rev_id condition (intention is to say "any revision following the stable one").

There's also flaggedpages, which has a similar structure and looks to be also usable to generate the answer:

SELECT
    actor_name,
    COUNT(*) AS pending
FROM flaggedpages
JOIN revision ON ((fp_page_id = rev_page) AND (fp_stable < rev_id))
JOIN actor ON rev_actor=actor_id
WHERE
    actor_user IN ({user_ids})

GROUP BY actor_id
ORDER BY pending DESC

Both queries return the same numbers (at least for arwiki). I'm not sure what's the exact difference between those two tables, that will need to be figured out.

Quick validation based on Special:Contributions and presence of the flaggedrevs-pending CSS class shows that both queries provide the correct numbers.

@Urbanecm_WMF I'm moving this to "In progress" but please correct that if it's wrong.

Thanks @kostajh!

Note that the rev_actor/actor_id join is a bit different on wikis which haven't finished actor migration yet (maybe only enwiki at this point?) so the query might need to be generated via ActorMigration::getJoin().

Per FlaggableWikiPage::updatePendingList() and FlaggableWikiPage::updateStableVersion(), there doesn't seem to be much difference between the two tables - flaggedpage_pending only includes pages with pending edits while flaggedpages does include fully reviewed pages, but that's not relevant here. Not sure why they both exist.

In T297811#8244250, @Tgr wrote:

Note that the rev_actor/actor_id join is a bit different on wikis which haven't finished actor migration yet (maybe only enwiki at this point?) so the query might need to be generated via ActorMigration::getJoin().

Thanks for the reminder! So far, I only played with the databases at the analytics cluster (+manually verifying via Special:PendingChanges). Once I get to actually writing the PHP code, I'll make sure to call ActorMigration.

Per FlaggableWikiPage::updatePendingList() and FlaggableWikiPage::updateStableVersion(), there doesn't seem to be much difference between the two tables - flaggedpage_pending only includes pages with pending edits while flaggedpages does include fully reviewed pages, but that's not relevant here. Not sure why they both exist.

Interesting. Thanks for the info!

In T297811#8215229, @Urbanecm_WMF wrote:

This was discussed during the 2022-09-06 mentorship meeting. There are two options we can go with:

1. add the number of unreviewed edits to the mentee overview module on flaggedrevs-enabled wikis
2. allow mentors to filter Special:PendingEdits down to only edits by their mentees, so they can focus only on those two edits.

@Dyolf77_WMF said he prefers the first solution, while @Ankan_WMF prefers the second. Perhaps we should do both?

Based on my initial assessment, it looks the first solution will be slightly easier. The second might get tricky, as there is no obvious way how to propagate the mentorship concept to other extensions, such as FlaggedRevs.

For option 1, is there a link on the number, and if so, where does it goes? To the mentees' special:Contributions' page where pending edits are listed? If you work on option 2, would this link go to the filtered version you describe in option 2?

In T297811#8251197, @Trizek-WMF wrote:

In T297811#8215229, @Urbanecm_WMF wrote:

[...]

1. add the number of unreviewed edits to the mentee overview module on flaggedrevs-enabled wikis
2. allow mentors to filter Special:PendingEdits down to only edits by their mentees, so they can focus only on those two edits.

[...]

For option 1, is there a link on the number, and if so, where does it goes? To the mentees' special:Contributions' page where pending edits are listed? If you work on option 2, would this link go to the filtered version you describe in option 2?

The idea is to complete T317145: Special:PendingChanges: Make it possible to filter the revision list by username as part of working on option 1. The link will then go to Special:PendingChanges, filtered down to that particular user.

When I discussed this task with @Tgr, he told me that option 2 is actually already possible thanks to the RecentChanges filters. If you are a mentor at a FlaggedRevs-enabled wiki, the "Filters" section should have the following options:

• "Needs review" (under "Flagged Revisions" section)
• "Your (un)starred mentees" (under "Mentorship" section)

If you select all three of those options, you'll have recent changes filtered down to edits by your mentees that need review (=option 2). The only issue is that unstarred filters are not available for our biggest wikis (arwiki, enwiki, frwiki), and some of them are FlaggedRevs-enabled. Other than that, it should work.

As we link to Special:Contributions, we provide a link that targets one user. We should keep this for FR, for consistency: if I click on the number of pending edits, I should just have the list of pending edits to check.

In T297811#8255419, @Trizek-WMF wrote:

As we link to Special:Contributions, we provide a link that targets one user. We should keep this for FR, for consistency: if I click on the number of pending edits, I should just have the list of pending edits to check.

Yes, that's the idea. I'm not yet sure whether the list will be via Special:Contributions or via Special:PendingChanges. In any way, with option 1, the mentor should have a link that'd display pending edits by that particular mentee.

In T297811#8244250, @Tgr wrote:

Note that the rev_actor/actor_id join is a bit different on wikis which haven't finished actor migration yet (maybe only enwiki at this point?) so the query might need to be generated via ActorMigration::getJoin().

Scratch that, ActorMigration is apparently not needed anymore. That's being clarified in c835235.

In T297811#8261591, @Tgr wrote:

In T297811#8244250, @Tgr wrote:

Note that the rev_actor/actor_id join is a bit different on wikis which haven't finished actor migration yet (maybe only enwiki at this point?) so the query might need to be generated via ActorMigration::getJoin().

Scratch that, ActorMigration is apparently not needed anymore. That's being clarified in c835235.

Oh, awesome! Thanks for the info.

This is blocked on in-review T321445: When FlaggedRevs is used, make it possible to filter down to "Pending changes only" from Special:Contributions (the newly added column will have to be linked to a page).