Created elgg_get_entities_from_metadata() and made get_entities_from_metadata().

Made elgg_get_entities() accept extra joins and wheres in the options.
Created a helper function elgg_normalise_plural_options_array() to rewrite shortcut singular options to plurals.


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

3 files changed, 346 insertions, 82 deletions

diff --git a/engine/lib/elgglib.php b/engine/lib/elgglib.php
index 351750b0b..2eecfefb6 100644
--- a/engine/lib/elgglib.php
+++ b/engine/lib/elgglib.php
@@ -2123,6 +2123,37 @@ function is_not_null($string) {
 	return true;
 }
 
+
+/**
+ * Normalise the singular keys in an options array
+ * to the plural keys.
+ *
+ * @param $options
+ * @param $singulars
+ * @return array
+ */
+function elgg_normalise_plural_options_array($options, $singulars) {
+	foreach ($singulars as $singular) {
+		$plural = $singular . 's';
+
+		// normalize the singular to plural
+		if (isset($options[$singular]) && $options[$singular] !== NULL && $options[$singular] !== FALSE) {
+			if (isset($options[$plural])) {
+				if (is_array($options[$plural])) {
+					$options[$plural][] = $options[$singlar];
+				} else {
+					$options[$plural] = array($options[$plural], $options[$singular]);
+				}
+			} else {
+				$options[$plural] = array($options[$singular]);
+			}
+		}
+		$options[$singular] = NULL;
+	}
+
+	return $options;
+}
+
 /**
  * Get the full URL of the current page.
  *
diff --git a/engine/lib/entities.php b/engine/lib/entities.php
index 8e8742d49..6b5176ee7 100644
--- a/engine/lib/entities.php
+++ b/engine/lib/entities.php
@@ -1640,6 +1640,8 @@ function get_entity($guid) {
  *
  * 	wheres => array() Additional where clauses to AND together
  *
+ * 	joins => array() Additional joins
+ *
  * @return array
  */
 function elgg_get_entities(array $options = array()) {
@@ -1647,12 +1649,17 @@ function elgg_get_entities(array $options = array()) {
 
 	//@todo allow use of singular types that rewrite to plural ones.
 	$defaults = array(
+		'type' => NULL,
 		'types' => NULL,
 		'subtypes' => NULL,
+		'subtype' => NULL,
 		'type_subtype_pairs' => NULL,
 		'owner_guids' => NULL,
+		'owner_guid' => NULL,
 		'container_guids' => NULL,
+		'container_guid' => NULL,
 		'site_guids' => $CONFIG->site_guid,
+		'site_guid' => NULL,
 
 		'order_by' => 'time_created desc',
 		'limit' => 10,
@@ -1664,11 +1671,16 @@ function elgg_get_entities(array $options = array()) {
 		'created_time_upper' => NULL,
 
 		'count' => FALSE,
-		'wheres' => array()
+		'wheres' => array(),
+		'joins' => array()
 	);
 
 	$options = array_merge($defaults, $options);
 
+	$singulars = array('type', 'subtype', 'owner_guid', 'container_guid', 'site_guid');
+	$options = elgg_normalise_plural_options_array($options, $singulars);
+
+	// evaluate where clauses
 	if (!is_array($options['wheres'])) {
 		$options['wheres'] = array($options['wheres']);
 	}
@@ -1682,6 +1694,9 @@ function elgg_get_entities(array $options = array()) {
 	$wheres[] = elgg_get_entity_time_where_sql('e', $options['created_time_upper'],
 		$options['created_time_lower'], $options['modified_time_upper'], $options['modified_time_lower']);
 
+	// remove identical where clauses
+	$wheres = array_unique($wheres);
+
 	// see if any functions failed
 	// remove empty strings on successful functions
 	foreach ($wheres as $i => $where) {
@@ -1692,18 +1707,40 @@ function elgg_get_entities(array $options = array()) {
 		}
 	}
 
+	// evaluate join clauses
+	if (!is_array($options['joins'])) {
+		$options['joins'] = array($options['joins']);
+	}
+
+	// remove identical join clauses
+	$joins = array_unique($options['joins']);
+
+	foreach ($joins as $i => $join) {
+		if ($join === FALSE) {
+			return FALSE;
+		} elseif (empty($join)) {
+			unset($joins[$i]);
+		}
+	}
+
 	if (!$options['count']) {
-		$query = "SELECT * FROM {$CONFIG->dbprefix}entities e WHERE";
+		$query = "SELECT DISTINCT e.* FROM {$CONFIG->dbprefix}entities e ";
 	} else {
-		$query = "SELECT count(guid) as total FROM {$CONFIG->dbprefix}entities e WHERE";
+		$query = "SELECT count(DISTINCT e.guid) as total FROM {$CONFIG->dbprefix}entities e ";
+	}
+
+	foreach ($joins as $j) {
+		$query .= " $j ";
 	}
 
+	$query .= ' WHERE ';
+
 	foreach ($wheres as $w) {
 		$query .= " $w AND ";
 	}
 
 	// Add access controls
-	$query .= get_access_sql_suffix();
+	$query .= get_access_sql_suffix('e');
 	if (!$options['count']) {
 		$order_by = sanitise_string($options['order_by']);
 		$query .= " ORDER BY $order_by";
@@ -1714,6 +1751,7 @@ function elgg_get_entities(array $options = array()) {
 			$query .= " LIMIT $offset, $limit";
 		}
 		$dt = get_data($query, "entity_row_to_elggstar");
+		//@todo normalize this to array()
 		return $dt;
 	} else {
 		$total = get_data_row($query);
@@ -1746,15 +1784,27 @@ $count = false, $site_guid = 0, $container_guid = null, $timelower = 0, $timeupp
 
 	$options = array();
 	if ($type) {
-		$options['types'] = $type;
+		if (is_array($type)) {
+			$options['types'] = $type;
+		} else {
+			$options['type'] = $type;
+		}
 	}
 
 	if ($subtype) {
-		$options['subtypes'] = $subtype;
+		if (is_array($subtype)) {
+			$options['subtypes'] = $subtype;
+		} else {
+			$options['subtype'] = $subtype;
+		}
 	}
 
 	if ($owner_guid) {
-		$options['owner_guids'] = $owner_guid;
+		if (is_array($owner_guid)) {
+			$options['owner_guids'] = $owner_guid;
+		} else {
+			$options['owner_guid'] = $owner_guid;
+		}
 	}
 
 	if ($order_by) {
@@ -1826,8 +1876,6 @@ function elgg_get_entity_type_subtype_where_sql($table, $types, $subtypes, $pair
 
 		if ($subtypes && !is_array($subtypes)) {
 			$subtypes = array($subtypes);
-		} else {
-			$subtypes = NULL;
 		}
 
 		// subtypes are based upon types, so we need to look at each
diff --git a/engine/lib/metadata.php b/engine/lib/metadata.php
index dc8107583..43f6ad5bf 100644
--- a/engine/lib/metadata.php
+++ b/engine/lib/metadata.php
@@ -532,9 +532,236 @@ function find_metadata($meta_name = "", $meta_value = "", $entity_type = "", $en
 	return get_data($query, "row_to_elggmetadata");
 }
 
+
+
+/**
+ * Get all entities.
+ *
+ * @param array $options Array in format:
+ *
+ * 	types => NULL|STR entity type
+ *
+ * 	subtypes => NULL|STR entity subtype
+ *
+ * 	type_subtype_pairs => NULL|ARR (type = '$type' AND subtype = '$subtype') pairs
+ *
+ * 	owner_guids => NULL|INT entity guid
+ *
+ * 	container_guids => NULL|INT container_guid
+ *
+ * 	site_guids => NULL (current_site)|INT site_guid
+ *
+ * 	order_by => NULL (time_created desc)|STR SQL order by clause
+ *
+ * 	limit => NULL (10)|INT SQL limit clause
+ *
+ * 	offset => NULL (0)|INT SQL offset clause
+ *
+ * 	time_lower => NULL|INT Time lower boundary in epoch time
+ *
+ * 	time_upper => NULL|INT Time upper boundary in epoch time
+ *
+ * 	count => TRUE|FALSE return a count instead of entities
+ *
+ * 	case_sensitive => TRUE|FALSE case sensitive metadata names
+ *
+ * @return array
+ */
+function elgg_get_entities_from_metadata(array $options = array()) {
+	$defaults = array(
+		'names' => NULL,
+		'name' => NULL,
+		'values' => NULL,
+		'value' => NULL,
+		'name_value_pairs' => NULL,
+		'case_sensitive' => TRUE
+	);
+
+	$options = array_merge($defaults, $options);
+
+	$singulars = array('name', 'value');
+	$options = elgg_normalise_plural_options_array($options, $singulars);
+
+	if (!is_array($options['wheres'])) {
+		$options['wheres'] = array();
+	}
+
+	$clauses = elgg_get_entity_metadata_where_sql('e', $options['names'],
+		$options['values'], $options['name_value_pairs'], $options['case_sensitive']);
+
+	// merge wheres to pass to get_entities()
+	if (isset($options['wheres']) && !is_array($options['wheres'])) {
+		$options['wheres'] = array($options['wheres']);
+	} elseif (!isset($options['wheres'])) {
+		$options['wheres'] = array();
+	}
+
+	$options['wheres'][] = $clauses['wheres'];
+
+	// merge joins to pass to get_entities()
+	if (isset($options['joins']) && !is_array($options['joins'])) {
+		$options['joins'] = array($options['joins']);
+	} elseif (!isset($options['joins'])) {
+		$options['joins'] = array();
+	}
+
+	$options['joins'] = array_merge($options['joins'], $clauses['joins']);
+
+	return elgg_get_entities($options);
+}
+
+/**
+ * Returns metadata name and value SQL where for entities.
+ * nb: $names and $values are not paired. Use $pairs for this.
+ * Pairs default to '=' operand.
+ *
+ * @param $names
+ * @param $values
+ * @return FALSE|array False on fail, array('joins', 'wheres')
+ */
+function elgg_get_entity_metadata_where_sql($prefix, $names = NULL, $values = NULL, $pairs = NULL, $case_sensitive = TRUE) {
+	global $CONFIG;
+
+	// short circuit if nothing requested
+	// 0 is a valid (if not ill-conceived) metadata name.
+	// 0 is also a valid metadata value for FALSE, NULL, or 0
+	if ((!$names && $names !== 0)
+		&& (!$values && $values !== 0)
+		&& (!$pairs && $pairs !== 0)) {
+		return '';
+	}
+
+	// binary forces byte-to-byte comparision of strings, making
+	// it case and diacritical mark sensitive.
+	// only supported on values.
+	$binary = ($case_sensitive) ? ' BINARY ' : '';
+
+	$access = get_access_sql_suffix('md');
+
+	$return = array (
+		'joins' => array (),
+		'wheres' => array()
+	);
+
+	// get names wheres and joins
+	$names_where = '';
+	if ($names !== NULL) {
+		$return['joins'][] = "JOIN {$CONFIG->dbprefix}metadata md on e.guid = md.entity_guid";
+		if (!is_array($names)) {
+			$names = array($names);
+		}
+
+		$sanitised_names = array();
+		foreach ($names as $name) {
+			// normalise to 0.
+			if (!$name) {
+				$name = '0';
+			}
+			$sanitised_names[] = "'$name'";
+		}
+
+		if ($names_str = implode(',', $sanitised_names)) {
+			$return['joins'][] = "JOIN {$CONFIG->dbprefix}metastrings msn on md.name_id = msn.id";
+			$names_where = "(msn.string IN ($names_str))";
+		}
+	}
+
+	// get values wheres and joins
+	$values_where = '';
+	if ($values !== NULL) {
+		$return['joins'][] = "JOIN {$CONFIG->dbprefix}metadata md on e.guid = md.entity_guid";
+
+		if (!is_array($values)) {
+			$values = array($values);
+		}
+
+		$sanitised_values = array();
+		foreach ($values as $value) {
+			// normalize to 0
+			if (!$value) {
+				$value = 0;
+			}
+			$sanitised_values[] = "'$value'";
+		}
+
+		if ($values_str = implode(',', $sanitised_values)) {
+			$return['joins'][] = "JOIN {$CONFIG->dbprefix}metastrings msv on md.value_id = msv.id";
+			$values_where = "({$binary}msv.string IN ($values_str))";
+		}
+	}
+
+	if ($names_where && $values_where) {
+		// @todo DECIDE IF AND OR OR!
+		// And rationale: Being more specific shouldn't give you more results, should give fewer.
+		$wheres[] = "($names_where AND $values_where AND $access)";
+	} elseif ($names_where) {
+		$wheres[] = "($names_where AND $access)";
+	} elseif ($values_where) {
+		$wheres[] = "($values_where AND $access)";
+	}
+
+	// add pairs
+	// pairs must be in arrays.
+	if (is_array($pairs)) {
+		$return['joins'][] = "JOIN {$CONFIG->dbprefix}metadata md on e.guid = md.entity_guid";
+		$return['joins'][] = "JOIN {$CONFIG->dbprefix}metastrings msn on md.name_id = msn.id";
+		$return['joins'][] = "JOIN {$CONFIG->dbprefix}metastrings msv on md.value_id = msv.id";
+
+		// check if this is an array of pairs or just a single pair.
+		if (isset($pairs['name']) || isset($pairs['value'])) {
+			$pairs = array($pairs);
+		}
+
+		$pair_wheres = array();
+
+		foreach ($pairs as $pair) {
+			// must have at least a name and value
+			if (!isset($pair['name']) || !isset($pair['value'])) {
+				// @todo should probably return false.
+				continue;
+			}
+
+			// case sensitivity can be specified per pair.
+			// default to higher level setting.
+			if (isset($pair['case_sensitive'])) {
+				$pair_binary = ($pair['case_sensitive']) ? ' BINARY ' : '';
+			} else {
+				$pair_binary = $binary;
+			}
+
+			if (isset($pair['operand'])) {
+				$operand = mysql_real_escape_string($pair['operand']);
+			} else {
+				$operand = ' = ';
+			}
+
+			// if the value is an int, don't quote it because str '15' < str '5'
+			// if the operand is IN don't quote it because quoting should be done already.
+			//$value = trim(strtolower($operand)) == 'in' ? $pair['value'] : "'{$pair['value']}'";
+			if (trim(strtolower($operand)) == 'in' || sanitise_int($pair['value'])) {
+				$value = $pair['value'];
+			} else {
+				$value = "'{$pair['value']}'";
+			}
+
+
+			$pair_wheres[] = "(msn.string = '{$pair['name']}' AND {$pair_binary}msv.string $operand $value)";
+		}
+
+		if ($where = implode (' OR ', $pair_wheres)) {
+			$wheres[] = "($where AND $access)";
+		}
+	}
+
+	$return['wheres'] = implode(' OR ', $wheres);
+
+	return $return;
+}
+
 /**
  * Return a list of entities based on the given search criteria.
  *
+ * @deprecated 1.7 use elgg_get_entities_from_metadata().
  * @param mixed $meta_name
  * @param mixed $meta_value
  * @param string $entity_type The type of entity to look for, eg 'site' or 'object'
@@ -548,97 +775,55 @@ function find_metadata($meta_name = "", $meta_value = "", $entity_type = "", $en
  *
  * @return int|array A list of entities, or a count if $count is set to true
  */
-function get_entities_from_metadata($meta_name, $meta_value = "", $entity_type = "", $entity_subtype = "", $owner_guid = 0, $limit = 10, $offset = 0, $order_by = "", $site_guid = 0, $count = FALSE, $case_sensitive = TRUE) {
-	global $CONFIG;
+function get_entities_from_metadata($meta_name, $meta_value = "", $entity_type = "", $entity_subtype = "",
+$owner_guid = 0, $limit = 10, $offset = 0, $order_by = "", $site_guid = 0,
+$count = FALSE, $case_sensitive = TRUE) {
 
-	$meta_n = get_metastring_id($meta_name);
-	$meta_v = get_metastring_id($meta_value, $case_sensitive);
+	$options = array();
 
-	$entity_type = sanitise_string($entity_type);
-	$entity_subtype_id = get_subtype_id($entity_type, $entity_subtype);
-	if ($entity_subtype != "" && $entity_subtype_id == FALSE) {
-		return false;
-	} else {
-		$entity_subtype = $entity_subtype_id;
-	}
-	
-	$limit = (int)$limit;
-	$offset = (int)$offset;
-	if ($order_by == "") {
-		$order_by = "e.time_created desc";
-	} else {
-		$order_by = "e.time_created, {$order_by}";
-	}
-	$order_by = sanitise_string($order_by);
-	$site_guid = (int) $site_guid;
-	if ((is_array($owner_guid) && (count($owner_guid)))) {
-		foreach($owner_guid as $key => $guid) {
-			$owner_guid[$key] = (int) $guid;
-		}
-	} else {
-		$owner_guid = (int) $owner_guid;
-	}
-	if ($site_guid == 0) {
-		$site_guid = $CONFIG->site_guid;
-	}
+	$options['names'] = $meta_name;
 
-	//$access = get_access_list();
-
-	$where = array();
+	if ($meta_value) {
+		$options['values'] = $meta_value;
+	}
 
-	if ($entity_type!=="") {
-		$where[] = "e.type='$entity_type'";
+	if ($entity_type) {
+		$options['types'] = $entity_type;
 	}
 
 	if ($entity_subtype) {
-		$where[] = "e.subtype=$entity_subtype";
+		$options['subtypes'] = $entity_subtype;
 	}
-	if ($meta_name!=="") {
-		$where[] = "m.name_id='$meta_n'";
+
+	if ($owner_guid) {
+		$options['owner'] = $owner_guid;
 	}
-	if ($meta_value!=="") {
-		if (is_array($meta_v)) {
-			$meta_v_string = "";
-			foreach ($meta_v as $v) {
-				$meta_v_string .= "'$v',";
-			}
-			$meta_v_string = rtrim($meta_v_string, ",");
-			$where[] = "m.value_id in ($meta_v_string)";
-		} else {
-			$where[] = "m.value_id='$meta_v'";
-		}
+
+	if ($limit) {
+		$options['limit'] = $limit;
 	}
-	if ($site_guid > 0) {
-		$where[] = "e.site_guid = {$site_guid}";
+
+	if ($offset) {
+		$options['offset'] = $offset;
 	}
-	if (is_array($owner_guid)) {
-		$where[] = "e.container_guid in (".implode(",",$owner_guid).")";
-	} else if ($owner_guid > 0) {
-		$where[] = "e.container_guid = {$owner_guid}";
+
+	if ($order_by) {
+		$options['order_by'];
 	}
 
-	if (!$count) {
-		$query = "SELECT distinct e.* ";
-	} else {
-		$query = "SELECT count(distinct e.guid) as total ";
+	if ($site_guid) {
+		$options['site_guid'];
 	}
 
-	$query .= "from {$CONFIG->dbprefix}entities e JOIN {$CONFIG->dbprefix}metadata m on e.guid = m.entity_guid where";
-	foreach ($where as $w) {
-		$query .= " $w and ";
+	if ($count) {
+		$options['count'] = $count;
 	}
-	$query .= get_access_sql_suffix("e"); // Add access controls
-	$query .= ' and ' . get_access_sql_suffix("m"); // Add access controls
 
-	if (!$count) {
-		$query .= " order by $order_by limit $offset, $limit"; // Add order and limit
-		return get_data($query, "entity_row_to_elggstar");
-	} else {
-		if ($row = get_data_row($query)) {
-			return $row->total;
-		}
+	if ($case_sensitive) {
+		$options['case_sensitive'] = $case_sensitive;
 	}
-	return false;
+
+	return elgg_get_entities_from_metadata($options);
 }
 
 /**