Provides a generic routine for selecting data from a Chado table.

Use this function to perform a simple select from any Chado table.

  $columns = array('feature_id', 'name');
  $values =  array(
    'organism_id' => array(
        'genus' => 'Citrus',
        'species' => array('sinensis', 'clementina'),
     ),
    'uniquename' => 'orange1.1g000034m.g',
    'type_id' => array (
        'cv_id' => array (
           'name' => 'sequence',
        ),
        'name' => 'gene',
        'is_obsolete' => 0
     ),
  );
  $options = array(
    'order_by' => array(
       'name' => 'ASC'
    ),
  );
  $result = chado_select_record('feature',$columns,$values,$options);

The above code selects a record from the feature table using the three fields that uniquely identify a feature. The $columns array simply lists the columns to select. The $values array is nested such that the organism is identified by way of the organism_id foreign key constraint by specifying the genus and species. The cvterm is also specified using its foreign key and the cv_id for the cvterm is nested as well. In the example above, two different species are allowed to match

Complex Filtering: All of the documentation above supports filtering based on 'is equal to' or 'is NULL'. If your criteria doesn't fall into one of these two categories then you need to provide an array with additional details such as the operator as well as the value. An example follows and will be discussed in detail.

     $columns = array('feature_id', 'fmin', 'fmax');
    // Regular criteria specifying the parent feature to retrieve locations from.
    $values = array(
      'srcfeature_id' => array(
        'uniquename' => 'MtChr01'
        'type_id' => array(
          'name' => 'pseudomolecule'
        ),
      ),
    );
    // Complex filtering to specify the range to return locations from.
    $values['fmin'][] = array(
      'op' => '>',
      'data' => 15
    );
    $values['fmin'][] = array(
      'op' => '<',
      'data' => 100
    );
    $results = chado_select_record('featureloc', $columns, $values);
  

The above code example will return all of the name, start and end of all the features that start within MtChr1:15-100bp. Note that complex filtering can be used in conjunction with basic filtering and that multiple criteria, even for the same field can be entered.

Parameters

$table: The name of the chado table for inserting

$columns: An array of column names

$values: An associative array containing the values for filtering the results. In the case where multiple values for the same time are to be selected an additional entry for the field should appear for each value. If you need to filter results using more complex methods see the 'Complex Filtering' section below.

$options: An associative array of additional options where the key is the option and the value is the value of that option.

Additional Options Include:

• has_record Set this argument to 'TRUE' to have this function return a numeric value for the number of records rather than the array of records. this can be useful in 'if' statements to check the presence of particula records.
• return_sql Set this to 'TRUE' to have this function return an array where the first element is the sql that would have been run and the second is an array of arguments.
• case_insensitive_columns An array of columns to do a case insensitive search on.
• regex_columns An array of columns where the value passed in should be treated as a regular expression
• order_by An associative array containing the column names of the table as keys and the type of sort (i.e. ASC, DESC) as the values. The results in the query will be sorted by the key values in the direction listed by the value
• is_duplicate: TRUE or FALSE. Checks the values submited to see if they violate any of the unique constraints. If not, the record is returned, if so, FALSE is returned.
• pager: Use this option if it is desired to return only a subset of results so that they may be shown with in a Drupal-style pager. This should be an array with two keys: 'limit' and 'element'. The value of 'limit' should specify the number of records to return and 'element' is a unique integer to differentiate between pagers when more than one appear on a page. The 'element' should start with zero and increment by one for each pager.

-limit: Specifies the number of records to return. -offset: Indicates the number of records to skip before returning records.

Return value

An array of results, FALSE if the query was not executed correctly, an empty array if no records were matched, or the number of records in the dataset if $has_record is set. If the option 'is_duplicate' is provided and the record is a duplicate it will return the duplicated record. If the 'has_record' option is provided a value of TRUE will be returned if a record exists and FALSE will bee returned if there are not records.

Example usage:

Related topics

File

tripal_chado/api/tripal_chado.query.api.inc, line 1203

Provides an API for querying of chado including inserting, updating, deleting and selecting from chado.

Code

function chado_select_record($table, $columns, $values, $options = NULL) {
  // Set defaults for options. If we don't set defaults then
  // we get memory leaks when we try to access the elements.
  if (!is_array($options)) {
    $options = array();
  }
  if (!array_key_exists('case_insensitive_columns', $options)) {
    $options['case_insensitive_columns'] = array();
  }
  if (!array_key_exists('regex_columns', $options)) {
    $options['regex_columns'] = array();
  }
  if (!array_key_exists('order_by', $options)) {
    $options['order_by'] = array();
  }
  if (!array_key_exists('return_sql', $options)) {
    $options['return_sql'] = FALSE;
  }
  if (!array_key_exists('has_record', $options)) {
    $options['has_record'] = FALSE;
  }
  if (!array_key_exists('is_duplicate', $options)) {
    $options['is_duplicate'] = FALSE;
  }
  $pager = array();
  if (array_key_exists('pager', $options)) {
    $pager = $options['pager'];
  }
  $print_errors = FALSE;
  if (isset($options['print_errors'])) {
    $print_errors = $options['print_errors'];
  }

  // Check that our columns and values arguments are proper arrays.
  if (!is_array($columns)) {
    tripal_report_error('tripal_chado', TRIPAL_ERROR, 
    'chado_select_record; the $columns argument must be an array. Columns:%columns', 
    array('%columns' => print_r($columns, TRUE)), 
    array('print' => $print_errors)
    );
    return FALSE;
  }
  if (!is_array($values)) {
    tripal_report_error('tripal_chado', TRIPAL_ERROR, 
    'chado_select_record; the $values argument must be an array. Values:%values', 
    array('%values' => print_r($values, TRUE)), 
    array('print' => $print_errors)
    );
    return FALSE;
  }

  // Get the table description.
  $table_desc = chado_get_schema($table);
  if (!is_array($table_desc)) {
    tripal_report_error('tripal_chado', TRIPAL_WARNING, 
    'chado_insert_record; There is no table description for !table_name', 
    array('!table_name' => $table), array('print' => $print_errors)
    );
    return FALSE;
  }

  $where = array();
  $args = array();

  if ($options['is_duplicate'] and array_key_exists('unique keys', $table_desc)) {
    $ukeys = $table_desc['unique keys'];
    $has_results = 0;

    // Iterate through the unique constraints and reset the values and columns
    // arrays to only include these fields.
    foreach ($ukeys as $cname => $fields) {
      if ($has_results) {
        continue;
      }
      $new_values = array();
      $new_columns = array();
      $new_options = array();
      $has_pkey = 0;

      // Include the primary key in the results returned.
      if (array_key_exists('primary key', $table_desc)) {
        $has_pkey = 1;
        $pkeys = $table_desc['primary key'];
        foreach ($pkeys as $index => $key) {
          array_push($new_columns, $key);
        }
      }

      // Recreate the $values and $columns arrays.
      foreach ($fields as $field) {
        if (array_key_exists($field, $values)) {
          $new_values[$field] = $values[$field];
          // If there is no primary key then use the unique constraint fields.
          if (!$has_pkey) {
            array_push($new_columns, $field);
          }
        }
        // If the field doesn't exist in the values array then
        // substitute any default values.
        elseif (array_key_exists('default', $table_desc['fields'][$field])) {
          $new_values[$field] = $table_desc['fields'][$field]['default'];
          if (!$has_pkey) {
            array_push($new_columns, $field);
          }
        }
        // If there is no value (default or otherwise) check if this field is
        // allowed to be null.
        elseif (!$table_desc['fields'][$field]['not null']) {
          $new_values[$field] = NULL;
          if (!$has_pkey) {
            array_push($new_columns, $field);
          }
        }
        // If the array key doesn't exist in the values given by the caller
        // and there is no default value then we cannot check if the record
        // is a duplicate so return FALSE.
        else {
          tripal_report_error('tripal_chado', TRIPAL_ERROR, 
          'chado_select_record: There is no value for %field thus we cannot ' .
            'check if this record for table, %table, is unique. %values', 
          array('%field' => $field, '%table' => $table, '%values' => print_r($values, TRUE)), 
          array('print' => $print_errors));
          return FALSE;
        }
      }
      $results = chado_select_record($table, $new_columns, $new_values, $new_options);
      // If we have a duplicate record then return the results.
      if (count($results) > 0) {
        $has_results = 1;
      }
      unset($new_columns);
      unset($new_values);
      unset($new_options);
    }
    if ($options['has_record'] and $has_results) {
      return TRUE;
    }
    else {
      return $results;
    }
  }

  // Process the values array into where clauses and retrieve foreign keys. The
  // $where array should always be an integer-indexed array with each value
  // being an array with a 'field', 'op', and 'data' keys with all foreign keys
  // followed.
  foreach ($values as $field => $value) {

    // Require the field be in the table description.
    if (!array_key_exists($field, $table_desc['fields'])) {
      tripal_report_error('tripal_chado', TRIPAL_ERROR, 
      'chado_select_record: The field "%field" does not exist in the table "%table".  Cannot perform query. Values: %array. Fields: %fields', 
      array(
        '%field' => $field,
        '%table' => $table,
        '%array' => print_r($values, 1),
        '%fields' => print_r($table_desc['fields'], 1)), 
      array('print' => $print_errors)
      );
      return array();
    }

    // CASE 1: We have an array for a value.
    if (is_array($value)) {

      // CASE 1a: If there is only one element in the array, treat it the same
      // as a non-array value.
      if (count($value) == 1 AND is_int(key($value))) {

        $value = array_pop($value);
        $op = '=';
        chado_select_record_check_value_type($op, $value, $table_desc['fields'][$field]['type']);

        $where[] = array(
          'field' => $field,
          'op' => $op,
          'data' => $value
        );
      }
      // CASE 1b: If there is a 'data' key in the array then we have the new
      // complex filtering format with a single criteria.
      elseif (isset($value['data']) AND isset($value['op'])) {

        $value['field'] = $field;
        $where[] = $value;
      }
      // CASE 1c: If we have an integer indexed array and the first element is
      // not an array then we have a simple array of values to be used for an 
      // IN clause.
      elseif (is_int(key($value)) AND !is_array(current($value))) {

        $where[] = array(
          'field' => $field,
          'op' => 'IN',
          'data' => $value
        );
      }
      // We have a multi-dimensional array: 2 cases...
      else {

        // CASE 1d: If there is a multi-dimensional array with each sub-array
        // containing a data key then we have the new complex filtering format
        // with multiple criteria.
        if (isset($value[0]['data']) AND isset($value[0]['op'])) {

          foreach ($value as $subvalue) {
            $subvalue['field'] = $field;
            $where[] = $subvalue;
          }
        }
        // CASE 1e: We have a multi-dimensional array that doesn't fit any of 
        // the above cases then we have a foreign key definition to follow.
        else {

          // Select the value from the foreign key relationship for this value.
          $foreign_options = array(
            'regex_columns' => $options['regex_columns'],
          );
          $results = chado_schema_get_foreign_key($table_desc, $field, $value, $foreign_options);

          // Ensure that looking up the foreign key didn't fail in an error.
          if ($results === FALSE OR $results === NULL) {
            tripal_report_error('tripal_chado', TRIPAL_ERROR, 
            'chado_select_record: could not follow the foreign key definition
              for %field where the definition supplied was %value', 
            array('%field' => $field, '%value' => print_r($value, TRUE))
            );
            return array();
          }
          // Ensure that there were results returned.
          elseif (count($results) == 0) {
            tripal_report_error('tripal_chado', TRIPAL_ERROR, 
            'chado_select_record: the foreign key definition for \'%field\' on table \'%table\' ' .
              'returned no results where the definition supplied was %value', 
            array('%field' => $field, '%table' => $table, '%value' => print_r($value, TRUE))
            );
            return array();
          }
          // If there was only a single resutlt then add it using an op of =.
          elseif (count($results) == 1) {
            $results = array_pop($results);
            $op = '=';
            chado_select_record_check_value_type($op, $results, $table_desc['fields'][$field]['type']);

            $where[] = array(
              'field' => $field,
              'op' => $op,
              'data' => $results
            );
          }
          // Otherwise multiple results were returned so we want to form an
          // IN (x, y, z) expression.
          else {
            $where[] = array(
              'field' => $field,
              'op' => 'IN',
              'data' => $results
            );
          }
        }
      }
    }
    // CASE 2: We have a single value.
    else {

      $op = '=';
      chado_select_record_check_value_type($op, $value, $table_desc['fields'][$field]['type']);

      $where[] = array(
        'field' => $field,
        'op' => $op,
        'data' => $value
      );
    }

    // Support Deprecated method for regex conditions.
    $current_key = key($where);
    if (in_array($field, $options['regex_columns'])) {
      $where[$current_key]['op'] = '~*';
    }

  }

  // Now build the SQL.
  if (empty($where)) {
    // Sometimes want to select everything.
    $sql = "SELECT " . implode(', ', $columns) . " ";
    $sql .= 'FROM {' . $table . '} ';
  }
  else {
    $sql = "SELECT " . implode(', ', $columns) . " ";
    $sql .= 'FROM {' . $table . '} ';

    // If $values is empty then we want all results so no where clause.
    if (!empty($values)) {
      $sql .= "WHERE ";
    }
    foreach ($where as $clause_num => $value_def) {

      switch ($value_def['op']) {
        // Deal with 'field IN (x, y, z)' where clauses.
        case 'IN':
          $sql .= $value_def['field'] . " IN (";
          $index = 0;
          foreach ($value_def['data'] as $v) {
            $placeholder = ':' . $value_def['field'] . $clause_num . '_' . $index;
            $sql .= $placeholder . ', ';
            $args[$placeholder] = $v;
            $index++;
          }
          $sql = drupal_substr($sql, 0, -2); // remove trailing ', '
          $sql .= ") AND ";
          break;

          // Deal with IS NULL.
        case 'IS NULL':
          $sql .= $value_def['field'] . ' IS NULL AND ';
          break;

          // Default is [field] [op] [data].
        default:
          $placeholder = ':' . $value_def['field'] . $clause_num;

          // Support case insensitive columns.
          if (in_array($value_def['field'], $options['case_insensitive_columns'])) {
            $sql .= 'lower(' . $value_def['field'] . ') ' . $value_def['op'] . ' lower(' . $placeholder . ') AND ';
          }
          else {
            $sql .= $value_def['field'] . ' ' . $value_def['op'] . ' ' . $placeholder . ' AND ';
          }
          $args[$placeholder] = $value_def['data'];
      }
    } // End foreach item in where clause.
    $sql = drupal_substr($sql, 0, -4); // Get rid of the trailing 'AND '
  } // End if (empty($where)){ } else {

  // Add any ordering of the results to the SQL statement.
  if (count($options['order_by']) > 0) {
    $sql .= " ORDER BY ";
    foreach ($options['order_by'] as $field => $dir) {
      $sql .= "$field $dir, ";
    }
    $sql = drupal_substr($sql, 0, -2); // Get rid of the trailing ', '
  }

  // Limit the records returned.
  if (array_key_exists('limit', $options) and is_numeric($options['limit'])) {
    $sql .= " LIMIT " . $options['limit'];
    if (array_key_exists('offset', $options) and is_numeric($options['offset'])) {
      $sql .= " OFFSET " . $options['offset'];
    }
  }

  // If the caller has requested the SQL rather than the results then do so.
  if ($options['return_sql'] == TRUE) {
    return array('sql' => $sql, 'args' => $args);
  }
  if (array_key_exists('limit', $pager)) {
    $total_records = 0;
    $resource = chado_pager_query($sql, $args, $pager['limit'], $pager['element'], NULL, $total_records);
  }
  else {
    $resource = chado_query($sql, $args);
  }

  // Format results into an array.
  $results = array();
  foreach ($resource as $r) {
    $results[] = $r;
  }
  if ($options['has_record']) {
    return count($results);
  }

  return $results;
}