Chado Custom Search API

This module provides a class-based API for creating fast Chado-focused searches. If you find Drupal Views-based searches too slow due to the amount of data available or simply difficult to customize to your needs, this API is your solution!

For each search, define a single class and list it in hook_chado_custom_search(). Just list your columns/filters and define the query to be used and you have a fully functional search with the following features:

  • automatically generated filter form
  • table listing of results
  • links to Tripal 3 entities
  • simple but fast pager
  • supports either “require input” or “basic search”

This API also gives you complete control over the search. Override any portion of the default filter form, supply custom form validation, optimize your query, etc. For more information on what you can accomplish or how to get started, see the following documentation:

Quick Start

  1. Implement hook_chado_custom_search to tell the API about the search you would like to create.
/**
 * Implement hook_chado_custom_search().
 *
 * This hook simply lists the machine name of the searches so that we can find
 * the info hook. We've done this for performance reasons.
 */
function example_ccsearch_chado_custom_search() {
  $searches = [];

  $searches['BreedingCrossSearch'] = 'Breeding Cross Search';

  return $searches;
}
  1. Create a class which extends ChadoCustomSearch. At a minimum you need to set the editable static constants and the getQuery() method. Just copy this basic search and change it to suit your needs.
  2. Clear the cache, navigate to the path defined in the class and enjoy your custom search!

Editable Constants

The ChadoCustomSearch defines a number of editable constants to make it easy to customize your search.

Title

The human readable title of this search. This will be used in listings and shown on the search page as the title.

public static $title = 'Custom Chado Search';

Module

The machine name of the module providing this search.

public static $module = 'custom_chado_search';

Description

A description of this search. This is shown at the top of the search page and used for the menu item.

public static $description = 'A default search which must be extended.';

Permissions

The machine names of the permissions with access to this search. This is used to map your search to existing permissions. It must be an array and is used in hook_menu(). Some examples include ‘access content’ and ‘administer tripal’.

public static $permissions = ['access content'];

If you want to use custom permissions, make sure to define them first in your module file using hook_permission().

Require Submit

If TRUE, this search will require the submit button to be clicked before executing the query; whereas, if FALSE it will be executed on the first page load without parameters.

public static $require_submit = TRUE;

Note

Keep this in mind when building your query in the getQuery() method. If this variable is set to FALSE, then defaults will be supplied to getQuery() as the filter values.

Paging

If $pager is TRUE, the API will add a simple pager to your search results with a previous and next page link as well as indication of what page they are on. A simple pager is used for speed since it doesn’t require counting the search results which is notoriously slow in PostgreSQL. You can also control the number of items per page using $num_items_per_page.

public static $pager = TRUE;
public static $num_items_per_page = 25;

Note

The API handles adding the pager to the search page. This includes determining which links should be available and changing the filter values to keep track of the page. You need to ensure your getQuery() method returns the appropriate results for a given page.

Attached CSS/JS

This allows you to add custom CSS and JS files to your search form and results page.

public static $attached = [
  'css' => [],
  'js' => [],
];

Field/Filter Information

This is arguably the most important editable constant. This is where you indicate the columns you want displayed in your results table (fields) and the filters you want made available to your users (filters).

public static $info = [
  // Lists the columns in your results table.
  'fields' => [
    'column_name' => [
      'title' => 'Title',
      // This keyval is optional. It's used to make the current
      // column a link. The link is made automagically as long as
      // you add the id_column to your query.
      'entity_link' => [
        'chado_table' => 'feature',
        'id_column' => 'feature_id',
      ],
    ],
  ],
  // The filter criteria available to the user.
  // This is used to generate a search form which can be altered.
  'filters' => [
    'column_name' => [
      'title' => 'Title',
      'help' => 'A description for users as to what this filter is.',
    ],
  ],
];

This API supports result links for Tripal Entities. Define the entity link for any field/column in your results table and the API will look up the appropriate bio_data_id given the chado base table id and the name of the base table. This is done using the chado_get_record_entity_by_table() API function provided by Tripal.

Button Text

This allows you to customize the text shown on the submit button at the bottom of the filter form.

public static $button_text = 'Search';

getQuery(): Define your search query

This API does not programmatically generate the query for you. Instead, you must define the query yourself in the getQuery() method of your custom search class. This design decision was made based on the core goal of the API (Faster, optimized searches) and ensures I don’t make assumptions about your data but rather leave it in the hands of the expert: you!

Note

I highly recommend testing your search queries on a full clone of your production site as the PostgreSQL query planner will base the underlying method on the data being queried. As such, the most performant query on a small test site will not be the same as on your production site.

/**
 * Defines the query and arguments to use for the search.
 *
 * MUST OVERRIDE!
 *
 * @param string $query
 *   The full SQL query to execute. This will be executed using chado_query()
 *   so use curly brackets appropriately. Use :placeholders for any values.
 * @param array $args
 *   An array of arguments to pass to chado_query(). Keys must be the
 *   placeholders in the query and values should be what you want them set to.
 * @param $offset
 *   The current offset. This is primarily used for pagers.
 */
public function getQuery(&$query, &$args, $offset) {}

To start, define the above method in your custom search class. At the top of your method, the $query variable will be empty -it is up to you to defined the query as a string. Lets look at a simple example with no filters:

public function getQuery(&$query, &$args, $offset) {

  $query = 'SELECT organism_id, genus, species FROM {organism}';

}

This custom search will display all organisms in your chado database to the user in the results table. It does not take any filter criteria into account nor does it support paging.

Filter Criteria

Filtering of queries is recommended to be done through Drupal placeholders. This helps protect your site from SQL injection and other security concerns. To implement Drupal placeholders in your query, add :placeholdername where you would normally concatenate a value and then add :placeholdername => $value to the $args array. The Chado Custom Search API will handle the rest!

public function getQuery(&$query, &$args, $offset) {

  // Retrieve the filter results already set.
  $filter_results = $this->values;

  // Define the base query.
  $query = 'SELECT organism_id, genus, species FROM {organism}';

  // If a filter value is available then add it to a list of
  // where clauses to be added.
  $where = [];
  // -- genus
  if (!empty($filter_results['genus'])) {
    $where[] = 'genus = :genus';
    $args[':genus'] = $filter_results['genus'];
  }
  // -- species
  if (!empty($filter_results['species'])) {
    $where[] = 'species = :species';
    $args[':species'] = $filter_results['species'];
  }

  // Then after you've checked all your filter criteria,
  // add the where clauses to your base query.
  $query .= ' WHERE ' . implode(' AND ', $where);
}

Paging

The Chado Custom Search API handles determining the offset required for the page the user is on based on the number of items per page you set in the editable constants. As such, all you need to do in your query, is add the limit/offset as so:

public function getQuery(&$query, &$args, $offset) {

  // Determine what your query should be based on the filter criteria.

  // Handle paging.
  $limit = $this::$num_items_per_page + 1;
  $query .= "\n" . ' LIMIT ' . $limit . ' OFFSET ' . $offset;

}

form: Alter your filter form

Customizing the filter form for your search can be done by implementing the form() method in your Chado Custom Search class.

/**
 * Generate the filter form.
 *
 * The base class will generate textfields for each filter defined in $info,
 * set defaults, labels and descriptions, as well as, create the search
 * button.
 *
 * Extend this method to alter the filter form.
 */
public function form($form, $form_state) {

  // This allows the base class to generate your form for you.
  $form = parent::form($form, $form_state);

  // Now just alter the elements you want to change.

}

The base class will use the information provided in $info['filters'] to generate text fields for each filter defined. The key for the textfield will be the key provided in $info['filters']. Now you can use the Drupal Form API to alter the form array generated by the base class to customize your form.

For example, the following will change the genus and species filters on our organism search to drop-downs with values present in the database. We did not do dependant drop-downs in the following example for simplicity.

public function form($form, $form_state) {

  // This allows the base class to generate your form for you.
  $form = parent::form($form, $form_state);

  // Now just alter the elements you want to change.
  // Genus: drop-down.
  $options = chado_query('SELECT genus FROM {organism} GROUP BY genus')->fetchAllKeyed(0,0);
  $form['genus']['#type'] = 'select';
  $form['genus']['#options'] = $options;

  // Species: drop-down.
  $options = chado_query('SELECT species FROM {organism} GROUP BY species')->fetchAllKeyed(0,0);
  $form['species']['#type'] = 'select';
  $form['species']['#options'] = $options;

  // Always make sure to return the form!
  return $form;
}

validateForm: Validate user search criteria!

If you want to validate the values submitted by users as search criteria, implement validateForm() in your Chado Custom Search class. You can access the form values through the $form_state['values'] variable. If they don’t match expectations, you can reject them with a message using form_set_error().

public function validateForm($form, $form_state) {
  $values = $form_state['values'];

  if (empty($values['my element'])) {
    form_set_error('my element', 'My Element is required.');
  }
}

formatResults: Customize result display

The formatResults() method is used to format the results from the query. The results are formatted as a table by default. If you wish to override the result display, make sure to add your custom formatted results to the $form array for them to be displayed.

/**
 * Format the results within the $form array.
 *
 * The base class will format the results as a table.
 *
 * @param array $form
 *   The current form array.
 * @param array $results
 *   The results to format. This will be an array of standard objects where
 *   the keys map to the keys in $info['fields'].
 */
public function formatResults(&$form, $results) {

  // Format the results in the $results array.
  $output = '';

  // Then add the formatted results to the form.
  $form['results'] = [
    '#type' => 'markup',
    '#markup' => $output,
  ];
}

add_pager: Override pager

To change the pager, override the add_pager() method.

/**
 * Adds a pager to the form.
 *
 * @param $form
 *   The form array to add the pager to.
 * @param $form_state
 *   The current state of the form.
 * @return
 *   The original form with the pager added.
 */
public function add_pager($form, $num_results) {

  // Determine the current page and offset using the query parameters.
  $q = drupal_get_query_parameters();
  $offset = (isset($q['offset'])) ? $q['offset'] : 0;
  $page_num = (isset($q['page_num'])) ? $q['page_num'] : 1;

  // Do your logic to render your pager.
  $output = '';

  // Add the pager to the result page.
  $form['pager'] = [
    '#type' => 'markup',
    '#markup' => $output,
    '#prefix' => '<div class="pager-container"><div class="pager">',
    '#suffix' => '</div></div>',
    '#weight' => 100,
  ];

  return $form;
}