diff options
Diffstat (limited to 'mostviewedimages.php')
| -rw-r--r-- | mostviewedimages.php | 22 |
1 files changed, 15 insertions, 7 deletions
diff --git a/mostviewedimages.php b/mostviewedimages.php index 8831dc33e..5bbbcf5e7 100644 --- a/mostviewedimages.php +++ b/mostviewedimages.php @@ -12,18 +12,26 @@ 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 . "metadata md - 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' - order by (views+0) desc LIMIT $max"; + + //this works but is wildly inefficient + //$annotations = get_annotations(0, "object", "image", "tp_view", "", "", 5000); + + $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' + 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 = "Most viewed images"; |