From 7b44f21a13862975853eb9848493923bbd0417c1 Mon Sep 17 00:00:00 2001 From: brettp Date: Thu, 21 Apr 2011 21:20:36 +0000 Subject: 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 --- engine/lib/annotations.php | 21 ++++++------- engine/lib/metastrings.php | 77 ++++++++++++++++++++++++++++++---------------- 2 files changed, 59 insertions(+), 39 deletions(-) (limited to 'engine') 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 "; -- cgit v1.2.3