Add extra order by clause when sorting by rating (#288)

chadweimer · web-flow · commit e90ec9792908 · 2021-06-13T16:39:43.000-07:00
diff --git a/db/recipe-postgres.go b/db/recipe-postgres.go
@@ -177,6 +177,14 @@ func (d *postgresRecipeDriver) Find(filter *models.SearchFilter, page int64, cou
 	if filter.SortDir == models.SortDirDesc {
 		orderStmt += " DESC"
 	}
+	// Need a special case for rating, since the way the execution plan works can
+	// cause uncertain results due to many recipes having the same rating (ties).
+	// By adding an additional sort to show recently modified recipes first,
+	// this ensures a consistent result.
+	if filter.SortBy == models.SortRecipeByRating {
+		orderStmt += ", r.modified_at DESC"
+	}
+
 	orderStmt += " LIMIT ? OFFSET ?"
 
 	selectStmt := d.postgresDriver.Db.Rebind("SELECT " +
diff --git a/db/recipe-sqlite.go b/db/recipe-sqlite.go
@@ -178,6 +178,14 @@ func (d *sqliteRecipeDriver) Find(filter *models.SearchFilter, page int64, count
 	if filter.SortDir == models.SortDirDesc {
 		orderStmt += " DESC"
 	}
+	// Need a special case for rating, since the way the execution plan works can
+	// cause uncertain results due to many recipes having the same rating (ties).
+	// By adding an additional sort to show recently modified recipes first,
+	// this ensures a consistent result.
+	if filter.SortBy == models.SortRecipeByRating {
+		orderStmt += ", r.modified_at DESC"
+	}
+
 	orderStmt += " LIMIT ? OFFSET ?"
 
 	selectStmt := d.sqliteDriver.Db.Rebind("SELECT " +
