function biblio_build_query in Bibliography Module 6.2
Same name and namespace in other branches
- 6 biblio.pages.inc \biblio_build_query()
- 7 includes/biblio.pages.inc \biblio_build_query()
- 7.2 includes/biblio.pages.inc \biblio_build_query()
Creates an SQL query to select and order biblio type content.
biblio_db_search builds the SQL query which will be used to select and order "biblio" type nodes. The query results are then passed to biblio_show_results for output
Parameters
$arg_list:
Return value
array An associative array with the following keys:
- query:
- query_terms:
- count_query:
- args:
- sort_attrib:
- rss:
1 call to biblio_build_query()
- biblio_db_search in includes/
biblio.pages.inc - Page callback: Displays a listing of biblio type of content.
File
- includes/
biblio.pages.inc, line 137 - Functions in the biblio module related to filtering and page generation.
Code
function biblio_build_query($arg_list) {
global $user, $db_type;
// Count of biblio_contributor (bc), increases with each invocation.
static $bcc = 0;
static $bkd = 0;
// Count of terms, increase with every invocation.
static $tcc = 0;
$inline = $rss_info['feed'] = FALSE;
$joins = array();
$selects = array();
$count_selects = array();
$count_limit = '';
$selects[] = "DISTINCT(n.nid)";
$count_selects[] = "DISTINCT(n.nid)";
$selects[] = "bt.name as biblio_type_name";
$joins[] = "left join {biblio} b on n.vid=b.vid ";
$joins[] = "inner join {biblio_types} bt on b.biblio_type=bt.tid ";
// POSIX regular expression matching, case insensitive
$match_op = $db_type == 'pgsql' ? '~*' : 'RLIKE';
// The following is to be used to compare titles
// LOWER() is required for case insensitive sorting (at least, in PostgreSQL)
$sort_title = 'biblio_sort_title';
$limit = '';
if (variable_get('biblio_view_only_own', 0)) {
$limit .= " AND n.uid = {$user->uid} ";
}
if (array_search('sort', $arg_list) === FALSE) {
$arg_list[] = 'sort';
$arg_list[] = variable_get('biblio_sort', 'year');
$arg_list[] = 'order';
$arg_list[] = strtolower(variable_get('biblio_order', 'desc'));
}
$limits = null;
if (!isset($_SESSION['biblio_filter']) || !is_array($_SESSION['biblio_filter'])) {
$_SESSION['biblio_filter'] = array();
}
$session =& $_SESSION['biblio_filter'];
if (!in_array('no_filters', $arg_list)) {
foreach ($session as $filter) {
$arg_list = array_merge($arg_list, $filter);
}
}
if (count($arg_list)) {
$args = array();
while ($arg_list) {
$type = $arg_list[0];
array_shift($arg_list);
switch ($type) {
case 'no_filters':
break;
case 'inline':
$inline = TRUE;
break;
case 'rss.xml':
$rss_info['feed'] = TRUE;
$count_limit = 'LIMIT ' . variable_get('biblio_rss_number_of_entries', 10);
break;
case 'profile':
$inline = "profile";
break;
case 'cid':
case 'aid':
$bcc++;
$term = explode("?", array_shift($arg_list));
$joins[] = "inner join {biblio_contributor} as bc" . $bcc . " on n.vid = bc" . $bcc . ".vid";
$where[] = "bc" . $bcc . ".cid = '%d' ";
$terms[] = db_escape_string($term[0]);
array_push($args, $type, $term[0]);
break;
case 'term':
case 'term_id':
$term = explode("?", array_shift($arg_list));
$joins[] = "inner join {term_node} as tn" . $tcc . " on n.vid = tn" . $tcc . ".vid";
if ($type == 'term') {
$joins[] = "inner join {term_data} as td on tn" . $tcc . ".tid= td.tid";
$where[] = "td.name = '%s' ";
}
elseif ($type == 'term_id') {
$where[] = "tn" . $tcc . ".tid = '%d' ";
}
$terms[] = db_escape_string($term[0]);
array_push($args, $type, $term[0]);
$tcc++;
break;
case 'tg':
$term = explode("?", array_shift($arg_list));
$where[] = "substring({$sort_title},1 ,1)" . $match_op . " LOWER('%s')";
$terms[] = db_escape_string($term[0]);
array_push($args, $type, $term[0]);
break;
case 'ag':
//selects entries whoose authors firstname starts with the letter provided
$term = explode("?", array_shift($arg_list));
$where[] = " UPPER(substring(bcd.lastname,1,1)) = '%s' ";
//$where['bc-rank'] = "bc.rank=0";
$joins['bc'] = ' INNER JOIN {biblio_contributor} as bc on b.vid = bc.vid ';
$joins['bcd'] = ' JOIN {biblio_contributor_data} as bcd on bc.cid = bcd.cid ';
$terms[] = db_escape_string(strtoupper($term[0]));
array_push($args, $type, $term[0]);
break;
case 'author':
$bcc++;
$term = explode("?", array_shift($arg_list));
if (is_numeric($term[0])) {
$joins[] = "inner join {biblio_contributor} as bc" . $bcc . " on n.vid = bc" . $bcc . ".vid";
$cids = db_query('SELECT cid FROM {biblio_contributor_data}
WHERE cid = %d OR aka = (SELECT aka FROM {biblio_contributor_data} WHERE cid = %d)', $term[0], $term[0]);
$wr = '';
while ($cid = db_fetch_object($cids)) {
$wr .= empty($wr) ? '' : ' OR ';
$wr .= "bc" . $bcc . ".cid = {$cid->cid} ";
}
$where[] = !empty($wr) ? $wr : "bc" . $bcc . ".cid = -1 ";
}
else {
$where[] = " bcd" . $bcc . '.name ' . $match_op . ' "[[:<:]]%s[[:>:]]" ';
$joins[] = " JOIN {biblio_contributor} as bc" . $bcc . " on b.vid = bc" . $bcc . ".vid ";
$joins[] = " JOIN {biblio_contributor_data} as bcd" . $bcc . " on bc" . $bcc . ".cid = bcd" . $bcc . ".cid ";
$terms[] = db_escape_string($term[0]);
$rss_info['title'] = t("Publications by " . $term[0]);
$rss_info['description'] = t("These publications by %author are part of the works listed at %sitename", array(
'%author' => $term[0],
'%sitename' => variable_get('site_name', 'Drupal'),
));
$rss_info['link'] = '/author/' . $term[0];
}
array_push($args, $type, $term[0]);
break;
case 'publisher':
$term = explode("?", array_shift($arg_list));
$where[] = "b.biblio_publisher " . $match_op . " '%s' ";
$terms[] = db_escape_string($term[0]);
array_push($args, $type, $term[0]);
break;
case 'year':
$term = db_escape_string(array_shift($arg_list));
$where[] = "b.biblio_year=%d ";
//$limit .= " AND b.biblio_year=%d ";
$terms[] = (int) $term;
array_push($args, $type, (int) $term);
break;
case 'uid':
$term = db_escape_string(array_shift($arg_list));
$where[] = "n.uid=%d ";
//$limit .= " AND b.biblio_year=%d ";
$terms[] = (int) $term;
array_push($args, $type, (int) $term);
break;
case 'keyword':
$bkd++;
$term = explode("?", array_shift($arg_list));
if (is_numeric($term[0])) {
$terms[] = db_escape_string($term[0]);
$joins[] = "inner join {biblio_keyword} as bk{$bkd} on n.vid = bk{$bkd}.vid";
//$joins[] = "inner join {biblio_keyword_data} as bkd on bk.kid= bkd.kid";
$where[] = "bk{$bkd}.kid = %d ";
}
elseif (strlen($term[0]) == 1) {
$joins['bk'] = ' JOIN {biblio_keyword} as bk on b.vid = bk.vid ';
$joins['bkd'] = ' LEFT JOIN {biblio_keyword_data} as bkd on bk.kid = bkd.kid ';
$selects[] = "bkd.word as biblio_keyword";
$where[] = " UPPER(substring(bkd.word,1,1)) = '%s' ";
$terms[] = db_escape_string(strtoupper($term[0]));
//array_push($args, $type, $term[0]);
}
else {
$where[] = " bkd" . $bkd . '.word ' . $match_op . ' "[[:<:]]%s[[:>:]]" ';
$joins[] = " JOIN {biblio_keyword} as bk" . $bkd . " on b.vid = bk" . $bkd . ".vid ";
$joins[] = " JOIN {biblio_keyword_data} as bkd" . $bkd . " on bk" . $bkd . ".kid = bkd" . $bkd . ".kid ";
$terms[] = db_escape_string($term[0]);
$rss_info['title'] = t("Keyword " . $term[0]);
$rss_info['description'] = t("These publications, containing the keyword: %keyword, are part of the works listed at %sitename", array(
'%keyword' => $term[0],
'%sitename' => variable_get('site_name', 'Drupal'),
));
$rss_info['link'] = '/keyword/' . $term[0];
}
array_push($args, $type, $term[0]);
break;
case 'citekey':
$term = explode("?", array_shift($arg_list));
$terms[] = db_escape_string($term[0]);
$where[] = "b.biblio_citekey= '%s' ";
array_push($args, $type, $term[0]);
break;
case 'type':
$term = db_escape_string(array_shift($arg_list));
$where[] = "b.biblio_type=%d ";
//$limit .= $operator. "b.biblio_type=%d ";
$terms[] = (int) $term;
array_push($args, $type, (int) $term);
break;
case 'order':
$term = db_escape_string(strtolower(array_shift($arg_list))) == 'desc' ? 'desc' : 'asc';
$sort_attrib['order'] = $term;
break;
case 'sort':
$term = db_escape_string(array_shift($arg_list));
$sort_attrib['sort'] = $term;
switch ($term) {
case 'type':
$sortby = "ORDER BY bt.name %s, {$sort_title}";
$selects[] = "bt.name, {$sort_title}";
break;
case 'title':
$sortby = "ORDER BY {$sort_title} %s";
$selects[] = $sort_title;
break;
case 'author':
$sortby = "ORDER BY bcd.lastname %s ";
$where['bc-rank'] = "bc.rank=0";
$where['bc-auth-category'] = "bc.auth_category IN (1,5)";
$joins['bc'] = ' INNER JOIN {biblio_contributor} as bc on b.vid = bc.vid ';
$joins['bcd'] = ' JOIN {biblio_contributor_data} as bcd on bc.cid = bcd.cid ';
$selects[] = "bcd.lastname";
break;
case 'keyword':
// added msh 070808
$sortby = "ORDER BY bkd.word %s ";
$joins['bk'] = ' JOIN {biblio_keyword} as bk on b.vid = bk.vid ';
$joins['bkd'] = ' LEFT JOIN {biblio_keyword_data} as bkd on bk.kid = bkd.kid ';
$selects[] = "bkd.word as biblio_keyword";
//$count_selects[] = "bkd.word";
break;
case 'year':
default:
$sortby = "ORDER BY b.biblio_year %s, b.biblio_date %s, {$sort_title} %s";
$selects[] = "b.biblio_year, b.biblio_date";
$selects[] = $sort_title;
}
//end switch
break;
case 'search':
$term = explode("?", array_shift($arg_list));
$result_nids = split(',', $term[0]);
$where[] = "n.nid in (" . db_placeholders($result_nids) . ")";
foreach ($result_nids as $result_nid) {
$terms[] = db_escape_string($result_nid);
array_push($args, $type, $result_nid);
}
// Save search keyword to show in the filter list.
$term = array_shift($arg_list);
array_push($args, $type, $term);
break;
default:
$fields = biblio_get_db_fields();
$term = explode("?", array_shift($arg_list));
if (in_array("biblio_{$type}", $fields)) {
$where[] = "b.biblio_{$type} " . $match_op . " '%s' ";
$terms[] = db_escape_string($term[0]);
array_push($args, $type, $term[0]);
}
break;
}
}
}
$where[] = "n.type='biblio' ";
if ($user->uid != 1) {
$where[] = 'n.status = 1 ';
}
//show only published entries to everyone except admin
$select = implode(', ', $selects);
$count_select = implode(', ', $count_selects);
$join = implode(' ', $joins);
$where_clause = count($where) > 1 ? '(' . implode(') AND (', $where) . ')' : $where[0];
$query = db_rewrite_sql("SELECT {$select} FROM {node} n {$join} WHERE {$where_clause} {$limit} {$sortby} {$count_limit}");
$count_query = db_rewrite_sql("SELECT COUNT({$count_select}) FROM {node} n {$join} WHERE {$where_clause} {$limit} {$count_limit}");
$_SESSION['last_biblio_query'] = $query;
$terms[] = $sort_attrib['order'];
// this is either asc or desc to be inserted into the first term of the ORDER clause
if ($sort_attrib['sort'] == 'year') {
$terms[] = $sort_attrib['order'];
// we need any extra order term when sorting by year since there are to date terms biblio_year and biblio_date
$terms[] = 'asc';
}
$_SESSION['last_biblio_query_terms'] = $terms;
return array(
'query' => $query,
'query_terms' => $terms,
'count_query' => $count_query,
'args' => $args,
'sort_attrib' => $sort_attrib,
'rss' => $rss_info,
);
}