Wrong 'open issues' count on user project page

Confirmed. This is just from the project_issue_user_projects.view.php default view provided by project_issue module. Not exactly sure what's going on with that column, I'll have to dig deeper or grab the folks who worked on porting this table to D7 to get them to look. Stay tuned.

Yeah, this kind of sucks. ;) The D7 view is just doing an aggregated field counting all issues that point to the given parent project. In D6, the upper table of projects there wasn't generated by a view at all -- it was some custom code that was doing the queries itself and correctly restricting issues to open states. I think our choices here are either:

A) Revert this upper table to custom code (uck!)

B) Add a custom views field handler that does the magic to count all issues for a given project with the desired states.

C) Add a filter on this view that restricts the issues from the relationship to the status values we care about. However, then that's going to mean that the most recent update time is also going to be filtered. Unless we have two separate relationships to the same data and filter them separately. I'm not sure that's even possible.

I'm going to click around on a test site a bit and see if I can get this working.

Tagging so people are more likely to find this. ;)

Totally untested, but something like this might work. I'm trying this on a test site now.

No, that doesn't work, either. They query blows up with the extra relationship and we get a missing count. So, revised options:

C) Filter the related issue status so the count is right, but then the last updated time is only for open issues, not all issues (which is misleading).

D) Try to get two relationships working on the same view so that the MAX timestamp one is unrestricted (except for the particular project) while the open issues count is also being filtered by issue status.

This probably isn't a high priority for right now, so I'm unassigning myself again...

Gonna give it a try

Talked with nick_vh about this. It's probably not possible with default views configuration. You have to add an extra relationship with a filter.

You could probably use https://drupal.org/project/views_field_view to make the view. But that's off course an extra module.
What's also possible is writing a custom view field handler, but off course, that's some extra custom code.
unassiging for now.

Perhaps just renaming the table column to all issues is simplest solution.
I filed a follow up, that this actually also is not really clear how this block (should) work.
#2228731: Confusing project_issue_user_projects block

I don't think having count of all issues on that page is very helpful. So I'd vote for custom view field handler if you can get it working.
I agree that columns in general are a bit confusing, so sorting that in a separate issue is a good idea.

I agree that columns in general are a bit confusing, so sorting that in a separate issue is a good idea.

You gave me the tip ;-)
I'll go for the custom views field then.

This will need attention to performance. It could be adding more per-row, making the page slow for people maintaining a lot of projects. What queries does it add? How good/bad is explain on those?

Performance wise, probably best is to make 2 custom view field handlers, for open issues, and last timestamp (with user filter). This could also remove the current issues relationship.
This changes the query with the current double join, in 1 query with a single relationship, and 2 single join queries per project. Not sure how this is better or worse performance wise. But i assume it's actually better, since we can remove a double join.

Not sure if i can fix this in decent timeframe, so unassigning for now. If i have something i'll post it here.

Hiding the original patch if it doesn't work.

Going to have a look at this one.

So I had a look at how this used to work in D6, and actually in D6 both the 'Last issue update' and 'Open issues' were filtered to be based on open issues. Rather than one being based on closed issues, and another on open issues.

So, I've attached a patch that just adds a filter to that view that filters by open issues.

If we really did want the 'Last issue update' to be based upon closed issues too, then:
Another approach would be to just use views field view module, which is enabled on d.o it seems.
And then yes, another approach would be to use a subquery I think, I'm not sure you can construct this query with simple joins, as we're wanting to aggregate over two different sets of data?

So, I guess we should decide if we care about the 'Last issue update' column, maybe we should rename it to: 'Last open issue update' and apply my patch and call this done?

Well hot damn. ;) You're totally right. I was just assuming/mis-remembering D6 was doing it differently, but lo:

  $default_states = implode(',', project_issue_default_states());
  $result = db_query(db_rewrite_sql("SELECT n.nid, n.title, COUNT(ni.nid) AS count, MAX(ni.changed) AS max_issue_changed FROM {node} n LEFT JOIN {project_issues} pi ON n.nid = pi.pid AND pi.sid IN ($default_states) LEFT JOIN {node} ni ON ni.nid = pi.nid AND ni.status = 1 WHERE n.type = 'project_project' AND n.status = 1 AND n.uid = %d GROUP BY n.nid, n.title") . tablesort_sql($header), $uid);

Since D6 always had this behavior and no one apparently noticed/cared, and arguably it's more appropriate (e.g. the issue auto-closer shouldn't always impact this number, nor random comments on closed issues), I think this patch is the best approach after all. Thanks for the thorough investigation! So nice having a fresh pair of eyes on this stuff. :)

I need to actually test this before I commit/push, but it looks great on a quick inspection. Too tired now to do a careful job, and I'm traveling again tomorrow so I might not have a chance, but I hope to get this in soon.

Thanks again!
-Derek

Thank

This is indeed probably the easiest approach, and since it was this way on d.o D6-style, i think we should go this way.
Perhaps we should change the label to say "last issue update on open issue".

this however still could cause some confusion, but that's probably better discussed in the follow-up i opened for this. #2228731: Confusing project_issue_user_projects block

Looks like good progress, but this limits the table to only projects with open issues. There should be rows with zero open issues. It is a bit easier to spot on Drupal.org dev sites since older issues don't exist.

Maybe there is a clean way to get this condition into the LEFT JOIN only, and not the WHERE?

Okay, so here's a patch that adds two very nasty subqueries to get the counts and dates working correctly again.

This needs a lot of cleanup, which I'll do later.

I had a look into moving the where condition into the join, but that didn't seem to change the query output.

Okay, here's a better patch.

Given that we know that our field data is stored in SQL, and specifically MySQL, we can exploit that to get the query to return the numbers we want:

I've created a views field handler that adds a handler that addsIF(status = <open>, 0, 1) expression to the query. This can then be SUMed with all the other aggregates to give the count that we need.

This is a much neater query I think, and doesn't change the rest of the view too much and doesn't add a whole bunch of code.

If someone wants to review this, or suggest a different way of how to go about fixing this, feel free!

This looks good!

Now deployed on Drupal.org.

Hooray! This wasn't the easiest one.

Also?! 4 open Drupal.org 7.1 issues:
https://www.drupal.org/project/issues/search?text=&projects=&assigned=&s...

Yay! :)

Would be keenly interested to know if this was causing any performance issues. I'm not sure if the query became more or less nasty.

It didn't look bad at all. I tested and EXPLAINed the query on the production DB before deployment, and the query actually looked better than I was expecting. I haven't heard any reports of slowdowns since deployment.

Yeah, I'd done some explains as well, but just wondered about the actual impact, but as no-one has complained, great!