RelationshipsTable.php 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445
  1. <?php
  2. namespace Elgg\Database;
  3. /**
  4. * WARNING: API IN FLUX. DO NOT USE DIRECTLY.
  5. *
  6. * @access private
  7. *
  8. * @package Elgg.Core
  9. * @subpackage Database
  10. * @since 1.10.0
  11. */
  12. class RelationshipsTable {
  13. /**
  14. * Global Elgg configuration
  15. *
  16. * @var \stdClass
  17. */
  18. private $CONFIG;
  19. /**
  20. * Constructor
  21. */
  22. public function __construct() {
  23. global $CONFIG;
  24. $this->CONFIG = $CONFIG;
  25. }
  26. /**
  27. * Get a relationship by its ID
  28. *
  29. * @param int $id The relationship ID
  30. *
  31. * @return \ElggRelationship|false False if not found
  32. */
  33. function get($id) {
  34. $row = _elgg_get_relationship_row($id);
  35. if (!$row) {
  36. return false;
  37. }
  38. return new \ElggRelationship($row);
  39. }
  40. /**
  41. * Get a database row from the relationship table
  42. *
  43. * @param int $id The relationship ID
  44. *
  45. * @return \stdClass|false False if no row found
  46. * @access private
  47. */
  48. function getRow($id) {
  49. $id = (int)$id;
  50. return _elgg_services()->db->getDataRow("SELECT * FROM {$this->CONFIG->dbprefix}entity_relationships WHERE id = $id");
  51. }
  52. /**
  53. * Delete a relationship by its ID
  54. *
  55. * @param int $id The relationship ID
  56. *
  57. * @return bool
  58. */
  59. function delete($id) {
  60. $id = (int)$id;
  61. $relationship = get_relationship($id);
  62. if (_elgg_services()->events->trigger('delete', 'relationship', $relationship)) {
  63. return _elgg_services()->db->deleteData("DELETE FROM {$this->CONFIG->dbprefix}entity_relationships WHERE id = $id");
  64. }
  65. return false;
  66. }
  67. /**
  68. * Create a relationship between two entities. E.g. friendship, group membership, site membership.
  69. *
  70. * This function lets you make the statement "$guid_one is a $relationship of $guid_two". In the statement,
  71. * $guid_one is the subject of the relationship, $guid_two is the target, and $relationship is the type.
  72. *
  73. * @param int $guid_one GUID of the subject entity of the relationship
  74. * @param string $relationship Type of the relationship
  75. * @param int $guid_two GUID of the target entity of the relationship
  76. *
  77. * @return bool
  78. * @throws \InvalidArgumentException
  79. */
  80. function add($guid_one, $relationship, $guid_two) {
  81. if (strlen($relationship) > \ElggRelationship::RELATIONSHIP_LIMIT) {
  82. $msg = "relationship name cannot be longer than " . \ElggRelationship::RELATIONSHIP_LIMIT;
  83. throw new \InvalidArgumentException($msg);
  84. }
  85. $guid_one = (int)$guid_one;
  86. $relationship = sanitise_string($relationship);
  87. $guid_two = (int)$guid_two;
  88. $time = time();
  89. // Check for duplicates
  90. if (check_entity_relationship($guid_one, $relationship, $guid_two)) {
  91. return false;
  92. }
  93. $id = _elgg_services()->db->insertData("INSERT INTO {$this->CONFIG->dbprefix}entity_relationships
  94. (guid_one, relationship, guid_two, time_created)
  95. VALUES ($guid_one, '$relationship', $guid_two, $time)");
  96. if ($id !== false) {
  97. $obj = get_relationship($id);
  98. // this event has been deprecated in 1.9. Use 'create', 'relationship'
  99. $result_old = _elgg_services()->events->trigger('create', $relationship, $obj);
  100. $result = _elgg_services()->events->trigger('create', 'relationship', $obj);
  101. if ($result && $result_old) {
  102. return true;
  103. } else {
  104. delete_relationship($obj->id);
  105. }
  106. }
  107. return false;
  108. }
  109. /**
  110. * Check if a relationship exists between two entities. If so, the relationship object is returned.
  111. *
  112. * This function lets you ask "Is $guid_one a $relationship of $guid_two?"
  113. *
  114. * @param int $guid_one GUID of the subject entity of the relationship
  115. * @param string $relationship Type of the relationship
  116. * @param int $guid_two GUID of the target entity of the relationship
  117. *
  118. * @return \ElggRelationship|false Depending on success
  119. */
  120. function check($guid_one, $relationship, $guid_two) {
  121. $guid_one = (int)$guid_one;
  122. $relationship = sanitise_string($relationship);
  123. $guid_two = (int)$guid_two;
  124. $query = "SELECT * FROM {$this->CONFIG->dbprefix}entity_relationships
  125. WHERE guid_one=$guid_one
  126. AND relationship='$relationship'
  127. AND guid_two=$guid_two limit 1";
  128. $row = row_to_elggrelationship(_elgg_services()->db->getDataRow($query));
  129. if ($row) {
  130. return $row;
  131. }
  132. return false;
  133. }
  134. /**
  135. * Delete a relationship between two entities.
  136. *
  137. * This function lets you say "$guid_one is no longer a $relationship of $guid_two."
  138. *
  139. * @param int $guid_one GUID of the subject entity of the relationship
  140. * @param string $relationship Type of the relationship
  141. * @param int $guid_two GUID of the target entity of the relationship
  142. *
  143. * @return bool
  144. */
  145. function remove($guid_one, $relationship, $guid_two) {
  146. $guid_one = (int)$guid_one;
  147. $relationship = sanitise_string($relationship);
  148. $guid_two = (int)$guid_two;
  149. $obj = check_entity_relationship($guid_one, $relationship, $guid_two);
  150. if ($obj == false) {
  151. return false;
  152. }
  153. // this event has been deprecated in 1.9. Use 'delete', 'relationship'
  154. $result_old = _elgg_services()->events->trigger('delete', $relationship, $obj);
  155. $result = _elgg_services()->events->trigger('delete', 'relationship', $obj);
  156. if ($result && $result_old) {
  157. $query = "DELETE FROM {$this->CONFIG->dbprefix}entity_relationships
  158. WHERE guid_one = $guid_one
  159. AND relationship = '$relationship'
  160. AND guid_two = $guid_two";
  161. return (bool)_elgg_services()->db->deleteData($query);
  162. } else {
  163. return false;
  164. }
  165. }
  166. /**
  167. * Removes all relationships originating from a particular entity
  168. *
  169. * @param int $guid GUID of the subject or target entity (see $inverse)
  170. * @param string $relationship Type of the relationship (optional, default is all relationships)
  171. * @param bool $inverse_relationship Is $guid the target of the deleted relationships? By default, $guid is the
  172. * subject of the relationships.
  173. * @param string $type The type of entity related to $guid (defaults to all)
  174. *
  175. * @return true
  176. */
  177. function removeAll($guid, $relationship = "", $inverse_relationship = false, $type = '') {
  178. $guid = (int) $guid;
  179. if (!empty($relationship)) {
  180. $relationship = sanitize_string($relationship);
  181. $where = "AND er.relationship = '$relationship'";
  182. } else {
  183. $where = "";
  184. }
  185. if (!empty($type)) {
  186. $type = sanitize_string($type);
  187. if (!$inverse_relationship) {
  188. $join = "JOIN {$this->CONFIG->dbprefix}entities e ON e.guid = er.guid_two";
  189. } else {
  190. $join = "JOIN {$this->CONFIG->dbprefix}entities e ON e.guid = er.guid_one";
  191. $where .= " AND ";
  192. }
  193. $where .= " AND e.type = '$type'";
  194. } else {
  195. $join = "";
  196. }
  197. $guid_col = $inverse_relationship ? "guid_two" : "guid_one";
  198. _elgg_services()->db->deleteData("
  199. DELETE er FROM {$this->CONFIG->dbprefix}entity_relationships AS er
  200. $join
  201. WHERE $guid_col = $guid
  202. $where
  203. ");
  204. return true;
  205. }
  206. /**
  207. * Get all the relationships for a given GUID.
  208. *
  209. * @param int $guid GUID of the subject or target entity (see $inverse)
  210. * @param bool $inverse_relationship Is $guid the target of the deleted relationships? By default $guid is
  211. * the subject of the relationships.
  212. *
  213. * @return \ElggRelationship[]
  214. */
  215. function getAll($guid, $inverse_relationship = false) {
  216. $guid = (int)$guid;
  217. $where = ($inverse_relationship ? "guid_two='$guid'" : "guid_one='$guid'");
  218. $query = "SELECT * from {$this->CONFIG->dbprefix}entity_relationships where {$where}";
  219. return _elgg_services()->db->getData($query, "row_to_elggrelationship");
  220. }
  221. /**
  222. * Return entities matching a given query joining against a relationship.
  223. * Also accepts all options available to elgg_get_entities() and
  224. * elgg_get_entities_from_metadata().
  225. *
  226. * To ask for entities that do not have a particular relationship to an entity,
  227. * use a custom where clause like the following:
  228. *
  229. * $options['wheres'][] = "NOT EXISTS (
  230. * SELECT 1 FROM {$db_prefix}entity_relationships
  231. * WHERE guid_one = e.guid
  232. * AND relationship = '$relationship'
  233. * )";
  234. *
  235. * @see elgg_get_entities
  236. * @see elgg_get_entities_from_metadata
  237. *
  238. * @param array $options Array in format:
  239. *
  240. * relationship => null|STR Type of the relationship. E.g. "member"
  241. *
  242. * relationship_guid => null|INT GUID of the subject of the relationship, unless "inverse_relationship" is set
  243. * to true, in which case this will specify the target.
  244. *
  245. * inverse_relationship => false|BOOL Are we searching for relationship subjects? By default, the query finds
  246. * targets of relationships.
  247. *
  248. * relationship_join_on => null|STR How the entities relate: guid (default), container_guid, or owner_guid
  249. * Examples using the relationship 'friend':
  250. * 1. use 'guid' if you want the user's friends
  251. * 2. use 'owner_guid' if you want the entities the user's friends own
  252. * (including in groups)
  253. * 3. use 'container_guid' if you want the entities in the user's personal
  254. * space (non-group)
  255. *
  256. * relationship_created_time_lower => null|INT Relationship created time lower boundary in epoch time
  257. *
  258. * relationship_created_time_upper => null|INT Relationship created time upper boundary in epoch time
  259. *
  260. * @return \ElggEntity[]|mixed If count, int. If not count, array. false on errors.
  261. */
  262. function getEntities($options) {
  263. $defaults = array(
  264. 'relationship' => null,
  265. 'relationship_guid' => null,
  266. 'inverse_relationship' => false,
  267. 'relationship_join_on' => 'guid',
  268. 'relationship_created_time_lower' => ELGG_ENTITIES_ANY_VALUE,
  269. 'relationship_created_time_upper' => ELGG_ENTITIES_ANY_VALUE,
  270. );
  271. $options = array_merge($defaults, $options);
  272. $join_column = "e.{$options['relationship_join_on']}";
  273. $clauses = elgg_get_entity_relationship_where_sql($join_column, $options['relationship'],
  274. $options['relationship_guid'], $options['inverse_relationship']);
  275. if ($clauses) {
  276. // merge wheres to pass to get_entities()
  277. if (isset($options['wheres']) && !is_array($options['wheres'])) {
  278. $options['wheres'] = array($options['wheres']);
  279. } elseif (!isset($options['wheres'])) {
  280. $options['wheres'] = array();
  281. }
  282. $options['wheres'] = array_merge($options['wheres'], $clauses['wheres']);
  283. // limit based on time created
  284. $time_wheres = _elgg_get_entity_time_where_sql('r', $options['relationship_created_time_upper'],
  285. $options['relationship_created_time_lower']);
  286. if ($time_wheres) {
  287. $options['wheres'] = array_merge($options['wheres'], array($time_wheres));
  288. }
  289. // merge joins to pass to get_entities()
  290. if (isset($options['joins']) && !is_array($options['joins'])) {
  291. $options['joins'] = array($options['joins']);
  292. } elseif (!isset($options['joins'])) {
  293. $options['joins'] = array();
  294. }
  295. $options['joins'] = array_merge($options['joins'], $clauses['joins']);
  296. if (isset($options['selects']) && !is_array($options['selects'])) {
  297. $options['selects'] = array($options['selects']);
  298. } elseif (!isset($options['selects'])) {
  299. $options['selects'] = array();
  300. }
  301. $select = array('r.id');
  302. $options['selects'] = array_merge($options['selects'], $select);
  303. if (!isset($options['group_by'])) {
  304. $options['group_by'] = $clauses['group_by'];
  305. }
  306. }
  307. return elgg_get_entities_from_metadata($options);
  308. }
  309. /**
  310. * Returns SQL appropriate for relationship joins and wheres
  311. *
  312. * @todo add support for multiple relationships and guids.
  313. *
  314. * @param string $column Column name the GUID should be checked against.
  315. * Provide in table.column format.
  316. * @param string $relationship Type of the relationship
  317. * @param int $relationship_guid Entity GUID to check
  318. * @param bool $inverse_relationship Is $relationship_guid the target of the relationship?
  319. *
  320. * @return mixed
  321. * @access private
  322. */
  323. function getEntityRelationshipWhereSql($column, $relationship = null,
  324. $relationship_guid = null, $inverse_relationship = false) {
  325. if ($relationship == null && $relationship_guid == null) {
  326. return '';
  327. }
  328. $wheres = array();
  329. $joins = array();
  330. $group_by = '';
  331. if ($inverse_relationship) {
  332. $joins[] = "JOIN {$this->CONFIG->dbprefix}entity_relationships r on r.guid_one = $column";
  333. } else {
  334. $joins[] = "JOIN {$this->CONFIG->dbprefix}entity_relationships r on r.guid_two = $column";
  335. }
  336. if ($relationship) {
  337. $wheres[] = "r.relationship = '" . sanitise_string($relationship) . "'";
  338. }
  339. if ($relationship_guid) {
  340. if ($inverse_relationship) {
  341. $wheres[] = "r.guid_two = '$relationship_guid'";
  342. } else {
  343. $wheres[] = "r.guid_one = '$relationship_guid'";
  344. }
  345. } else {
  346. // See #5775. Queries that do not include a relationship_guid must be grouped by entity table alias,
  347. // otherwise the result set is not unique
  348. $group_by = $column;
  349. }
  350. if ($where_str = implode(' AND ', $wheres)) {
  351. return array('wheres' => array("($where_str)"), 'joins' => $joins, 'group_by' => $group_by);
  352. }
  353. return '';
  354. }
  355. /**
  356. * Gets the number of entities by a the number of entities related to them in a particular way.
  357. * This is a good way to get out the users with the most friends, or the groups with the
  358. * most members.
  359. *
  360. * @param array $options An options array compatible with elgg_get_entities_from_relationship()
  361. *
  362. * @return \ElggEntity[]|int|boolean If count, int. If not count, array. false on errors.
  363. */
  364. function getEntitiesFromCount(array $options = array()) {
  365. $options['selects'][] = "COUNT(e.guid) as total";
  366. $options['group_by'] = 'r.guid_two';
  367. $options['order_by'] = 'total desc';
  368. return elgg_get_entities_from_relationship($options);
  369. }
  370. }