Builds the SQL statement need to search Chado for the publications that match the user supplied criteria. Tpyically, this function is called by the search form generated by the tripal_pub_search_form() function but this function is included in the API for calling by anyone.

Parameters

$search_array: An array of search criteria provided by the user. The search array is an associative array with the following keys: 'num_criteria': an integer indicating the number of search criteria supplied 'from_year': filters records by a start year 'to_year': filters records by an end year 'criteria': an array of criteria. Each criteria is an associative array with the following keys: 'search_terms': The text used for searching 'scope': The cvterm_id of the property used for filtering 'mode': The operation (e.g. AND, OR or NOT)

$offset: The offset for paging records. The first record returned will be at the offset indicated here, and the next $limit number of records will be returned.

$limit: The number of records to retrieve

total_records: A value passed by reference. This value will get set to the total number of matching records

Return value

a PDO database object of the query results.

Related topics

File

tripal_pub/includes/tripal_pub.pub_search.inc, line 573

Functions responsible for creating the publication search form that allows a user of the site to search for publications that are currently in Chado.

Code

function tripal_search_publications($search_array, $offset, $limit, &$total_records) {

  // build the SQL based on the criteria provided by the user
  // @todo: re-write to support external chado databases.
  $select = "SELECT DISTINCT P.*, CP.nid ";
  $from = "FROM {pub} P
               LEFT JOIN [chado_pub] CP on P.pub_id = CP.pub_id
               INNER JOIN {cvterm} CVT on CVT.cvterm_id = P.type_id
            ";
  $where = "WHERE (NOT P.title = 'null') "; // always exclude the dummy pub
  $order = "ORDER BY P.pyear DESC, P.title ASC";
  $args = array(); // arguments for where clause
  $join = 0;

  $num_criteria = $search_array['num_criteria'];
  $from_year = $search_array['from_year'];
  $to_year = $search_array['to_year'];

  for ($i = 1; $i <= $num_criteria; $i++) {
    $value = $search_array['criteria'][$i]['search_terms'];
    $type_id = $search_array['criteria'][$i]['scope'];
    $mode = $search_array['criteria'][$i]['mode'];
    $op = $search_array['criteria'][$i]['operation'];

    // skip criteria with no values
    if (!$value) {
      continue;
    }

    // to prevent SQL injection make sure our operator is
    // what we expect
    if ($op and $op != "AND" and $op != "OR" and $op != 'NOT') {
      $op = 'AND';
    }
    if ($op == 'NOT') {
      $op = 'AND NOT';
    }
    if (!$op) {
      $op = 'AND';
    }

    // get the scope type
    $values = array('cvterm_id' => $type_id);
    $cvterm = chado_select_record('cvterm', array('name'), $values);
    $type_name = '';
    if (count($cvterm) > 0) {
      $type_name = $cvterm[0]->name;
    }
    if ($type_name == 'Title') {
      $where .= " $op (lower(P.title) LIKE lower(:crit$i)) ";
      $args[":crit$i"] = '%' . $value . '%';
    }
    elseif ($type_name == 'Year') {
      $where .= " $op (lower(P.pyear) = lower(:crit$i)) ";
      $args[":crit$i"] = '%' . $value . '%';
    }
    elseif ($type_name == 'Volume') {
      $where .= " $op (lower(P.volume) = lower(:crit$i)) ";
      $args[":crit$i"] = '%' . $value . '%';
    }
    elseif ($type_name == 'Issue') {
      $where .= " $op (lower(P.issue) = lower(:crit$i)) ";
      $args[":crit$i"] = '%' . $value . '%';
    }
    elseif ($type_name == 'Journal Name') {
      $from .= " LEFT JOIN {pubprop} PP$i ON PP$i.pub_id = P.pub_id AND PP$i.type_id = :crit$i ";
      $where .= " $op ((lower(P.series_name) = lower(:crit$i) and CVT.name = 'Journal Article') OR
      (lower(PP$i.value) = lower(:crit$i))) ";
      $args[":crit$i"] = $type_id;
    }
    elseif ($type_name == 'Conference Name') {
      $from .= " LEFT JOIN {pubprop} PP$i ON PP$i.pub_id = P.pub_id AND PP$i.type_id = :crit$i ";
      $where .= " $op ((lower(P.series_name) = lower(:crit$i) and CVT.name = 'Conference Proceedings') OR
      (lower(PP$i.value) = lower(:crit$i))) ";
      $args[":crit$i"] = $type_id;
    }
    elseif ($type_name == 'Publication Type') {
      $where .= " $op (lower(CVT.name) = lower(:crit$i))";
      $args[":crit$i"] = $value;
    }
    elseif ($type_id == 0) { //'Any Field'
      $from .= " LEFT JOIN {pubprop} PP$i ON PP$i.pub_id = P.pub_id ";
      $where .= " $op (lower(PP$i.value)  LIKE lower(:crit$i) OR
      lower(P.title) LIKE lower(:crit$i) OR
      lower(P.volumetitle) LIKE lower(:crit$i) OR
      lower(P.publisher) LIKE lower(:crit$i) OR
      lower(P.uniquename) LIKE lower(:crit$i) OR
      lower(P.pubplace) LIKE lower(:crit$i) OR
      lower(P.miniref) LIKE lower(:crit$i) OR
      lower(P.series_name) LIKE lower(:crit$i)) ";
      $args[":crit$i"] = '%' . $value . '%';
    }
    // for all other properties
    else {
      $from .= " LEFT JOIN {pubprop} PP$i ON PP$i.pub_id = P.pub_id AND PP$i.type_id = :type_id$i ";
      $where .= " $op (lower(PP$i.value) LIKE lower(:crit$i)) ";
      $args[":crit$i"] = '%' . $value . '%';
      $args[":type_id$i"] = $type_id;
    }
  }
  if ($from_year and $to_year) {
    $where .= " AND (P.pyear ~ '....' AND to_number(P.pyear,'9999') >= :from$i AND to_number(P.pyear,'9999') <= :to$i) ";
    $args[":from$i"] = $from_year;
    $args[":to$i"] = $to_year;
  }
  $sql = "$select $from $where $order  LIMIT " . (int) $limit . ' OFFSET ' . (int) $offset;
  $count = "SELECT count(*) FROM ($select $from $where $order) as t1";

  // first get the total number of matches
  $total_records = chado_query($count, $args)->fetchField();
  $results = chado_query($sql, $args);

  return $results;
}