Tripal v1.0 (6.x-1.0)
|
00001 <?php 00002 00003 module_load_include('inc', 'views', 'includes/base'); 00004 module_load_include('inc', 'views', 'includes/handlers'); 00005 00023 class views_handler_join_chado_aggregator extends views_join { 00024 00025 // PHP 4 doesn't call constructors of the base class automatically from a 00026 // constructor of a derived class. It is your responsibility to propagate 00027 // the call to constructors upstream where appropriate. 00028 function construct($table = NULL, $left_table = NULL, $left_field = NULL, $field = NULL, $extra = array(), $type = 'LEFT', $added = NULL) { 00029 parent::construct($table, $left_table, $left_field, $field, $extra, $type); 00030 00031 // Determine the postgresql version 00032 $postgresql_version = pg_version(); 00033 $this->postgresql_version = $postgresql_version['client']; 00034 00035 // If version is 9.0+ then indicate 00036 // Needed to apply sorting for aggregated fields 00037 if (intval($postgresql_version['client']) >= 9) { 00038 $this->postgresql_9up = TRUE; 00039 } 00040 00041 } 00042 00046 function join($table, &$query) { 00047 00048 $opt = array( 00049 'table' => $this->definition['table'], 00050 'field' => $this->definition['field'], 00051 'left_table' => $this->definition['left_table'], 00052 'left_field' => $this->definition['left_field'], 00053 'table_aggregated' => $this->definition['table_aggregated'], 00054 'sort' => $this->sort, 00055 'filter' => $this->filter, 00056 'postgresql_9up' => $this->postgresql_9up, 00057 ); 00058 00059 $output = $this->aggregate_join($query, $opt); 00060 return implode("\n", $output); 00061 00062 } 00063 00064 function aggregate_join(&$query, $opt) { 00065 // Create the table SQL (used in join) ------- 00066 // query creating one-to-one table using array_agg 00067 00068 // Only aggregate each field if it the join table hadn't been pre-aggregated 00069 // Example where it might be pre-aggregated: Materialized view 00070 if (!$this->definition['pre-aggregated']) { 00071 00072 $sql = $this->get_aggregate_sql_for_table_field($opt); 00073 00074 // Create the join (full SQL) ---------------- 00075 $output[] = $this->create_single_join( 00076 $query, 00077 array( 00078 'table' => $opt['table'], 00079 'field' => $opt['field'], 00080 'table_sql' => $sql, 00081 'is_drupal' => FALSE, 00082 ), 00083 array( 00084 'table' => $opt['left_table'], 00085 'field' => $opt['left_field'], 00086 ), 00087 'LEFT' 00088 ); 00089 00090 // Otherwise the table has been pre-aggregated 00091 // Then only need to do a regular join with any in where 00092 } 00093 else { 00094 00095 // Create the join 00096 00097 $current_table_spec = array( 00098 'table' => $opt['table'], 00099 'field' => $opt['field'], 00100 'is_drupal' => FALSE, 00101 ); 00102 $left_table_spec = array( 00103 'table' => $opt['left_table'], 00104 'field' => $opt['left_field'], 00105 ); 00106 00107 switch ($opt['table_aggregated']) { 00108 default: 00109 case 'CURRENT': 00110 $current_table_spec['pre-aggregated'] = TRUE; 00111 break; 00112 case 'LEFT': 00113 $left_table_spec['pre-aggregated'] = TRUE; 00114 break; 00115 } 00116 00117 $output[] = $this->create_single_join( 00118 $query, 00119 $current_table_spec, 00120 $left_table_spec, 00121 'LEFT' 00122 ); 00123 } 00124 00125 return $output; 00126 } 00127 00131 function get_aggregate_sql_for_table_field($opt) { 00132 00133 // Determine Order BY's for aggregates 00134 $order_by = array(); 00135 if (!is_array($opt['sort'])) { 00136 $opt['sort'] = array(); 00137 } 00138 foreach ($opt['sort'] as $s) { 00139 $order_by[] = $s['table'] . '.' . $s['field'] . ' ' . $s['order']; 00140 } 00141 00142 // get table description (if defined via schema api) 00143 $table_desc = tripal_core_get_chado_table_schema($opt['table']); 00144 $select_fields[ $opt['table'] ] = $table_desc['fields']; 00145 00146 if (!empty($table_desc)) { 00147 // Add joins to tables with a foreign key in this table 00148 // (ie: add join to cvterm if this table has a type_id 00149 $joins = array(); 00150 foreach ($table_desc['foreign keys'] as $defn) { 00151 if ($defn['table'] != $opt['left_table']) { 00152 foreach ( $defn['columns'] as $left => $right) { 00153 $left = $opt['table'] . '.' . $left; 00154 $right = $defn['table'] . '.' . $right; 00155 $joins[] = "LEFT JOIN $defn[table] $defn[table] ON $left=$right"; 00156 } 00157 00158 // Fields to be selected from joined table 00159 $join_table = tripal_core_get_chado_table_schema($defn['table']); 00160 $select_fields[ $defn['table'] ] = $join_table['fields']; 00161 } 00162 } 00163 00164 // Fields to be selected 00165 foreach ($select_fields as $table => $table_fields) { 00166 foreach ($table_fields as $fname => $f) { 00167 $alias = ''; 00168 if ($table != $opt['table']) { 00169 $alias = $table . '_'; 00170 } 00171 00172 if ($fname != $opt['field']) { 00173 // Add sort to aggregate field if postgreSQL 9.0+ 00174 if ($opt['postgresql_9up'] && !empty($order_by)) { 00175 $fields[] = 'array_agg(' . $table . '.' . $fname . ' ORDER BY ' . implode(',', $order_by) . ') as ' . $alias . $fname; 00176 } 00177 else { 00178 $fields[] = 'array_agg(' . $table . '.' . $fname . ') as '. $alias . $fname; 00179 } 00180 $composite_field_parts[] = "'" . $alias . $fname . "::' ||" . $table . '.' . $fname; 00181 } 00182 else { 00183 $fields[] = $fname; 00184 $composite_field_parts[] = "'" . $alias . $fname . "::' ||" . $table . '.' . $fname; 00185 } 00186 } 00187 } 00188 00189 // There is no definition in schema api 00190 // then use postgresql select 00191 } 00192 else { 00193 00194 // No known foreign key reelationships 00195 $joins = array(); 00196 00197 // Fields to be selected 00198 $sql = "SELECT 00199 attname as column, 00200 format_type(atttypid, atttypmod) as datatype 00201 FROM pg_attribute, pg_type 00202 WHERE typname='nd_genotype_experiment' 00203 AND attrelid=typrelid 00204 AND attname NOT IN ('cmin','cmax','ctid','oid','tableoid','xmin','xmax')"; 00205 $resource = chado_query($sql); 00206 while ($r = db_fetch_object($resource)) { 00207 $table = $opt['table']; 00208 $alias = ''; //no alias needed if table is current table (only option if no schema api definition) 00209 $fname = $r->column; 00210 00211 if ($fname != $opt['field']) { 00212 // Add sort to aggregate field if postgreSQL 9.0+ 00213 if ($opt['postgresql_9up'] && !empty($order_by)) { 00214 $fields[] = 'array_agg(' . $table . '.' . $fname . ' ORDER BY ' . implode(',', $order_by) . ') as ' . $alias . $fname; 00215 } 00216 else { 00217 $fields[] = 'array_agg(' . $table . '.' . $fname . ') as ' . $alias . $fname; 00218 } 00219 $composite_field_parts[] = "'" . $alias . $fname . "::' ||" . $table . '.' . $fname; 00220 } 00221 else { 00222 $fields[] = $fname; 00223 $composite_field_parts[] = "'" . $alias . $fname . "::' ||" . $table . '.' . $fname; 00224 } 00225 } 00226 00227 } 00228 00229 // composite field 00230 // (combines all other fields before aggregating) 00231 // Add sort to aggregate field if postgreSQL 9.0+ 00232 if ($opt['postgresql_9up'] && !empty($order_by)) { 00233 $composite_field = "array_agg('{'||" . implode(" || ',' || ", $composite_field_parts) . "||'}' ORDER BY " . implode(',', $order_by) . ") as all"; 00234 } 00235 else { 00236 $composite_field = "array_agg('{'||" . implode(" || ',' || ", $composite_field_parts) . "||'}') as all"; 00237 } 00238 $fields[] = $composite_field; 00239 00240 // SQL to use in the join 00241 $sql = 'SELECT ' . implode(', ', $fields) 00242 .' FROM ' . $opt['table'] 00243 .' ' . implode(' ', $joins); 00244 00245 if (!empty($opt['filter'])) { 00246 $sql .= ' WHERE ' . implode(' AND ', $opt['filter']); 00247 } 00248 00249 $sql .= ' GROUP BY ' . $opt['field']; 00250 00251 return $sql; 00252 } 00253 00259 function create_single_join(&$query, $right_spec, $left_spec, $join_type) { 00260 00261 if ($right_spec['table']) { 00262 $right = $query->get_table_info($right_spec['table']); 00263 if (!$right['alias']) { 00264 $right['alias'] = $right_spec['table']; 00265 } 00266 $right_field = "$right[alias].$right_spec[field]"; 00267 00268 // Add any() around field if already aggregated 00269 if ($right_spec['pre-aggregated']) { 00270 $right_field = "any(" . $right_field . ")"; 00271 } 00272 00273 // Add drupal { } around table 00274 if ($right_spec['is_drupal']) { 00275 $right_table = '{' . $right_spec['table'] . '}'; 00276 } 00277 else { 00278 $right_table = $right_spec['table']; 00279 } 00280 } 00281 00282 if ($left_spec['table']) { 00283 $left = $query->get_table_info($left_spec['table']); 00284 if (!$left['alias']) { 00285 $left['alias'] = $left_spec['table']; 00286 } 00287 $left_field = "$left[alias].$left_spec[field]"; 00288 } 00289 else { 00290 // This can be used if left_field is a formula or something. It should be used only *very* rarely. 00291 $left_field = $this->left_spec['field']; 00292 } 00293 00294 // Add any() around field if already aggregated 00295 if ($left_spec['pre-aggregated']) { 00296 $left_field = "any(" . $left_field . ")"; 00297 } 00298 00299 // Concatenate parts together to form join sql 00300 if (!empty($right_spec['table_sql'])) { 00301 $output = " $join_type JOIN ($right_spec[table_sql]) $right[alias] ON $left_field = $right_field"; 00302 } 00303 else { 00304 $output = " $join_type JOIN $right_spec[table] $right[alias] ON $left_field = $right_field"; 00305 } 00306 00307 return $output; 00308 } 00309 00310 }