function chado_schema_get_foreign_key

2.x chado_schema_get_foreign_key($table_desc, $field, $values, $options = NULL)

Gets the value of a foreign key relationship

This function is used by chado_select_record, chado_insert_record, and chado_update_record to iterate through the associate array of values that gets passed to each of those routines. The values array is nested where foreign key contraints are used to specify a value that. See documentation for any of those functions for further information.

  $values = array(
    'genus' => 'Citrus',
    'species' => 'sinensis',
  $value = chado_schema_get_foreign_key('feature', 'organism_id',$values);

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.


$table_desc: A table description for the table with the foreign key relationship to be identified generated by hook_chado_<table name>_schema()

$field: The field in the table that is the foreign key.

$values: An associative array containing the values

$options: An associative array of additional options where the key is the option and the value is the value of that option. These options are passed on to chado_select_record.

Additional Options Include:

  • 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

Return value

A string containg the results of the foreign key lookup, or FALSE if failed.

Example usage:

Related topics

5 calls to chado_schema_get_foreign_key()
chado_delete_record in tripal_core/api/
Provides a generic function for deleting a record(s) from any chado table
chado_insert_record in tripal_core/api/
Provides a generic routine for inserting into any Chado table
chado_select_record in tripal_core/api/
Provides a generic routine for selecting data from a Chado table
chado_update_record in tripal_core/api/
Provides a generic routine for updating into any Chado table
tripal_core_chado_get_foreign_key in tripal_core/api/
1 string reference to 'chado_schema_get_foreign_key'


tripal_core/api/, line 1727
Provides an API for querying of chado including inserting, updating, deleting and selecting from chado.


function chado_schema_get_foreign_key($table_desc, $field, $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();

  // get the list of foreign keys for this table description and
  // iterate through those until we find the one we're looking for
  $fkeys = '';
  if (array_key_exists('foreign keys', $table_desc)) {
    $fkeys = $table_desc['foreign keys'];
  if ($fkeys) {
    foreach ($fkeys as $name => $def) {
      if (is_array($def['table'])) {
        //foreign key was described 2X
        $message = "The foreign key " . $name . " was defined twice. Please check modules "
          . "to determine if hook_chado_schema_<version>_" . $table_desc['table'] . "() was "
          . "implemented and defined this foreign key when it wasn't supposed to. Modules "
          . "this hook was implemented in: " . implode(', ', 
          module_implements("chado_" . $table_desc['table'] . "_schema")) . ".";
        tripal_report_error('tripal_core', $message);
        drupal_set_message(check_plain($message), 'error');
      $table = $def['table'];
      $columns = $def['columns'];

      // iterate through the columns of the foreign key relationship
      foreach ($columns as $left => $right) {
        // does the left column in the relationship match our field?
        if (strcmp($field, $left) == 0) {
          // the column name of the foreign key matches the field we want
          // so this is the right relationship.  Now we want to select
          $select_cols = array($right);
          $result = chado_select_record($table, $select_cols, $values, $options);
          $fields = array();
          if ($result and count($result) > 0) {
            foreach ($result as $obj) {
              $fields[] = $obj->$right;
            return $fields;
  else {
    // @todo: what do we do if we get to this point and we have a fk
    // relationship expected but we don't have any definition for one in the
    // table schema??
    $version = $GLOBALS["chado_version"];
    $message = t("There is no foreign key relationship defined for " . $field . " .
       To define a foreign key relationship, determine the table this foreign
       key referrs to (<foreign table>) and then implement
       hook_chado_chado_schema_v<version>_<foreign table>(). See
       tripal_feature_chado_v1_2_schema_feature for an example. Chado version: $version");
    tripal_report_error('tripal_core', $message);
    drupal_set_message(check_plain($message), 'error');

  return array();