Media with no reviews are sorted funny by default

NonlinearFruit / Retrospector

Review and rate your favorite media in an easy-to-use way that allows for statistics and chronological change in preference.

The Unlicense

12 stars 1 forks source link

Media with no reviews are sorted funny by default #83

Closed NonlinearFruit closed 6 years ago

NonlinearFruit commented 6 years ago

The sql sorts media by the largest review id. But if there is no review id, it sorts by media id. This was a fine idea, but review ids grow faster then media ids. My current review id is 2k and media is 1.6k. So if I create a media with no reviews is will be in 400th place, even though it is the newest.

NonlinearFruit commented 6 years ago

To solve this we use this this: ORDER BY (CASE WHEN MAX(review.id) IS NULL THEN 1 ELSE 0 END) DESC, MAX(review.id) DESC

First places media with reviews before media with no reviews
Then sort the media by there max review id