Enhanced rating search; uses correct algorithm for calculation, allows

specification of the operator.

3 files changed, 34 insertions, 16 deletions

diff --git a/docs/CHANGELOG b/docs/CHANGELOG
index dae90d53..138edb8d 100755
--- a/docs/CHANGELOG
+++ b/docs/CHANGELOG
@@ -4,6 +4,8 @@
 
 --------------------------------------------------------------------------
   v.3.6-Alpha1
+	- Rating search now allows specification of operator (>=, <=, or =)
+		and uses the same ratings as normal display.
 	- Add -t to catalog_update.inc for generating thumbnails
 	- Generate Thumbnails during catalog art operations
 	- Fixed transcode seeking of Flacs by switching to MM:SS format for
diff --git a/lib/search.php b/lib/search.php
index 199ce5c1..e860d68b 100644
--- a/lib/search.php
+++ b/lib/search.php
@@ -198,23 +198,34 @@ function search_song($data,$operator,$method,$limit) {
 			break;
 			case 'rating':
 				$value = intval($value);
-
-				// This is a little more complext, pull a list of IDs that have this average rating
-				$rating_sql = "SELECT `object_id`,AVG(`rating`.`rating`) AS `avgrating` FROM `rating` " .
-						"WHERE `object_type`='song' GROUP BY `object_id`";
-				$db_results = Dba::read($rating_sql);
-
-				// Fill it with one value to prevent sql error on no results
-				$where_sql .= " `song`.`id` IN (";
-
-				$ids = array('0');
-
-				while ($row = Dba::fetch_assoc($db_results)) {
-					if ($row['avgrating'] < $value) { continue; }
-					$ids[] = $row['object_id'];
+				$userid = $GLOBALS['user']->id;
+				$rcomparison = '>=';
+				if ($_REQUEST['s_rating_operator'] == '1') { 
+					$rcomparison = '<=';
 				}
-
-				$where_sql .= implode(',',$ids) . ') ' . $operator;
+				elseif ($_REQUEST['s_rating_operator'] == '2') {
+					$rcomparison = '<=>';
+				}
+				// Complex SQL follows
+				// We do a join on ratings from the table with a
+				// preference for our own and fall back to the 
+				// FLOORed average of everyone's rating if it's
+				// a song we haven't rated.
+				if ($operator == 'AND') {
+					$table_sql .= ' INNER JOIN';
+				}
+				else {
+					$table_sql .= ' LEFT JOIN';
+				}
+				$table_sql .= "	(SELECT `object_id`, `rating` FROM `rating` WHERE `object_type`='song' AND `user`='$userid'
+					UNION
+					SELECT `object_id`, FLOOR(AVG(`rating`)) AS 'rating' FROM `rating` 
+						WHERE `object_type`='song' AND 
+						`object_id` NOT IN (SELECT `object_id` FROM `rating` WHERE `object_type`='song' AND `user`='$userid')
+						GROUP BY `object_id`
+					) AS realrating ON `song`.`id` = `realrating`.`object_id`";
+
+				$where_sql .= " `realrating`.`rating` $rcomparison '$value' $operator";
 			break;
 			case 'tag':
 
diff --git a/templates/show_search.inc.php b/templates/show_search.inc.php
index 1cba5861..a1b8da5a 100644
--- a/templates/show_search.inc.php
+++ b/templates/show_search.inc.php
@@ -104,6 +104,11 @@
 <tr class="<?php echo flip_class(); ?>">
 	<td><?php echo _('Rating'); ?></td>
 	<td>
+		<select id="s_rating_operator" name="s_rating_operator">
+			<option value="0" <?php if($_REQUEST['s_rating_operator']=="0") echo "selected=\"selected\""?>><?php echo _('>='); ?></option>
+			<option value="1" <?php if($_REQUEST['s_rating_operator']=="1") echo "selected=\"selected\""?>><?php echo _('<='); ?></option>
+			<option value="2" <?php if($_REQUEST['s_rating_operator']=="2") echo "selected=\"selected\""?>><?php echo _('='); ?></option>
+		</select>
 		<select id="s_rating" name="s_rating">
 			<option value="">&nbsp;</option>
 			<option value="1" <?php if($_REQUEST['s_rating']=="1") echo "selected=\"selected\""?>><?php echo _('One Star'); ?></option>