tripal_views_query.inc 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431
  1. <?php
  2. class tripal_views_query extends views_plugin_query {
  3. /**
  4. * Ensure a table exists in the queue.
  5. *
  6. * This function overrides the views_plugin_query version of the function
  7. * but does nothing other than return the "table" (or bundle) name as
  8. * we won't be using aliases for bundles.
  9. *
  10. * @param $table
  11. * The unaliased name of the table to ensure.
  12. * @param $relationship
  13. * The relationship to ensure the table links to. Each relationship will
  14. * get a unique instance of the table being added. If not specified, will
  15. * be the primary table.
  16. * @param $join
  17. * A views_join object (or derived object) to join the alias in.
  18. *
  19. * @return
  20. * The alias used to refer to this specific table, or NULL if the table
  21. * cannot be ensured.
  22. */
  23. public function ensure_table($table, $relationship = NULL, $join = NULL) {
  24. // Because we are not querying a table, we're querying a TripalFieldQuery
  25. // object we don't need to ensure the table.
  26. return $table;
  27. }
  28. /**
  29. *
  30. */
  31. public function init($base_table = 'tripal_entity', $base_field = 'id', $options) {
  32. ;
  33. parent::init($base_table, $base_field, $options);
  34. $this->fields = [];
  35. $this->where = [];
  36. $this->order = [];
  37. // Creqte the TripalFieldQuery object.
  38. $this->query = new TripalFieldQuery();
  39. $this->cquery = new TripalFieldQuery();
  40. $this->cquery->count();
  41. // Convert the $base_table into the bundle table. Because every
  42. // tripal site will have different bundle tables we have to do the
  43. // conversion for cross-site compatibility.
  44. list($vocabulary, $accession) = explode('__', $base_table);
  45. $term = tripal_load_term_entity([
  46. 'vocabulary' => $vocabulary,
  47. 'accession' => $accession,
  48. ]);
  49. $bundle = tripal_load_bundle_entity(['term_id' => $term->id]);
  50. // Make sure we only query on the entities for this bundle type.
  51. $this->query->entityCondition('entity_type', 'TripalEntity');
  52. $this->query->entityCondition('bundle', $bundle->name);
  53. $this->cquery->entityCondition('entity_type', 'TripalEntity');
  54. $this->cquery->entityCondition('bundle', $bundle->name);
  55. }
  56. /**
  57. *
  58. */
  59. public function add_field($table_alias, $field_name, $alias = '', $params = []) {
  60. $this->fields[] = [
  61. 'table_alias' => $table_alias,
  62. 'field_name' => $field_name,
  63. 'alias' => $alias,
  64. 'params' => $params,
  65. ];
  66. }
  67. /**
  68. * Add a simple WHERE clause to the query.
  69. *
  70. * @param $group
  71. * The WHERE group to add these to; groups are used to create AND/OR
  72. * sections. Groups cannot be nested. Use 0 as the default group. If the
  73. * group does not yet exist it will be created as an AND group.
  74. * @param $field
  75. * The name of the field to check.
  76. * @param $value
  77. * The value to test the field against. In most cases, this is a scalar.
  78. * For more complex options, it is an array. The meaning of each element
  79. * in the array is dependent on the $operator.
  80. * @param $operator
  81. * The comparison operator, such as =, <, or >=. It also accepts more
  82. * complex options such as IN, LIKE, or BETWEEN. Defaults to IN if $value
  83. * is an array = otherwise. If $field is a string you have to use 'formula'
  84. * here.
  85. */
  86. public function add_where($group, $field_name, $value = NULL, $operator = NULL) {
  87. if ($value) {
  88. $this->filters[] = [
  89. 'group' => $group,
  90. 'field_name' => $field_name,
  91. 'value' => $value,
  92. 'op' => $operator,
  93. ];
  94. // Handle the bundle properties separate from real fields.
  95. if ($field_name == 'entity_id' or $field_name == 'status') {
  96. $this->query->propertyCondition($field_name, $value, $operator);
  97. $this->cquery->propertyCondition($field_name, $value, $operator);
  98. return;
  99. }
  100. // For fields compatible with the Tripal storage API, the
  101. // incoming $field_name is a combination of the entity term ID,
  102. // followed by the field name and the the sub element string, with
  103. // sub elements children separated by a period. For non Tripal
  104. // storage API the $field_name is a combination of the table name
  105. // followed by the table column. We have to handle both because
  106. // a TripalEntity can have both types attached.
  107. $elements = explode('.', $field_name);
  108. $field = NULL;
  109. if (count($elements) > 2) {
  110. $bundle_term = array_shift($elements);
  111. $field_name = array_shift($elements);
  112. $field = field_info_field($field_name);
  113. // put the sub elements back together into a string with a comma.
  114. $element_name = implode(',', $elements);
  115. }
  116. if (count($elements) == 2) {
  117. $field_name = array_shift($elements);
  118. $element_name = array_shift($elements);
  119. // At this point we're still not 100% sure if we have a
  120. // Tripal Storage API field or not. One quick way to
  121. // tell is to see if we get a field using the $field_name. if so the
  122. // field name comes in as the second element.
  123. $field = field_info_field($element_name);
  124. if ($field) {
  125. $field_name = $element_name;
  126. $element_name = '';
  127. }
  128. }
  129. if ($field) {
  130. $instance = field_info_instance('TripalEntity', $field_name, $this->query->entityConditions['bundle']['value']);
  131. // Construct the field term.
  132. $field_term = $instance['settings']['term_vocabulary'] . ':' . $instance['settings']['term_accession'];
  133. // Let's add on the $field_term to the element_name and add the
  134. // query condition.
  135. if ($element_name) {
  136. $element_name = $field_term . ',' . $element_name;
  137. }
  138. else {
  139. $element_name = $field_term;
  140. }
  141. $this->query->fieldCondition($field_name, $element_name, $value, $operator);
  142. $this->cquery->fieldCondition($field_name, $element_name, $value, $operator);
  143. }
  144. else {
  145. // If we have a table name then this table is in the Drupal schema and
  146. // we need to add a relationship with the tripal_entity table.
  147. $table = $field_name;
  148. $field = $element_name;
  149. $this->query->relationshipCondition($table, $field, $value, $operator);
  150. $this->cquery->relationshipCondition($table, $field, $value, $operator);
  151. }
  152. }
  153. }
  154. /**
  155. * Add's a where exression clause to a query.
  156. *
  157. * @param $group
  158. * The WHERE group to add these to; groups are used to create AND/OR
  159. * sections. Groups cannot be nested. Use 0 as the default group. If the
  160. * group does not yet exist it will be created as an AND group.
  161. * @param $snippet
  162. * The snippet to check. This can be either a column or a complex
  163. * expression like "UPPER(table.field) = 'value'".
  164. * @param $args
  165. * An associative array of arguments.
  166. */
  167. public function add_where_expression($group, $snippet, $args = []) {
  168. // Ensure all variants of 0 are actually 0. Thus '', 0 and NULL are all
  169. // the default group.
  170. if (empty($group)) {
  171. $group = 0;
  172. }
  173. // TODO: this function needs to be adjusted to pull out the element from
  174. // snippet (see the code for the add_where function above for an example.
  175. // for now this function will not properly work.
  176. // Check for a group.
  177. if (!isset($this->where[$group])) {
  178. $this->set_where_group('AND', $group);
  179. }
  180. $this->where[$group]['conditions'][] = [
  181. 'field' => $snippet,
  182. 'value' => $args,
  183. 'operator' => 'formula',
  184. ];
  185. }
  186. /**
  187. * Overrides add_orderby().
  188. */
  189. public function add_orderby($table, $field_name = NULL, $order = 'ASC', $alias = '', $params = []) {
  190. if ($field_name) {
  191. // If we already have an orderBy for this field then remove it so
  192. // we can reset it.
  193. foreach ($this->order as $index => $order_details) {
  194. if ($order_details['field'] == $field_name) {
  195. $this->order[$index]['direction'] = $order;
  196. unset($this->order[$index]);
  197. }
  198. }
  199. $this->order[] = [
  200. 'field' => $field_name,
  201. 'direction' => strtoupper($order),
  202. ];
  203. // If the field_name comes to us with a period in it then it means that
  204. // we need to separate the field name from sub-element names.
  205. $matches = [];
  206. if (preg_match('/^(.+?)\.(.*)$/', $field_name, $matches)) {
  207. $field_name = $matches[1];
  208. $element_name = $matches[2];
  209. $field = field_info_field($field_name);
  210. $instance = field_info_instance('TripalEntity', $field_name, $this->query->entityConditions['bundle']['value']);
  211. $element_name = $instance['settings']['term_vocabulary'] . ':' . $instance['settings']['term_accession'] . ',' . $element_name;
  212. $this->query->fieldOrderBy($field_name, $element_name, $order);
  213. }
  214. else {
  215. $instance = field_info_instance('TripalEntity', $field_name, $this->query->entityConditions['bundle']['value']);
  216. $field_term = $instance['settings']['term_vocabulary'] . ':' . $instance['settings']['term_accession'];
  217. $this->query->fieldOrderBy($field_name, $field_term, $order);
  218. }
  219. }
  220. }
  221. /**
  222. * Overrides build().
  223. */
  224. function build(&$view) {
  225. // Make the query distinct if the option was set.
  226. if (!empty($this->options['distinct'])) {
  227. $this->set_distinct(TRUE, !empty($this->options['pure_distinct']));
  228. }
  229. // Store the view in the object to be able to use it later.
  230. $this->view = $view;
  231. $view->init_pager();
  232. // Let the pager modify the query to add limits.
  233. $this->pager->query();
  234. $view->build_info['query'] = $this->query;
  235. $view->build_info['count_query'] = $this->cquery;
  236. }
  237. /**
  238. *
  239. * @param $view
  240. */
  241. function execute(&$view) {
  242. $query = $view->build_info['query'];
  243. $cquery = $view->build_info['count_query'];
  244. if ($query) {
  245. $start = microtime(TRUE);
  246. try {
  247. if ($this->pager->use_count_query() || !empty($view->get_total_rows)) {
  248. // TODO: The code below was taken from the
  249. // views_plugin_pager::execute_count_query($count_query) which would
  250. // be called here, but that function expects the query is a
  251. // database query rather than a TripalEntityField query. We
  252. // really should create a new tripal_views_plugin_pager class
  253. // and call the corresponding function here, but due to time
  254. // constraints this is the shortcut.
  255. $total_items = $this->cquery->execute();
  256. $this->pager->total_items = $total_items;
  257. if (!empty($this->pager->options['offset'])) {
  258. $this->pager->total_items -= $this->pager->options['offset'];
  259. };
  260. $this->pager->update_page_info();
  261. }
  262. // TODO: we need to implement a new views_plugin_pager class to
  263. // override the pre_execute to set the range, instead we'll just do
  264. // it manully here until we have the class.
  265. $this->pager->pre_execute($query);
  266. $num_items_per_page = $this->pager->get_items_per_page();
  267. $offset = $this->pager->get_current_page() * $num_items_per_page;
  268. // I'm not sure why an offset would come back as -1 but it has happened
  269. // with Drupal Views. This is a quick band-aid fix.
  270. $offset = ($offset < 0) ? 0 : $offset;
  271. $query->range($offset, $num_items_per_page);
  272. // Get the IDs
  273. $results = $query->execute();
  274. $entity_ids = (isset($results['TripalEntity']) AND is_array($results['TripalEntity'])) ? array_keys($results['TripalEntity']) : [];
  275. $this->pager->post_execute($view->result);
  276. if ($this->pager->use_count_query() || !empty($view->get_total_rows)) {
  277. $view->total_rows = $this->pager->get_total_items();
  278. }
  279. // Get the fields to attach to the entity
  280. $fields = [];
  281. $field_ids = [];
  282. foreach ($this->fields as $details) {
  283. $field_name = $details['field_name'];
  284. // If the field_name comes to us with a period in it then it means that
  285. // we need to separate the field name from sub-element names.
  286. $matches = [];
  287. if (preg_match('/^(.+?)\.(.*)$/', $field_name, $matches)) {
  288. $field_name = $matches[1];
  289. $element_name = $matches[2];
  290. }
  291. $field = field_info_field($field_name);
  292. if ($field) {
  293. $fields[$field_name] = $field;
  294. $field_ids[] = $field['id'];
  295. }
  296. }
  297. // Get the entity IDs from the query.
  298. $entities = tripal_load_entity('TripalEntity', $entity_ids, FALSE, $field_ids);
  299. $i = 0;
  300. foreach ($entities as $entity_id => $entity) {
  301. $view->result[$i] = new stdClass();
  302. foreach ($this->fields as $details) {
  303. $field_name = $details['field_name'];
  304. // The entity_id and link fields are not true fields. They are
  305. // added by the tripal_views_data_tripal_entity() function to provide
  306. // useful fields to reference entities. If we see these
  307. // we need to support them here by giving them values.
  308. if ($field_name == 'entity_id') {
  309. $view->result[$i]->$field_name = $entity;
  310. continue;
  311. }
  312. if ($field_name == 'link') {
  313. $view->result[$i]->$field_name = $entity;
  314. continue;
  315. }
  316. if ($field_name == 'edit_link') {
  317. $view->result[$i]->$field_name = $entity;
  318. continue;
  319. }
  320. if ($field_name == 'delete_link') {
  321. $view->result[$i]->$field_name = $entity;
  322. continue;
  323. }
  324. if ($field_name == 'status') {
  325. $view->result[$i]->$field_name = $entity->status;
  326. continue;
  327. }
  328. // If the field_name comes to us with a period in it then it means that
  329. // we need to separate the field name from sub-element names.
  330. $matches = [];
  331. if (preg_match('/^(.+?)\.(.*)$/', $field_name, $matches)) {
  332. $field_name = $matches[1];
  333. $element_name = $matches[2];
  334. }
  335. if (array_key_exists($field_name, $fields)) {
  336. $items = field_get_items('TripalEntity', $entity, $field_name);
  337. $view->result[$i]->$field_name = $items;
  338. }
  339. }
  340. // Always add the entity to the results so that handlers
  341. // can take advantage of it.
  342. $view->result[$i]->entity = $entity;
  343. $i++;
  344. }
  345. } catch (Exception $e) {
  346. $view->result = [];
  347. if (!empty($view->live_preview)) {
  348. drupal_set_message($e->getMessage(), 'error');
  349. }
  350. else {
  351. vpr('Exception in @human_name[@view_name]: @message', [
  352. '@human_name' => $view->human_name,
  353. '@view_name' => $view->name,
  354. '@message' => $e->getMessage(),
  355. ]);
  356. }
  357. }
  358. }
  359. else {
  360. $start = microtime(TRUE);
  361. }
  362. $view->execute_time = microtime(TRUE) - $start;
  363. }
  364. /**
  365. * Provides a unique placeholders for handlers.
  366. */
  367. public function placeholder() {
  368. // TODO: this function doesn't currently work. It is used by the
  369. // words, all words, shorter than, longer than filters, but those
  370. // are currently commented out.
  371. //return $this->query->placeholder($this->options['table'] . '_' . $this->options['field']);
  372. }
  373. /**
  374. * This function copied from views_plugin_query_default::add_relationship
  375. */
  376. public function add_relationship($alias, $join, $base, $link_point = NULL) {
  377. // Make sure $alias isn't already used; if it, start adding stuff.
  378. $alias_base = $alias;
  379. $count = 1;
  380. while (!empty($this->relationships[$alias])) {
  381. $alias = $alias_base . '_' . $count++;
  382. }
  383. $this->query->addRelationship($join->table, $alias, $join->field);
  384. $this->cquery->addRelationship($join->table, $alias, $join->field);
  385. return $alias;
  386. }
  387. }