How to display entities in order of most recent comments added to them?

Sorry for the very lengthy posting... This is a problem I currently have with the Tidypics plugin as the implemented code used does not fully work as intended. It's also not necessarily limited to the "order of recent comments" or the Tidypics plugin but the question is the same for any type of entity to be sorted by the order of some type of annotation connected with these entities. For example in the Tidypics plugin the same code is also used to list images in the order of most recently viewed.

Currently the code for getting the relevant entities is:

$prefix = elgg_get_config('dbprefix');
$max = 16; //the number of images you want to be displayed
$max_limit = 200; //get extra because you'll have multiple views per image in the result set

$sql = "SELECT distinct (ent.guid), ann1.time_created
                        FROM " . $prefix . "entities 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 = 'generic_comment'
                        ORDER BY ann1.time_created DESC
                        LIMIT $max_limit";

$result = get_data($sql);

$entities = array();
foreach ($result as $entity) {
        if (!$entities[$entity->guid]) {
                $entities[$entity->guid] = get_entity($entity->guid);
        }
        if (count($entities) >= $max) {
                break;
        }
}

The problematic points with this code are that you get results the user might not have the right to see (and which result in the subsequent elgg_view_entity_list() call to fail) and also it's not a good solution to get more than only a few results (as you need to filter out the desired $max images from the $max_limit number of results which could contain the same images several times depending on the comments posted to them).

What I have tried instead:

$prefix = elgg_get_config('dbprefix');
$max = 16; //the number of images you want to be displayed
$options = array('types' => 'object',
                 'subtypes' => 'image',
                 'limit' => $max,
                 'offset' => $offset,
                 'joins' => array("join {$prefix}annotations ann1 ON ann1.entity_guid = e.guid",
                                  "join {$prefix}metastrings ms ON ms.id = ann1.name_id AND ms.string = 'generic_comment'"),
                 'order_by' => "n_table.time_created desc",
                 'full_view' => false,
                 'list_type' => 'gallery',
                 'gallery_class' => 'tidypics-gallery'
                );

$result = elgg_list_entities_from_metadata($options);

and also:

$options = array('type' => 'object',
                 'subtype' => 'image',
                 'owner_guid' => null,
                 'limit' => $max_limit,
                 'offset' => $offset,
                 'annotation_name' => 'generic_comment',
                 'annotation_owner_guid' => null,
                 'order_by' => "n_table.time_created desc",
                 'full_view' => false,
                 'list_type' => 'gallery',
                 'gallery_class' => 'tidypics-gallery'
                );

$result = elgg_list_entities_from_annotations($options);

Basically, the same query using two different Elgg functions. The good thing is that you only get results the user is allowed to see and you get each image only once but unfortunately the result is not 100% correct. Instead of ordering the images based on the last comment added to them it orders them by the first comment added to each of them not considering any other comments. This means with two pictures A and B and adding first a comment to A and then B and then again to A you get the order B before A (because the first comment added to B is newer than the first comment added to A neglecting the second comment added to A).

I would be most thankful if someone could help me out here.

 

Bonus questions:

The queries used in the Tidypics plugin involving ordering by votes (most recent votes, hightest vote counts, highest average vote) have some additional joins that would need to be considered. Maybe I can already conclude how the code would need to be modified when I know how the above query for ordering on recent comments would have to be. But if you could also help me out with getting the following queries to work it would be even better.

So, the currently used code for ordering based on most recent votes is:

$sql = "SELECT ent.guid, u2.name AS owner, u.name AS voter, ms2.string as vote
             FROM " . $prefix . "entities 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 = 'fivestar'
             INNER JOIN " . $prefix . "metastrings ms2 ON ms2.id = ann1.value_id
             INNER JOIN " . $prefix . "users_entity u ON ann1.owner_guid = u.guid
             INNER JOIN " . $prefix . "users_entity u2 ON ent.owner_guid = u2.guid
             ORDER BY ann1.time_created DESC
             LIMIT $max_limit";
$result = get_data($sql);

Then there's the query for ordering on highest number of votes:

$sql = "SELECT ent.guid, u.name AS owner, count(1) AS mycount, avg( ms2.string ) AS average
             FROM " . $prefix . "entities 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 = 'fivestar'
             INNER JOIN " . $prefix . "metastrings ms2 ON ms2.id = ann1.value_id
             INNER JOIN " . $prefix . "users_entity u ON ent.owner_guid = u.guid
             GROUP BY ent.guid
             ORDER BY mycount DESC
             LIMIT $max_limit";

$result = get_data($sql);

and the ordering by highest average vote:

$sql = "SELECT ent.guid, count(1) AS mycount, avg(ms2.string) AS average
            FROM " . $prefix . "entities 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 = 'fivestar'
            INNER JOIN " . $prefix . "metastrings ms2 ON ms2.id = ann1.value_id
            INNER JOIN " . $prefix . "users_entity u ON ann1.owner_guid = u.guid
            GROUP BY ent.guid HAVING mycount > 1
            ORDER BY average DESC
            LIMIT $max_limit";

$result = get_data($sql);

  • Consider this topic closed due to lack of response...

    ...only joking. I found out how it works in the meantime. Should be implemented in the next Tidypics release.

  • I tried looking a little for you last night but you probably know 1,000x (maybe 10,000) what I do about it.  I saw time_updated (http://docs.elgg.org/wiki/DatabaseSchema) but I don't believe that helps in your case because I'm presuming each annotation is separate and the original entity probably isn't getting updated in that way when a comment is added so that you can't read it's time_updated?   Anyway good luck with the release. :)