views_handler_join_chado_aggregator.inc 12 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339
  1. <?php
  2. module_load_include('inc', 'views', 'includes/base');
  3. module_load_include('inc', 'views', 'includes/handlers');
  4. /**
  5. * @file
  6. * Handler to allow joins between records via a linking table
  7. *
  8. * Example Usage:
  9. * To join the analysisprop table to the analysis table,
  10. * Use the following code in the analysisprop hook_views_data:
  11. * @code
  12. $data['analysisprop']['table']['join']['analysis'] = array(
  13. 'left_field' => 'analysis_id',
  14. 'field' => 'analysis_id',
  15. 'handler' => 'views_handler_join_chado_aggregator',
  16. 'pre-aggregated' => TRUE | FALSE //whether the table is already aggregated (contains arrays)
  17. 'table_aggregated' => CURRENT | LEFT //the table which has many records for each record in the other
  18. );
  19. * @endcode
  20. */
  21. class views_handler_join_chado_aggregator extends views_join {
  22. // PHP 4 doesn't call constructors of the base class automatically from a
  23. // constructor of a derived class. It is your responsibility to propagate
  24. // the call to constructors upstream where appropriate.
  25. function construct($table = NULL, $left_table = NULL, $left_field = NULL, $field = NULL, $extra = array(), $type = 'LEFT', $added = NULL) {
  26. parent::construct($table, $left_table, $left_field, $field, $extra, $type);
  27. // Determine the postgresql version
  28. $postgresql_version = pg_version();
  29. $this->postgresql_version = $postgresql_version['client'];
  30. // If version is 9.0+ then indicate
  31. // Needed to apply sorting for aggregated fields
  32. if (intval($postgresql_version['client']) >= 9) {
  33. $this->postgresql_9up = TRUE;
  34. }
  35. }
  36. /**
  37. * Creates SQL including aggregation query used in join
  38. */
  39. function join($table, &$query) {
  40. $opt = array(
  41. 'table' => $this->definition['table'],
  42. 'field' => $this->definition['field'],
  43. 'left_table' => $this->definition['left_table'],
  44. 'left_field' => $this->definition['left_field'],
  45. 'table_aggregated' => $this->definition['table_aggregated'],
  46. 'sort' => $this->sort,
  47. 'filter' => $this->filter,
  48. 'additional_joins' => $this->additional_joins,
  49. 'postgresql_9up' => $this->postgresql_9up,
  50. );
  51. $output = $this->aggregate_join($query, $opt);
  52. return implode("\n", $output);
  53. }
  54. function aggregate_join(&$query, $opt) {
  55. // Create the table SQL (used in join) -------
  56. // query creating one-to-one table using array_agg
  57. // Only aggregate each field if it the join table hadn't been pre-aggregated
  58. // Example where it might be pre-aggregated: Materialized view
  59. if (!$this->definition['pre-aggregated']) {
  60. $sql = $this->get_aggregate_sql_for_table_field($opt);
  61. // Create the join (full SQL) ----------------
  62. $output[] = $this->create_single_join(
  63. $query,
  64. array(
  65. 'table' => $opt['table'],
  66. 'field' => $opt['field'],
  67. 'table_sql' => $sql,
  68. 'is_drupal' => FALSE,
  69. ),
  70. array(
  71. 'table' => $opt['left_table'],
  72. 'field' => $opt['left_field'],
  73. ),
  74. 'LEFT'
  75. );
  76. // Otherwise the table has been pre-aggregated
  77. // Then only need to do a regular join with any in where
  78. }
  79. else {
  80. // Create the join
  81. $current_table_spec = array(
  82. 'table' => $opt['table'],
  83. 'field' => $opt['field'],
  84. 'is_drupal' => FALSE,
  85. );
  86. $left_table_spec = array(
  87. 'table' => $opt['left_table'],
  88. 'field' => $opt['left_field'],
  89. );
  90. switch ($opt['table_aggregated']) {
  91. default:
  92. case 'CURRENT':
  93. $current_table_spec['pre-aggregated'] = TRUE;
  94. break;
  95. case 'LEFT':
  96. $left_table_spec['pre-aggregated'] = TRUE;
  97. break;
  98. }
  99. $output[] = $this->create_single_join(
  100. $query,
  101. $current_table_spec,
  102. $left_table_spec,
  103. 'LEFT'
  104. );
  105. }
  106. return $output;
  107. }
  108. /**
  109. * Create the SQL needed to aggregate a table
  110. */
  111. function get_aggregate_sql_for_table_field($opt) {
  112. // Determine Order BY's for aggregates
  113. $order_by = array();
  114. if (!is_array($opt['sort'])) {
  115. $opt['sort'] = array();
  116. }
  117. foreach ($opt['sort'] as $s) {
  118. $order_by[] = $s['table'] . '.' . $s['field'] . ' ' . $s['order'];
  119. }
  120. // get table description (if defined via schema api)
  121. $table_desc = tripal_core_get_chado_table_schema($opt['table']);
  122. $select_fields[ $opt['table'] ] = $table_desc['fields'];
  123. $joins = array();
  124. if (!empty($table_desc)) {
  125. // Add joins to tables with a foreign key in this table
  126. // (ie: add join to cvterm if this table has a type_id
  127. foreach ($table_desc['foreign keys'] as $defn) {
  128. if ($defn['table'] != $opt['left_table']) {
  129. foreach ( $defn['columns'] as $left => $right) {
  130. $table_alias = 'fk_' . $defn['table'] . '_' . $left;
  131. $left = $opt['table'] . '.' . $left;
  132. $right = $table_alias . '.' . $right;
  133. $joins[] = "LEFT JOIN $defn[table] $table_alias ON $left=$right";
  134. // Fields to be selected from joined table
  135. $join_table = tripal_core_get_chado_table_schema($defn['table']);
  136. $select_fields[ $table_alias ] = $join_table['fields'];
  137. }
  138. }
  139. }
  140. // Fields to be selected
  141. foreach ($select_fields as $table => $table_fields) {
  142. foreach ($table_fields as $fname => $f) {
  143. $alias = '';
  144. if ($table != $opt['table']) {
  145. $alias = $table . '_';
  146. }
  147. if ($fname != $opt['field']) {
  148. // Add sort to aggregate field if postgreSQL 9.0+
  149. if ($opt['postgresql_9up'] && !empty($order_by)) {
  150. $fields[] = 'array_agg(' . $table . '.' . $fname . ' ORDER BY ' . implode(',', $order_by) . ') as ' . $alias . $fname;
  151. }
  152. else {
  153. $fields[] = 'array_agg(' . $table . '.' . $fname . ') as '. $alias . $fname;
  154. }
  155. $composite_field_parts[] = "'" . $alias . $fname . "::' || COALESCE(CAST(" . $table . '.' . $fname . " as text), '')";
  156. }
  157. else {
  158. $fields[] = $fname;
  159. $composite_field_parts[] = "'" . $alias . $fname . "::' || COALESCE(CAST(" . $table . '.' . $fname . " as text), '')";
  160. }
  161. }
  162. }
  163. // There is no definition in schema api
  164. // then use postgresql select
  165. }
  166. else {
  167. // No known foreign key relationships
  168. // so don't add to $joins
  169. // Fields to be selected
  170. $sql = "SELECT
  171. attname as column,
  172. format_type(atttypid, atttypmod) as datatype
  173. FROM pg_attribute, pg_type
  174. WHERE typname='nd_genotype_experiment'
  175. AND attrelid=typrelid
  176. AND attname NOT IN ('cmin','cmax','ctid','oid','tableoid','xmin','xmax')";
  177. $resource = chado_query($sql);
  178. while ($r = db_fetch_object($resource)) {
  179. $table = $opt['table'];
  180. $alias = ''; //no alias needed if table is current table (only option if no schema api definition)
  181. $fname = $r->column;
  182. if ($fname != $opt['field']) {
  183. // Add sort to aggregate field if postgreSQL 9.0+
  184. if ($opt['postgresql_9up'] && !empty($order_by)) {
  185. $fields[] = 'array_agg(' . $table . '.' . $fname . ' ORDER BY ' . implode(',', $order_by) . ') as ' . $alias . $fname;
  186. }
  187. else {
  188. $fields[] = 'array_agg(' . $table . '.' . $fname . ') as ' . $alias . $fname;
  189. }
  190. $composite_field_parts[] = "'" . $alias . $fname . "::' || COALESCE(CAST(" . $table . '.' . $fname . " as text), '')";
  191. }
  192. else {
  193. $fields[] = $fname;
  194. $composite_field_parts[] = "'" . $alias . $fname . "::' || COALESCE(CAST(" . $table . '.' . $fname . " as text), '')";
  195. }
  196. }
  197. }
  198. // Add in any additional joins specified by handlers
  199. if (!empty($opt['additional_joins'])) {
  200. foreach ($opt['additional_joins'] as $join_defn) {
  201. // Add the join SQL in
  202. $table = ($join_defn['table_alias']) ? $join_defn['table_alias'] : 'adt_' . $join_defn['table'] . '_' . $join_defn['left_field'];
  203. $left = $join_defn['left_table'] . '.' . $join_defn['left_field'];
  204. $right = $table . '.' . $join_defn['field'];
  205. $joins[] = "LEFT JOIN $join_defn[table] $table ON $left=$right";
  206. // Add to the fields and composite field
  207. $join_table_desc = tripal_core_get_chado_table_schema($join_defn['table']);
  208. $alias = $table . '_';
  209. foreach ($join_table_desc['fields'] as $fname => $fdesc) {
  210. // Add sort to aggregate field if postgreSQL 9.0+
  211. if ($opt['postgresql_9up'] && !empty($order_by)) {
  212. $fields[] = 'array_agg(' . $table . '.' . $fname . ' ORDER BY ' . implode(',', $order_by) . ') as ' . $alias . $fname;
  213. }
  214. else {
  215. $fields[] = 'array_agg(' . $table . '.' . $fname . ') as ' . $alias . $fname;
  216. }
  217. $composite_field_parts[] = "'" . $alias . $fname . "::' || COALESCE(CAST(" . $table . '.' . $fname . " as text), '')";
  218. }
  219. }
  220. }
  221. // composite field
  222. // (combines all other fields before aggregating)
  223. // Add sort to aggregate field if postgreSQL 9.0+
  224. if ($opt['postgresql_9up'] && !empty($order_by)) {
  225. $composite_field = "array_agg('{'||" . implode(" || ',' || ", $composite_field_parts) . "||'}' ORDER BY " . implode(',', $order_by) . ") as all";
  226. }
  227. else {
  228. $composite_field = "array_agg('{'||" . implode(" || ',' || ", $composite_field_parts) . "||'}') as all";
  229. }
  230. $fields[] = $composite_field;
  231. // SQL to use in the join
  232. $sql = 'SELECT ' . implode(', ', $fields)
  233. .' FROM ' . $opt['table']
  234. .' ' . implode(' ', $joins);
  235. if (!empty($opt['filter'])) {
  236. $sql .= ' WHERE ' . implode(' AND ', $opt['filter']);
  237. }
  238. $sql .= ' GROUP BY ' . $opt['field'];
  239. return $sql;
  240. }
  241. /**
  242. * Creates SQL for a single join based on parameters
  243. * Join will be: <type> JOIN (<query creating one-to-one table using array_agg>) <table alias>
  244. * ON <qualified left field>=<qualified right field>
  245. */
  246. function create_single_join(&$query, $right_spec, $left_spec, $join_type) {
  247. if ($right_spec['table']) {
  248. $right = $query->get_table_info($right_spec['table']);
  249. if (!$right['alias']) {
  250. $right['alias'] = $right_spec['table'];
  251. }
  252. $right_field = "$right[alias].$right_spec[field]";
  253. // Add any() around field if already aggregated
  254. if ($right_spec['pre-aggregated']) {
  255. $right_field = "any(" . $right_field . ")";
  256. }
  257. // Add drupal { } around table
  258. if ($right_spec['is_drupal']) {
  259. $right_table = '{' . $right_spec['table'] . '}';
  260. }
  261. else {
  262. $right_table = $right_spec['table'];
  263. }
  264. }
  265. if ($left_spec['table']) {
  266. $left = $query->get_table_info($left_spec['table']);
  267. if (!$left['alias']) {
  268. $left['alias'] = $left_spec['table'];
  269. }
  270. $left_field = "$left[alias].$left_spec[field]";
  271. }
  272. else {
  273. // This can be used if left_field is a formula or something. It should be used only *very* rarely.
  274. $left_field = $this->left_spec['field'];
  275. }
  276. // Add any() around field if already aggregated
  277. if ($left_spec['pre-aggregated']) {
  278. $left_field = "any(" . $left_field . ")";
  279. }
  280. // Concatenate parts together to form join sql
  281. if (!empty($right_spec['table_sql'])) {
  282. $output = " $join_type JOIN ($right_spec[table_sql]) $right[alias] ON $left_field = $right_field";
  283. }
  284. else {
  285. $output = " $join_type JOIN $right_spec[table] $right[alias] ON $left_field = $right_field";
  286. }
  287. return $output;
  288. }
  289. }