435123 - Reviews dropped when new add-on version was uploaded

Status: VERIFIED FIXED

(addons.mozilla.org Graveyard :: Public Pages, defect)

Description

[Basil Hashem [:baz]]

For BlueOrganizer, the author has noted that the review count dropped (from 21 to 13) when a new version of the addon was uploaded. 

I performed some queries on the database on the Mozilla-internal khan db and observed that the database has all the reviews (21 entries, actually 23 since 2 are under editorreview). It seems that the query that generates the reviews view is not picking up all the reviews somehow.

Here's the query I used:

SELECT reviews.id as "Review ID",reviews.version_id,reviews.rating,editorreview,translations.localized_string as "Review Title" FROM `reviews`
	inner join versions on versions.id=reviews.version_id
	inner join translations on reviews.title=translations.id
WHERE versions.addon_id="3481";

Comment 1

[Fred Wenzel [:wenzel]]

One of their versions probably has reviews associated with it that does not contain any files (anymore). The current code returns all reviews for all valid versions, a valid version being one that is not disabled and contains files.

Comment 2

[Justin Scott [:fligtar]]

Fred's correct, version_id 42640 had all files deleted, so its reviews are not showing up. However, I think that the query should be changed so that even those versions' reviews show up. Versions don't have statuses and can't be disabled --  only the files of the version can be.

Basil and I were talking about this and I am starting to think we should just associate reviews with add-ons instead of versions, since we don't make any differentiation of reviews by version.

Basil, the SQL to fix the particular add-on is:
UPDATE reviews SET version_id=45174 WHERE version_id=42640;

Comment 3

[Fred Wenzel [:wenzel]]

Attachment: Show reviews for all (not only valid) versions

In order not to cause yet another review count mismatch (bug 436810), this patch will show and count all reviews for all versions, regardless of these versions' files' statuses.

Comment 4

[Justin Scott [:fligtar]]

Comment on attachment 324563 [details] [diff] [review]
Show reviews for all (not only valid) versions

Tested and all the reviews showed up. Thanks for the fix.

Comment 5

[Fred Wenzel [:wenzel]]

Thanks for the review: Checked into trunk, r15261.

Comment 6

[Stephen Donner [:stephend] Not actively reading bugmail]

https://preview.addons.mozilla.org/en-US/firefox/search?q=blueorganizer&cat=all currently shows 24 reviews, while https://preview.addons.mozilla.org/en-US/firefox/addon/3481 displays 26 reviews, but I'm pretty sure that's bug 436810, which will need a maintenance script to be run.

The original issue in comment 0 is fixed; the review count is back up there.

Safe to verify?

Comment 7

[Fred Wenzel [:wenzel]]

(In reply to comment #6)
> Safe to verify?

Yes. The fact that the static count generated by the maintenance script differs is expected. Maybe preview should run the same cron jobs production does, so that we don't constantly run into this problem?

Comment 8

[Stephen Donner [:stephend] Not actively reading bugmail]

Verified FIXED; yeah, that would be nice.  I hate having to ask annoying questions (hard to believe, I know!)

Comment 9

[Fred Wenzel [:wenzel]]

(In reply to comment #8)
> Verified FIXED; yeah, that would be nice.  I hate having to ask annoying
> questions (hard to believe, I know!)
> 

files bug 438832.