counting now using annotations

1 files changed, 14 insertions, 10 deletions

diff --git a/yourmostviewed.php b/yourmostviewed.php
index f9a1af88a..84cc2ca10 100644
--- a/yourmostviewed.php
+++ b/yourmostviewed.php
@@ -13,20 +13,24 @@
 	global $CONFIG;
 	$prefix = $CONFIG->dbprefix;
 	$max = 24;
-	//grab the top views (metadata 'tp_views') for $max number of entities 
-	//ignores entity subtypes
-	$sql = "SELECT md.entity_guid, md.owner_guid, md.enabled, ms.string AS views from " . $prefix . "entities ent
-			INNER JOIN " . $prefix . "metadata md ON md.entity_guid = ent.guid 
-			INNER JOIN " . $prefix . "metastrings ms ON md.value_id = ms.id
-			INNER JOIN " . $prefix . "metastrings ms2 ON md.name_id = ms2.id AND ms2.string = 'tp_views'
-			WHERE ent.owner_guid = " . $viewer->guid . "
-			ORDER BY (views+0) DESC LIMIT $max";
-	
+
+	$sql = "SELECT ent.guid, count( * ) AS views
+			FROM `my_elggentities` ent
+			INNER JOIN " . $prefix . "entity_subtypes sub ON ent.subtype = sub.id
+			AND sub.subtype = 'image'
+			INNER JOIN " . $prefix . "annotations ann1 ON ann1.entity_guid = ent.guid
+			INNER JOIN " . $prefix . "metastrings ms ON ms.id = ann1.name_id
+			AND ms.string = 'tp_view'
+			WHERE ann1.owner_guid = " . $viewer->guid . "
+			GROUP BY ent.guid
+			ORDER BY views DESC
+			LIMIT $max";
+		
 	$result = get_data($sql);
 
 	$entities = array();
 	foreach($result as $entity) {
-		$entities[] = get_entity($entity->entity_guid);
+		$entities[] = get_entity($entity->guid);
 	}
 	
 	$title = elgg_echo("tidypics:yourmostviewed");