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);
info@elgg.org
Security issues should be reported to security@elgg.org!
©2014 the Elgg Foundation
Elgg is a registered trademark of Thematic Networks.
Cover image by RaĆ¼l Utrera is used under Creative Commons license.
Icons by Flaticon and FontAwesome.
- iionly@iionly
iionly - 0 likes
- gts fan9@gtsfan
gts fan9 - 0 likes
You must log in to post replies.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. :)