diff options
| -rw-r--r-- | engine/lib/annotations.php | 372 | ||||
| -rw-r--r-- | engine/lib/entities.php | 32 | ||||
| -rw-r--r-- | engine/lib/metadata.php | 2 |
3 files changed, 313 insertions, 93 deletions
diff --git a/engine/lib/annotations.php b/engine/lib/annotations.php index f6535a036..13d9bfdc5 100644 --- a/engine/lib/annotations.php +++ b/engine/lib/annotations.php @@ -407,134 +407,336 @@ $value = "", $owner_guid = 0, $limit = 10, $offset = 0, $order_by = "asc", $time return get_data($query, "row_to_elggannotation"); } + /** - * Return a list of entities which are annotated with a specific annotation. - * These can be ordered by when the annotation was created/updated. * - * @param string $entity_type Type of entity. - * @param string $entity_subtype Subtype of entity. - * @param string $name Name of annotation. - * @param string $value Value of annotation. - * @param int $owner_guid Owner. - * @param int $group_guid Group container. Currently this is only supported if $entity_type == 'object' - * @param int $limit Maximum number of results to return. - * @param int $offset Place to start. - * @param string $order_by How to order results. - * @param boolean $count Whether to count entities rather than return them - * @param int $timelower The earliest time the annotation can have been created. Default: all - * @param int $timeupper The latest time the annotation can have been created. Default: all + * @todo Add support for arrays of names and values + * + * @param $options + * @return unknown_type */ -function get_entities_from_annotations($entity_type = "", $entity_subtype = "", $name = "", $value = "", $owner_guid = 0, $group_guid = 0, $limit = 10, $offset = 0, $order_by = "asc", $count = false, $timelower = 0, $timeupper = 0) { - global $CONFIG; +function elgg_get_entities_from_annotations(array $options = array()) { + $defaults = array( + 'annotation_names' => NULL, + 'annotation_name' => NULL, + 'annotation_values' => NULL, + 'annotation_value' => NULL, + 'annotation_name_value_pair' => NULL, + 'annotation_name_value_pairs' => NULL, + 'annotation_name_value_pairs_operator' => 'AND', + 'annotation_case_sensitive' => TRUE + ); + + $options = array_merge($defaults, $options); + + $singulars = array('annotation_name', 'annotation_value', 'annotation_name_value_pair'); + $options = elgg_normalise_plural_options_array($options, $singulars); + + $clauses = elgg_get_entity_annotation_where_sql('e', $options['annotation_names'], $options['annotation_values'], + $options['annotation_name_value_pairs'], $options['annotation_name_value_pairs_operator'], $options['annotation_case_sensitive']); + + if ($clauses) { + // 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(); + } - $entity_type = sanitise_string($entity_type); - $entity_subtype = get_subtype_id($entity_type, $entity_subtype); - $timelower = (int) $timelower; - $timeupper = (int) $timeupper; + $options['wheres'] = array_merge($options['wheres'], $clauses['wheres']); - if ($name) { - $name = get_metastring_id($name); + // 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(); + } - if ($name === false) { - $name = 0; + $options['joins'] = array_merge($options['joins'], $clauses['joins']); + + // merge selects to pass to get_entities() + if (isset($options['selects']) && !is_array($options['selects'])) { + $options['selects'] = array($options['selects']); + } elseif (!isset($options['selects'])) { + $options['selects'] = array(); + } + + $options['selects'] = array_merge($options['selects'], $clauses['selects']); + + // @todo overwrites the current order and group bys + if ($clauses['order_by']) { + $options['order_by'] = $clauses['order_by']; + } + if ($clauses['group_by']) { + $options['group_by'] = $clauses['group_by']; } } - if ($value != "") { - $value = get_metastring_id($value); + + return elgg_get_entities($options); +} + +/** + * Returns annotation name and value SQL where for entities. + * nb: $names and $values are not paired. Use $pairs for this. + * Pairs default to '=' operand. + * + * @param $prefix + * @param ARR|NULL $names + * @param ARR|NULL $values + * @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 + * @return FALSE|array False on fail, array('joins', 'wheres') + */ +function elgg_get_entity_annotation_where_sql($table, $names = NULL, $values = NULL, $pairs = NULL, $pair_operator = 'AND', $case_sensitive = TRUE) { + global $CONFIG; + + // short circuit if nothing requested + // 0 is a valid (if not ill-conceived) annotation name. + // 0 is also a valid annotation value for FALSE, NULL, or 0 + if ((!$names && $names !== 0) + && (!$values && $values !== 0) + && (!$pairs && $pairs !== 0)) { + return ''; } - if (is_array($owner_guid)) { - if (sizeof($owner_guid) > 0) { - foreach($owner_guid as $key => $val) { - $owner_guid[$key] = (int) $val; + + // 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('a'); + + $return = array ( + 'joins' => array (), + 'wheres' => array(), + 'selects' => array() + ); + + $wheres = array(); + + // get names wheres and joins + $names_where = ''; + if ($names !== NULL) { + $return['joins'][] = "JOIN {$CONFIG->dbprefix}annotations a on {$table}.guid = a.entity_guid"; + if (!is_array($names)) { + $names = array($names); + } + + $sanitised_names = array(); + foreach ($names as $name) { + // normalise to 0. + if (!$name) { + $name = '0'; } - } else { - $owner_guid = 0; + $sanitised_names[] = "'$name'"; } - } else { - $owner_guid = (int)$owner_guid; - } - $group_guid = (int)$group_guid; - $limit = (int)$limit; - $offset = (int)$offset; - if($order_by == 'asc') { - $order_by = "maxtime asc"; + if ($names_str = implode(',', $sanitised_names)) { + $return['joins'][] = "JOIN {$CONFIG->dbprefix}metastrings msn on a.name_id = msn.id"; + $names_where = "(msn.string IN ($names_str))"; + } } - if($order_by == 'desc') { - $order_by = "maxtime desc"; - } + // get values wheres and joins + $values_where = ''; + if ($values !== NULL) { + $return['joins'][] = "JOIN {$CONFIG->dbprefix}annotations a on {$table}.guid = a.entity_guid"; - $where = array(); + if (!is_array($values)) { + $values = array($values); + } - if ($entity_type != "") { - $where[] = "e.type='$entity_type'"; + $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 a.value_id = msv.id"; + $values_where = "({$binary}msv.string IN ($values_str))"; + } } - if ($entity_subtype != "") { - $where[] = "e.subtype='$entity_subtype'"; + if ($names_where && $values_where) { + $wheres[] = "($names_where AND $values_where AND $access)"; + } elseif ($names_where) { + $wheres[] = "($names_where AND $access)"; + } elseif ($values_where) { + $wheres[] = "($values_where AND $access)"; } - if ($owner_guid != 0 && !is_array($owner_guid)) { - $where[] = "a.owner_guid=$owner_guid"; - } else { - if (is_array($owner_guid)) { - $where[] = "a.owner_guid in (" . implode(",",$owner_guid) . ")"; + // add pairs + // pairs must be in arrays. + if (is_array($pairs)) { + $array = array( + 'name' => 'test', + 'value' => 5 + ); + + $array = array('test' => 5); + + // check if this is an array of pairs or just a single pair. + if (isset($pairs['name']) || isset($pairs['value'])) { + $pairs = array($pairs); } - } - if (($group_guid != 0) && ($entity_type=='object')) { - $where[] = "e.container_guid = $group_guid"; - } + $pair_wheres = array(); + + // @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. + if (!is_array($pair)) { + $pair = array( + 'name' => $index, + 'value' => $pair + ); + } - if ($name !== "") { - $where[] = "a.name_id='$name'"; + // @todo The multiple joins are only needed when the operator is AND + $return['joins'][] = "JOIN {$CONFIG->dbprefix}annotations a{$i} on {$table}.guid = a{$i}.entity_guid"; + $return['joins'][] = "JOIN {$CONFIG->dbprefix}metastrings msn{$i} on a{$i}.name_id = msn{$i}.id"; + $return['joins'][] = "JOIN {$CONFIG->dbprefix}metastrings msv{$i} on a{$i}.value_id = msv{$i}.id"; + + // 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']}'"; + } + + $access = get_access_sql_suffix("a{$i}"); + $pair_wheres[] = "(msn{$i}.string = '{$pair['name']}' AND {$pair_binary}msv{$i}.string $operand $value AND $access)"; + $i++; + } + + if ($where = implode (" $pair_operator ", $pair_wheres)) { + $wheres[] = "($where)"; + } } - if ($value != "") { - $where[] = "a.value_id='$value'"; + if ($where = implode(' OR ', $wheres)) { + $return['selects'][] = "max(a.time_created) as maxtime"; + $return['wheres'][] = "($where)"; + $return['group_by'] = 'a.entity_guid'; + $return['order_by'] = 'maxtime asc'; } - if ($timelower) { - $where[] = "a.time_created >= {$timelower}"; + return $return; +} + +/** + * Return a list of entities which are annotated with a specific annotation. + * These can be ordered by when the annotation was created/updated. + * + * @param string $entity_type Type of entity. + * @param string $entity_subtype Subtype of entity. + * @param string $name Name of annotation. + * @param string $value Value of annotation. + * @param int $owner_guid Owner. + * @param int $group_guid Group container. Currently this is only supported if $entity_type == 'object' + * @param int $limit Maximum number of results to return. + * @param int $offset Place to start. + * @param string $order_by How to order results. + * @param boolean $count Whether to count entities rather than return them + * @param int $timelower The earliest time the annotation can have been created. Default: all + * @param int $timeupper The latest time the annotation can have been created. Default: all + */ + + +/** + * @deprecated 1.7 Use elgg_get_entities_from_annotations() + * @param $entity_type + * @param $entity_subtype + * @param $name + * @param $value + * @param $owner_guid + * @param $group_guid + * @param $limit + * @param $offset + * @param $order_by + * @param $count + * @param $timelower + * @param $timeupper + * @return unknown_type + */ +function get_entities_from_annotations($entity_type = "", $entity_subtype = "", $name = "", $value = "", $owner_guid = 0, $group_guid = 0, $limit = 10, $offset = 0, $order_by = "asc", $count = false, $timelower = 0, $timeupper = 0) { + + elgg_log('get_entities_from_annotations() was deprecated in 1.7 by elgg_get_entities_from_annotations()!', 'WARNING'); + + $options = array(); + + $options['annotation_names'] = $name; + + if ($value) { + $options['annotation_values'] = $value; } - if ($timeupper) { - $where[] = "a.time_created <= {$timeupper}"; + if ($entity_type) { + $options['types'] = $entity_type; } - if ($count) { - $query = "SELECT count(distinct e.guid) as total "; - } else { - $query = "SELECT e.*, max(a.time_created) as maxtime "; + if ($entity_subtype) { + $options['subtypes'] = $entity_subtype; } - $query .= "FROM {$CONFIG->dbprefix}annotations a - JOIN {$CONFIG->dbprefix}entities e on e.guid = a.entity_guid "; + if ($owner_guid) { + $options['owner'] = $owner_guid; + } - if ($value != "") { - $query .= " JOIN {$CONFIG->dbprefix}metastrings v on a.value_id=v.id "; + if ($limit) { + $options['limit'] = $limit; } - if (($group_guid != 0) && ($entity_type=='object')) { - $query .= "JOIN {$CONFIG->dbprefix}objects_entity o on o.guid = e.guid"; + if ($offset) { + $options['offset'] = $offset; } - $query .= " where"; - foreach ($where as $w) { - $query .= " $w and "; + if ($order_by) { + $options['order_by']; } - $query .= get_access_sql_suffix("a"); // Add access controls - $query .= ' and ' . get_access_sql_suffix("e"); // Add access controls + if ($site_guid) { + $options['site_guid']; + } if ($count) { - $row = get_data_row($query); - return $row->total; - } else { - $query .= " group by a.entity_guid order by $order_by limit $offset,$limit"; // Add order and limit - return get_data($query, "entity_row_to_elggstar"); + $options['count'] = $count; } + + // need to be able to pass false + $options['annotations_case_sensitive'] = $case_sensitive; + + return elgg_get_entities_from_annotations($options); } /** diff --git a/engine/lib/entities.php b/engine/lib/entities.php index 3c2adc5ed..b1c09316b 100644 --- a/engine/lib/entities.php +++ b/engine/lib/entities.php @@ -1661,16 +1661,17 @@ function elgg_get_entities(array $options = array()) { 'site_guids' => $CONFIG->site_guid, 'site_guid' => NULL, - 'order_by' => 'time_created desc', - 'limit' => 10, - 'offset' => 0, - 'modified_time_lower' => NULL, 'modified_time_upper' => NULL, 'created_time_lower' => NULL, 'created_time_upper' => NULL, + 'order_by' => 'e.time_created desc', + 'group_by' => NULL, + 'limit' => 10, + 'offset' => 0, 'count' => FALSE, + 'selects' => array(), 'wheres' => array(), 'joins' => array() ); @@ -1723,16 +1724,28 @@ function elgg_get_entities(array $options = array()) { } } + // evalutate selects + if ($options['selects']) { + $selects = ''; + foreach ($options['selects'] as $select) { + $selects = ", $select"; + } + } else { + $selects = ''; + } + if (!$options['count']) { - $query = "SELECT DISTINCT e.* FROM {$CONFIG->dbprefix}entities e "; + $query = "SELECT DISTINCT e.*{$selects} FROM {$CONFIG->dbprefix}entities e "; } else { $query = "SELECT count(DISTINCT e.guid) as total FROM {$CONFIG->dbprefix}entities e "; } + // add joins foreach ($joins as $j) { $query .= " $j "; } + // add wheres $query .= ' WHERE '; foreach ($wheres as $w) { @@ -1742,8 +1755,13 @@ function elgg_get_entities(array $options = array()) { // Add access controls $query .= get_access_sql_suffix('e'); if (!$options['count']) { - $order_by = sanitise_string($options['order_by']); - $query .= " ORDER BY $order_by"; + if ($group_by = sanitise_string($options['group_by'])) { + $query .= " GROUP BY $group_by"; + } + + if ($order_by = sanitise_string($options['order_by'])) { + $query .= " ORDER BY $order_by"; + } if ($options['limit']) { $limit = sanitise_int($options['limit']); diff --git a/engine/lib/metadata.php b/engine/lib/metadata.php index 342910d47..3803793ed 100644 --- a/engine/lib/metadata.php +++ b/engine/lib/metadata.php @@ -790,7 +790,7 @@ function get_entities_from_metadata($meta_name, $meta_value = "", $entity_type = $owner_guid = 0, $limit = 10, $offset = 0, $order_by = "", $site_guid = 0, $count = FALSE, $case_sensitive = TRUE) { - elgg_log('get_entities_from_metadata() was deprecated in 1.7 by elgg_get_entities()!', 'WARNING'); + elgg_log('get_entities_from_metadata() was deprecated in 1.7 by elgg_get_entities_from_metadata()!', 'WARNING'); $options = array(); |