Fixes #1542: Pulled in Kevin's patch to add ability to sort by metadata. Added unit tests for this.

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

2 files changed, 172 insertions, 6 deletions

diff --git a/engine/lib/metadata.php b/engine/lib/metadata.php
index 67a135d75..1795116c0 100644
--- a/engine/lib/metadata.php
+++ b/engine/lib/metadata.php
@@ -569,7 +569,8 @@ function elgg_get_entities_from_metadata(array $options = array()) {
 		'metadata_name_value_pairs'	=>	ELGG_ENTITIES_ANY_VALUE,
 
 		'metadata_name_value_pairs_operator' => 'AND',
-		'metadata_case_sensitive' => TRUE
+		'metadata_case_sensitive' => TRUE,
+		'order_by_metadata' => array(),
 	);
 
 	$options = array_merge($defaults, $options);
@@ -578,7 +579,8 @@ function elgg_get_entities_from_metadata(array $options = array()) {
 	$options = elgg_normalise_plural_options_array($options, $singulars);
 
 	$clauses = elgg_get_entity_metadata_where_sql('e', $options['metadata_names'], $options['metadata_values'],
-		$options['metadata_name_value_pairs'], $options['metadata_name_value_pairs_operator'], $options['metadata_case_sensitive']);
+		$options['metadata_name_value_pairs'], $options['metadata_name_value_pairs_operator'], $options['metadata_case_sensitive'],
+		$options['order_by_metadata']);
 
 	if ($clauses) {
 		// merge wheres to pass to get_entities()
@@ -598,6 +600,15 @@ function elgg_get_entities_from_metadata(array $options = array()) {
 		}
 
 		$options['joins'] = array_merge($options['joins'], $clauses['joins']);
+
+		if ($clauses['orders']) {
+			$order_by_metadata = implode(", ",$clauses['orders']);
+			if (isset($options['order_by']) && $options['order_by']) {
+				$options['order_by'] = "$order_by_metadata, {$options['order_by']}";
+			} else {
+				$options['order_by'] = "$order_by_metadata, e.time_created DESC";
+			}
+		}
 	}
 
 	return elgg_get_entities($options);
@@ -614,9 +625,10 @@ function elgg_get_entities_from_metadata(array $options = array()) {
  * @param ARR|NULL $pairs array of names / values / operands
  * @param AND|OR $pair_operator Operator to use to join the where clauses for pairs
  * @param BOOL $case_sensitive
+ * @param ARR|NULL $order_by_metadata array of names / direction
  * @return FALSE|array False on fail, array('joins', 'wheres')
  */
-function elgg_get_entity_metadata_where_sql($table, $names = NULL, $values = NULL, $pairs = NULL, $pair_operator = 'AND', $case_sensitive = TRUE) {
+function elgg_get_entity_metadata_where_sql($table, $names = NULL, $values = NULL, $pairs = NULL, $pair_operator = 'AND', $case_sensitive = TRUE, $order_by_metadata = NULL) {
 	global $CONFIG;
 
 	// short circuit if nothing requested
@@ -624,7 +636,8 @@ function elgg_get_entity_metadata_where_sql($table, $names = NULL, $values = NUL
 	// 0 is also a valid metadata value for FALSE, NULL, or 0
 	if ((!$names && $names !== 0)
 		&& (!$values && $values !== 0)
-		&& (!$pairs && $pairs !== 0)) {
+		&& (!$pairs && $pairs !== 0)
+		&& !isset($order_by_metadata)) {
 		return '';
 	}
 
@@ -637,7 +650,8 @@ function elgg_get_entity_metadata_where_sql($table, $names = NULL, $values = NUL
 
 	$return = array (
 		'joins' => array (),
-		'wheres' => array()
+		'wheres' => array(),
+		'orders' => array()
 	);
 
 	$wheres = array();
@@ -697,6 +711,8 @@ function elgg_get_entity_metadata_where_sql($table, $names = NULL, $values = NUL
 		$wheres[] = "($values_where AND $access)";
 	}
 
+	$i = 1;
+
 	// add pairs
 	// pairs must be in arrays.
 	if (is_array($pairs)) {
@@ -709,7 +725,7 @@ function elgg_get_entity_metadata_where_sql($table, $names = NULL, $values = NUL
 
 		// @todo when the pairs are > 3 should probably split the query up to
 		// denormalize the strings table.
-		$i = 1;
+
 		foreach ($pairs as $index => $pair) {
 			// @todo move this elsewhere?
 			// support shortcut 'n' => 'v' method.
@@ -789,6 +805,36 @@ function elgg_get_entity_metadata_where_sql($table, $names = NULL, $values = NUL
 		$return['wheres'][] = "($where)";
 	}
 
+	if (is_array($order_by_metadata)) {
+		if ((count($order_by_metadata) > 0) && !is_array($order_by_metadata[0])) {
+			// singleton, so fix
+			$order_by_metadata = array($order_by_metadata);
+		}
+		foreach ($order_by_metadata as $order_by) {
+			if (is_array($order_by) && isset($order_by['name'])) {
+				$name = sanitise_string($order_by['name']);
+				if (isset($order_by['direction'])) {
+					$direction = sanitise_string($order_by['direction']);
+				} else {
+					$direction = 'ASC';
+				}
+				$return['joins'][] = "JOIN {$CONFIG->dbprefix}metadata md{$i} on {$table}.guid = md{$i}.entity_guid";
+				$return['joins'][] = "JOIN {$CONFIG->dbprefix}metastrings msn{$i} on md{$i}.name_id = msn{$i}.id";
+				$return['joins'][] = "JOIN {$CONFIG->dbprefix}metastrings msv{$i} on md{$i}.value_id = msv{$i}.id";
+
+				$access = get_access_sql_suffix("md{$i}");
+
+				$return['wheres'][] = "(msn{$i}.string = '$name' AND $access)";
+				if (isset($order_by['as']) && $order_by['as'] == 'integer') {
+					$return['orders'][] = "CAST(msv{$i}.string AS SIGNED) $direction";
+				} else {
+					$return['orders'][] = "msv{$i}.string $direction";
+				}
+				$i++;
+			}
+		}
+	}
+
 	return $return;
 }
 
diff --git a/engine/tests/api/entity_getter_functions.php b/engine/tests/api/entity_getter_functions.php
index 04fa3e4e5..49fd4ec0d 100644
--- a/engine/tests/api/entity_getter_functions.php
+++ b/engine/tests/api/entity_getter_functions.php
@@ -1610,4 +1610,124 @@ class ElggCoreEntityGetterFunctionsTest extends ElggCoreUnitTest {
 			}
 		}
 	}
+
+	function testElggApiGettersEntityMetadataNVPOrderByMDText() {
+		$subtypes = $this->getRandomValidSubtypes(array('object'), 1);
+		$subtype = $subtypes[0];
+		$md_name = 'test_metadata_name_' . rand();
+		$md_value = 2;
+		$guids = array();
+		$valid_guids = array();
+
+		// our targets
+		$valid = new ElggObject();
+		$valid->subtype = $subtype;
+		$valid->$md_name = $md_value;
+		$valid->save();
+		$guids[] = $valid->getGUID();
+		$valid_guids[] = $valid->getGUID();
+
+		$valid2 = new ElggObject();
+		$valid2->subtype = $subtype;
+		$valid2->$md_name = 3;
+		$valid2->save();
+		$guids[] = $valid->getGUID();
+		$valid_guids[] = $valid2->getGUID();
+
+		$valid3 = new ElggObject();
+		$valid3->subtype = $subtype;
+		$valid3->$md_name = 1;
+		$valid3->save();
+		$guids[] = $valid->getGUID();
+		$valid_guids[] = $valid3->getGUID();
+
+		$md_valid_values = array($md_value, $md_value2);
+
+		$options = array(
+			'type' => 'object',
+			'subtype' => $subtype,
+			//'metadata_name' => $md_name,
+			'order_by_metadata' => array('name' => $md_name, 'as' => 'integer')
+		);
+
+		$entities = elgg_get_entities_from_metadata($options);
+
+		$this->assertIsa($entities, 'array');
+		$this->assertEqual(count($entities), 3);
+
+		$i = 1;
+		foreach ($entities as $entity) {
+			$this->assertTrue(in_array($entity->getGUID(), $valid_guids));
+			$this->assertEqual($entity->$md_name, $i);
+			$i++;
+			$entity->delete();
+		}
+
+		foreach ($guids as $guid) {
+			if ($e = get_entity($guid)) {
+				$e->delete();
+			}
+		}
+	}
+
+	function testElggApiGettersEntityMetadataNVPOrderByMDString() {
+		$subtypes = $this->getRandomValidSubtypes(array('object'), 1);
+		$subtype = $subtypes[0];
+		$md_name = 'test_metadata_name_' . rand();
+		$md_value = 'b';
+		$guids = array();
+		$valid_guids = array();
+
+		// our targets
+		$valid = new ElggObject();
+		$valid->subtype = $subtype;
+		$valid->$md_name = $md_value;
+		$valid->save();
+		$guids[] = $valid->getGUID();
+		$valid_guids[] = $valid->getGUID();
+
+		$valid2 = new ElggObject();
+		$valid2->subtype = $subtype;
+		$valid2->$md_name = 'c';
+		$valid2->save();
+		$guids[] = $valid->getGUID();
+		$valid_guids[] = $valid2->getGUID();
+
+		$valid3 = new ElggObject();
+		$valid3->subtype = $subtype;
+		$valid3->$md_name = 'a';
+		$valid3->save();
+		$guids[] = $valid->getGUID();
+		$valid_guids[] = $valid3->getGUID();
+
+		$md_valid_values = array($md_value, $md_value2);
+
+		$options = array(
+			'type' => 'object',
+			'subtype' => $subtype,
+			'metadata_name' => $md_name,
+			'order_by_metadata' => array('name' => $md_name, 'as' => 'text')
+		);
+
+		$entities = elgg_get_entities_from_metadata($options);
+
+		$this->assertIsa($entities, 'array');
+		$this->assertEqual(count($entities), 3);
+
+		$alpha = array('a', 'b', 'c');
+
+		$i = 0;
+		foreach ($entities as $entity) {
+			$this->assertTrue(in_array($entity->getGUID(), $valid_guids));
+			$this->assertEqual($entity->$md_name, $alpha[$i]);
+			$i++;
+			$entity->delete();
+		}
+
+		foreach ($guids as $guid) {
+			if ($e = get_entity($guid)) {
+				$e->delete();
+			}
+		}
+	}
 }