Help project maintainers manage RTBC queue on a first-in, first-out basis

That's pi functionality.

One thing that might be easier to measure is the age of the last patch on the issue. So if I post a patch, it sits at cnr for six weeks, then gets to rtbc with no modifications, that might be near the top of the queue even though it was rtbced two hours ago. Compared to a patch that was posted 48 hours ago and RTBCed 12 hours ago then had two minor followups posted while the status was left at RTBC.

This goes back to the meta issue, but are we trying to get at both sides of this?

- patches which are a bit green round the ears and might not actually be ready yet.

- patches which have been waiting around a long time.

Either rtbc age or patch age is going to affect these slightly differently.

Didn't think thoroughly through it, but "latest patch age" makes indeed way more sense to me than "rtbc age."

Interesting. "latest patch age" and "view" make me say "denormalize". This will absolutely destroy some already bad queries if we try to figure this out each time via SQL. We need to record a timestamp in a new column in the {project_issue_nodes} table, keep it accurate, expose it to views, and then alter the default views to include a sort by this.

Sorts in Views2 are a bit sucky, since you have to make them columns in a table if you want to have different possible sort orders. We should have a separate issue about upgrading d.o to Views3 and ensuring PI is compatible with that. Then, we could have a bunch of exposed sorts, even if we don't necessarily want to display all these timestamps in the default (or adv search) issue queues.

Then again, if we're sorting by this sort of thing, maybe we need to display it, too. But, that seems pretty specialized. I think a better approach is doing the denormalization and exposure to views via this issue, and then have another drupalorg issue open for a new RTBC view with all the bells and whistles we need for this. Does that make sense to y'all?

Thanks,
-Derek

attribute blockquote

Related: #2242183: Store first-RTBC timestamp and add column to issue listings

Latest patch age does not take rerolls into account: what about counting the number of RTBC patches? Yes, we'd definitely need denormalization for that...

This is one of the biggest contributors to burnout, so escalating to major. The idea to expose "last patch upload date" is intriguing; seems like that'd be super simple to figure out, vs. trying to figure out things like the first/last time an issue was moved to status X.

And while #7 is undoubtedly correct, I'm currently in the process of triaging 70+ issues in the RTBC queue, I can tell you that the last uploaded patch value would still be a lot better than nothing. Also, it seems like random testbot fails are statistically more common than re-rolls, and this would help prevent against false-positives related to that.

> seems like that'd be super simple to figure out, vs. trying to figure out things like the first/last time an issue was moved to status X.

I think they're both about the same in terms of complexity.

In both cases, we're doing a one-to-many join and wanting to limit the many by a criterion that gives us just one:

- node -> all files in the filefield, but limit to the newest file
- node -> all revisions that have status at 'RTBC', but limit to the oldest revision

> We need to record a timestamp in a new column in the {project_issue_nodes} table, keep it accurate, expose it to views, and then alter the default views to include a sort by this.

A quicker, hackier way to do this would be:

- add a date field to project issue nodes
- hide it from display and form display
- add a hook_node_presave() which sets the field to REQUEST_TIME if:
- the node's status has changed from something else to RTBC
- AND the date field in question is not already set

Patches are all managed files, and file_managed has a timestamp field, no? Or am I missing something?

If we added custom columns we'd have to do that with hook_schema_alter(). RTBC is a very Drupal-specific thing, we wouldn't want that behaviour in Project issue module "proper," unless we could somehow generalize it out.

Drupal.org uses the search_api_db module to denormalize the data for fewer tables and JOINs. The first step is a Search API alter callback to make the last file upload timestamp available for indexing. These are implemented in project_issue’s search_api directory. (Unless there’s another module which happens to already do this.)

Once that’s done, this issue can move to drupalorg for actually doing the indexing, back-filling data, and adding to our issue queue Views.

I think it will work best to store the time of the last status change. This will let all the queues be ordered by how long an issue has been in that status.

Drupal.org’s issue index is now storing the last status change timestamp as issues are updated. The next step is to make some ugly code to back-fill that data.

Back-filling the data was done with this query to get the last status change: SELECT n.nid, ifnull(min(nr.timestamp), n.created) FROM node n LEFT JOIN (SELECT n.nid, max(frf_is.revision_id) diff_revision FROM node n INNER JOIN field_data_field_issue_status fdf_is ON fdf_is.entity_id = n.nid LEFT JOIN field_revision_field_issue_status frf_is ON frf_is.entity_id = n.nid AND frf_is.field_issue_status_value != fdf_is.field_issue_status_value WHERE n.status = 1 AND n.type = 'project_issue' GROUP BY n.nid ORDER BY NULL) q ON q.nid = n.nid LEFT JOIN node_revision nr ON nr.nid = n.nid AND nr.vid > q.diff_revision WHERE n.status = 1 AND n.type = 'project_issue' GROUP BY n.nid ORDER BY NULL;

This type of Search API field does not come with a Views field handler, so looks like we need to build one.

I ended up needing to add a hidden field which is now backfilled.

To do:
- Add the new field to the index and backfill.
- Remove the old field.
- Export the Views with that field.

Exciting!! :) Thanks so much, @drumm!

The new field is now being indexed in Search API. Updating the Views should be doable in the next day or two.

🎉

“Advanced search” issue queue listings now have a “Status changed” column, so anything can now be sorted by the last time the issue status changed.

For example, the longest RTBC core issues are at https://www.drupal.org/project/issues/search/drupal?text=&assigned=&subm...

YESSSSS!!!

Thank you SOOO much for this!!! :D

W00t!