aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorbrettp <brettp@36083f99-b078-4883-b0ff-0f9b5a30f544>2011-04-21 21:20:36 +0000
committerbrettp <brettp@36083f99-b078-4883-b0ff-0f9b5a30f544>2011-04-21 21:20:36 +0000
commit7b44f21a13862975853eb9848493923bbd0417c1 (patch)
tree07c989eac0ed0dfc0be2d01a3126f268d20e00a7
parenta0e1576d2b50b7640637eff763fa077d2cda35bc (diff)
downloadelgg-7b44f21a13862975853eb9848493923bbd0417c1.tar.gz
elgg-7b44f21a13862975853eb9848493923bbd0417c1.tar.bz2
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
-rw-r--r--engine/lib/annotations.php21
-rw-r--r--engine/lib/metastrings.php77
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 ";