Refs #3195. Selectively joining certain tables for performance with annotation calculations.

git-svn-id: http://code.elgg.org/elgg/trunk@9011 36083f99-b078-4883-b0ff-0f9b5a30f544

2 files changed, 59 insertions, 39 deletions

diff --git a/engine/lib/annotations.php b/engine/lib/annotations.php
index 7a4e62921..80ffbe74e 100644
--- a/engine/lib/annotations.php
+++ b/engine/lib/annotations.php
@@ -388,13 +388,10 @@ function elgg_list_entities_from_annotations($options = array()) {
 /**
  * Get entities ordered by a mathematical calculation on annotation values
  *
- * @todo I think at some point this could run through elgg_get_annotations() and pass a the callback
- * as entity_row_to_elggstar
- *
  * @param array $options An options array:
  * 	'annotation_calculation' => The calculation to use. Must be a valid MySQL function.
  *                              Defaults to sum.  Result selected as 'annotation_calculation'.
- *	'order_by'               => The order for the sorting. Defaults to 'calculation desc'.
+ *	'order_by'               => The order for the sorting. Defaults to 'annotation_calculation desc'.
  *	'annotation_names'       => The names of annotations on the entity.
  *	'annotation_values'	     => The values of annotations on the entity.
  *
@@ -404,9 +401,10 @@ function elgg_list_entities_from_annotations($options = array()) {
  * @return mixed
  */
 function elgg_get_entities_from_annotation_calculation($options) {
+	$db_prefix = elgg_get_config('dbprefix');
 	$defaults = array(
-		'calculation' => 'sum',
-		'order_by' => 'calculation desc'
+		'calculation'	=>	'sum',
+		'order_by'		=>	'annotation_calculation desc'
 	);
 
 	$options = array_merge($defaults, $options);
@@ -415,14 +413,13 @@ function elgg_get_entities_from_annotation_calculation($options) {
 
 	// you must cast this as an int or it sorts wrong.
 	$options['selects'][] = 'e.*';
-	$options['selects'][] = "$function(cast(v.string as signed)) as calculation";
+	$options['selects'][] = "$function(cast(a_msv.string as signed)) as annotation_calculation";
 
-	// need our own join to get the values.
-//	$options['joins'][] = "JOIN {$db_prefix}metastrings msv ON a.value_id = msv.id";
-//	$options['joins'][] = "JOIN {$db_prefix}entities ae ON a.entity_guid = ae.guid";
+	// need our own join to get the values because the lower level functions don't
+	// add all the joins if it's a different callback.
+	$options['joins'][] = "JOIN {$db_prefix}metastrings a_msv ON n_table.value_id = a_msv.id";
 
-	$options['wheres'][] = get_access_sql_suffix('n_table');
-//	$options['wheres'][] = "e.guid = a.entity_guid";
+	// don't need access control because it's taken care of by elgg_get_annotations.
 	$options['group_by'] = 'n_table.entity_guid';
 
 	$options['callback'] = 'entity_row_to_elggstar';
diff --git a/engine/lib/metastrings.php b/engine/lib/metastrings.php
index 655617ac6..d444121d0 100644
--- a/engine/lib/metastrings.php
+++ b/engine/lib/metastrings.php
@@ -211,23 +211,29 @@ function delete_orphaned_metastrings() {
  *
  * @param array $options Array in format:
  *
- * 	metastring_names => NULL|ARR metastring names
+ * 	metastring_names              => NULL|ARR metastring names
  *
- * 	metastring_values => NULL|ARR metastring values
+ * 	metastring_values             => NULL|ARR metastring values
  *
- * 	metastring_ids => NULL|ARR metastring ids
+ * 	metastring_ids                => NULL|ARR metastring ids
  *
- * 	metastring_case_sensitive => BOOL Overall Case sensitive
+ * 	metastring_case_sensitive     => BOOL     Overall Case sensitive
  *
- *  metastring_owner_guids => NULL|ARR guids for metadata owners
+ *  metastring_owner_guids        => NULL|ARR Guids for metadata owners
  *
- *  metastring_created_time_lower => INT Lower limit for created time.
+ *  metastring_created_time_lower => INT      Lower limit for created time.
  *
- *  metastring_created_time_upper => INT Upper limit for created time.
+ *  metastring_created_time_upper => INT      Upper limit for created time.
  *
- *  metastring_calculation => STR Perform the MySQL function on the metastring values returned.
+ *  metastring_calculation        => STR      Perform the MySQL function on the metastring values
+ *                                            returned.
+ *                                            This differs from egef_annotation_calculation in that
+ *                                            it returns only the calculation of all annotation values.
+ *                                            You can sum, avg, count, etc. egef_annotation_calculation()
+ *                                            returns ElggEntities ordered by a calculation on their
+ *                                            annotation values.
  *
- *  metastring_type => STR metadata or annotation(s)
+ *  metastring_type               => STR      metadata or annotation(s)
  *
  * @return mixed
  * @access private
@@ -373,14 +379,35 @@ function elgg_get_metastring_based_objects($options) {
 	}
 
 	$joins = $options['joins'];
-
 	$joins[] = "JOIN {$db_prefix}entities e ON n_table.entity_guid = e.guid";
-	$joins[] = "JOIN {$db_prefix}metastrings n on n_table.name_id = n.id";
-	$joins[] = "JOIN {$db_prefix}metastrings v on n_table.value_id = v.id";
 
+	// evaluate selects
+	if (!is_array($options['selects'])) {
+		$options['selects'] = array($options['selects']);
+	}
 
-	// remove identical join clauses
-	$joins = array_unique($joins);
+	$selects = $options['selects'];
+
+	// allow count shortcut
+	if ($options['count']) {
+		$options['metastring_calculation'] = 'count';
+	}
+
+	// For performance reasons we don't want the joins required for metadata / annotations
+	// unless we're going through one of their callbacks.
+	// this means we expect the functions passing different callbacks to pass their required joins.
+	// If we're doing a calculation
+	$custom_callback = ($options['callback'] == 'row_to_elggmetadata'
+						|| $options['callback'] == 'row_to_elggannotation');
+	$is_calculation = $options['metastring_calculation'] ? true : false;
+	
+	if ($custom_callback || $is_calculation) {
+		$joins[] = "JOIN {$db_prefix}metastrings n on n_table.name_id = n.id";
+		$joins[] = "JOIN {$db_prefix}metastrings v on n_table.value_id = v.id";
+
+		$selects[] = 'n.string as name';
+		$selects[] = 'v.string as value';
+	}
 
 	foreach ($joins as $i => $join) {
 		if ($join === FALSE) {
@@ -400,28 +427,24 @@ function elgg_get_metastring_based_objects($options) {
 		$joins = array_merge($joins, $metastring_clauses['joins']);
 	}
 
-	// check for calculations
-	if ($options['count']) {
-		$options['metastring_calculation'] = 'count';
-	}
-
 	if ($options['metastring_calculation'] === ELGG_ENTITIES_NO_VALUE) {
+		$selects = array_unique($selects);
 		// evalutate selects
-		if ($options['selects']) {
-			$selects = '';
-			foreach ($options['selects'] as $select) {
-				$selects .= ", $select";
+		$select_str = '';
+		if ($selects) {
+			foreach ($selects as $select) {
+				$select_str .= ", $select";
 			}
-		} else {
-			$selects = '';
 		}
 
-		$query = "SELECT DISTINCT n_table.*, n.string as name,
-			v.string as value{$selects} FROM {$db_prefix}$type n_table";
+		$query = "SELECT DISTINCT n_table.*{$select_str} FROM {$db_prefix}$type n_table";
 	} else {
 		$query = "SELECT {$options['metastring_calculation']}(v.string) as calculation FROM {$db_prefix}$type n_table";
 	}
 
+	// remove identical join clauses
+	$joins = array_unique($joins);
+
 	// add joins
 	foreach ($joins as $j) {
 		$query .= " $j ";