Create the SQL needed to aggregate a table

File

tripal_views/views/handlers/views_handler_join_chado_aggregator.inc, line 132

Handler to allow joins between records via a linking table

Class

views_handler_join_chado_aggregator

@file Handler to allow joins between records via a linking table

Code

function get_aggregate_sql_for_table_field($opt) {

  // Determine Order BY's for aggregates
  $order_by = array();
  if (!is_array($opt['sort'])) {
    $opt['sort'] = array();
  }
  foreach ($opt['sort'] as $s) {
    $order_by[] = $s['table'] . '.' . $s['field'] . ' ' . $s['order'];
  }

  // get table description (if defined via schema api)
  $table_desc = tripal_core_get_chado_table_schema($opt['table']);
  $select_fields[$opt['table']] = $table_desc['fields'];


  $joins = array();
  if (!empty($table_desc)) {
    // Add joins to tables with a foreign key in this table
    // (ie: add join to cvterm if this table has a type_id
    foreach ($table_desc['foreign keys'] as $defn) {
      if ($defn['table'] != $opt['left_table']) {
        foreach ($defn['columns'] as $left => $right) {
          $table_alias = 'fk_' . $defn['table'] . '_' . $left;
          $left = $opt['table'] . '.' . $left;
          $right = $table_alias . '.' . $right;
          $joins[] = "LEFT JOIN $defn[table] $table_alias ON $left=$right";

          // Fields to be selected from joined table
          $join_table = tripal_core_get_chado_table_schema($defn['table']);
          $select_fields[$table_alias] = $join_table['fields'];
        }
      }
    }

    // Fields to be selected
    foreach ($select_fields as $table => $table_fields) {
      foreach ($table_fields as $fname => $f) {
        $alias = '';
        if ($table != $opt['table']) {
          $alias = $table . '_';
        }

        if ($fname != $opt['field']) {
          // Add sort to aggregate field if postgreSQL 9.0+
          if ($opt['postgresql_9up'] && !empty($order_by)) {
            $fields[] = 'array_agg(' . $table . '.' . $fname . ' ORDER BY ' . implode(',', $order_by) . ') as ' . $alias . $fname;
          }
          else {
            $fields[] = 'array_agg(' . $table . '.' . $fname . ') as ' . $alias . $fname;
          }
          $composite_field_parts[] = "'" . $alias . $fname . "::' || COALESCE(CAST(" . $table . '.' . $fname . " as text), '')";
        }
        else {
          $fields[] = $fname;
          $composite_field_parts[] = "'" . $alias . $fname . "::' || COALESCE(CAST(" . $table . '.' . $fname . " as text), '')";
        }
      }
    }

    // There is no definition in schema api
    // then use postgresql select
  }
  else {

    // No known foreign key relationships
    // so don't add to $joins

    // Fields to be selected
    $sql = "SELECT
                attname as column,
                format_type(atttypid, atttypmod) as datatype
              FROM pg_attribute, pg_type
              WHERE typname='nd_genotype_experiment'
                AND attrelid=typrelid
                AND attname NOT IN ('cmin','cmax','ctid','oid','tableoid','xmin','xmax')";
    $resource = chado_query($sql);
    while ($r = db_fetch_object($resource)) {
      $table = $opt['table'];
      $alias = ''; //no alias needed if table is current table (only option if no schema api definition)
      $fname = $r->column;

      if ($fname != $opt['field']) {
        // Add sort to aggregate field if postgreSQL 9.0+
        if ($opt['postgresql_9up'] && !empty($order_by)) {
          $fields[] = 'array_agg(' . $table . '.' . $fname . ' ORDER BY ' . implode(',', $order_by) . ') as ' . $alias . $fname;
        }
        else {
          $fields[] = 'array_agg(' . $table . '.' . $fname . ') as ' . $alias . $fname;
        }
        $composite_field_parts[] = "'" . $alias . $fname . "::' || COALESCE(CAST(" . $table . '.' . $fname . " as text), '')";
      }
      else {
        $fields[] = $fname;
        $composite_field_parts[] = "'" . $alias . $fname . "::' || COALESCE(CAST(" . $table . '.' . $fname . " as text), '')";
      }
    }

  }

  // Add in any additional joins specified by handlers
  if (!empty($opt['additional_joins'])) {
    foreach ($opt['additional_joins'] as $join_defn) {

      // Add the join SQL in
      $table = ($join_defn['table_alias']) ? $join_defn['table_alias'] : 'adt_' . $join_defn['table'] . '_' . $join_defn['left_field'];
      $left = $join_defn['left_table'] . '.' . $join_defn['left_field'];
      $right = $table . '.' . $join_defn['field'];
      $joins[] = "LEFT JOIN $join_defn[table] $table ON $left=$right";

      // Add to the fields and composite field
      $join_table_desc = tripal_core_get_chado_table_schema($join_defn['table']);
      $alias = $table . '_';
      foreach ($join_table_desc['fields'] as $fname => $fdesc) {
        // Add sort to aggregate field if postgreSQL 9.0+
        if ($opt['postgresql_9up'] && !empty($order_by)) {
          $fields[] = 'array_agg(' . $table . '.' . $fname . ' ORDER BY ' . implode(',', $order_by) . ') as ' . $alias . $fname;
        }
        else {
          $fields[] = 'array_agg(' . $table . '.' . $fname . ') as ' . $alias . $fname;
        }
        $composite_field_parts[] = "'" . $alias . $fname . "::' || COALESCE(CAST(" . $table . '.' . $fname . " as text), '')";
      }
    }
  }

  // composite field
  // (combines all other fields before aggregating)
  // Add sort to aggregate field if postgreSQL 9.0+
  if ($opt['postgresql_9up'] && !empty($order_by)) {
    $composite_field = "array_agg('{'||" . implode(" || ',' || ", $composite_field_parts) . "||'}' ORDER BY " . implode(',', $order_by) . ") as all";
  }
  else {
    $composite_field = "array_agg('{'||" . implode(" || ',' || ", $composite_field_parts) . "||'}') as all";
  }
  $fields[] = $composite_field;

  // SQL to use in the join
  $sql = 'SELECT ' . implode(', ', $fields)
    . ' FROM ' . $opt['table']
    . ' ' . implode(' ', $joins);

  if (!empty($opt['filter'])) {
    $sql .= ' WHERE ' . implode(' AND ', $opt['filter']);
  }

  $sql .= ' GROUP BY ' . $opt['field'];

  return $sql;
}