Only consult is_active = 1 records when looking at which ones have suggestions

Hm, my immediate response would have been that translated strings can also have suggestions. But looks like the filter does not work even if we exclude that. I've also looked at the moderation form filters, but that did not give me results for suggestions on core strings either. Will look into why are these stats are failing.

Mmmmm... I think it was my fault. I imported a lot of suggestions, let me review them and see what happens. I'll reopen the issue if needed.

Well, reviewed all the pending translations that I imported, nothing else to "moderate" in terms of Drupal project and now the system says I've 321 suggestions to review...

Some bug exists for sure on the translation status...

I thought those numbers were cached and not always the 'latest' numbers.

I think there is a cache for the frontpage which could clear more often, we have the same situation for no-nb (but only 3 suggestions on the frontpage, one remains to review)

I'm pretty sure I reviewed the two missing suggestions during the weekend.

The cache is supposed to be cleared on cron (every 30 minutes), so it should not stay the same for a week :) We either have a problem with that cache clearing or the database query. For extra fun, the new caching code and new queries were added in the same time.

Ok, so there seems to be some disconnect between the actual data and the "cached" has_suggestion column data. Looks like that is not updated consistently in some case(s).

First of all, if the "has suggestions" column shows 310 as in the original report above, that *does not* mean you have 310 suggestions to look through, but it means *310 strings* are with suggestions. If all of them have 10 suggestions, then you have 3100 suggestions to look at ;)

So I ran this query which build the stats (drupal core is pid = 2):

SELECT COUNT(DISTINCT t.sid) AS translation_count, t.language FROM l10n_community_line l LEFT JOIN l10n_community_translation t ON l.sid = t.sid WHERE l.pid = 2 AND t.has_suggestion = 1 GROUP BY t.language;

I got the 321 number, which is where it is currently.

Then I looked at actual active suggestions on the language for core:

SELECT COUNT(*) FROM l10n_community_line l LEFT JOIN l10n_community_translation t ON l.sid = t.sid WHERE l.pid = 2 AND t.is_suggestion = 1 AND t.language = 'pt-pt' AND t.is_active = 1;

Got none.

So looks like the has_suggestion cached value on translations get to be somehow inaccurate. Need to look at whether it is the moderation code or the translation code or some other place.

Moving to the right queue.

Ha, further digging reveals that the problem is actually in the SELECT queries. As you can see above, the active row check is desparataly missing on the count query. Only rows which are active should ever be counted in stats:

SELECT COUNT(DISTINCT t.sid) AS translation_count, t.language FROM l10n_community_line l LEFT JOIN l10n_community_translation t ON l.sid = t.sid WHERE l.pid = 2 AND t.has_suggestion = 1 AND is_active = 1 GROUP BY t.language;

Adding these to the three count queries seem to resolve the counting issues indeed. Committing this. Also, deployed on localize.drupal.org and seem to have fixed the mentioned issues. Yay!

BTW if you have a suggestion for the "Suggestions" header to clearly say "this many strings have suggestions" instead of "this many suggestions there are", I'd more then welcome that!

Automatically closed -- issue fixed for 2 weeks with no activity.