Method:
listJoin()
Class:
STATEMENTS
Method Call:
include_once("core/sql/STATEMENTS.php");
STRING STATEMENTS::listJoin([STRING: $sortMethod = FALSE, STRING: $condition = FALSE]);
Description:
Returns a string of LEFT JOIN SQL clasues directly pertaining to resources, formatted for inclusion in a SQL statement
such as select() and intended to be used when listing bibliographic resources.
$sortMethod can be one of 'title', 'creator', 'publisher', 'timestamp' or 'year' and provides the sort order. If something other than 'year' is set, fields 'year1', 'year2' and 'year3' are used for 2nd., 3rd. and 4th. order sorting. If $sortMethod is not set, no SQL ORDER clause is added so the developer should append their own after calling this method.
$condition is a SQL WHERE clause.
Usage:
$join = STATEMENTS::listJoin();Example:
$this->template->setVar('heading', $this->messages->text("heading", "list"));
if($userBibCondition = $this->common->userBibCondition())
$userBibCondition = " WHERE " . $userBibCondition;
include_once("core/sql/STATEMENTS.php");
$stmt = new STATEMENTS($this->db);
$sql = $this->db->select(array("WKX_resource"),
$stmt->listFields($this->session->getVar('list_method')),
$stmt->listJoin('creator'), $userBibCondition));
$queryString = "action=listProcess";
list($pString, $pagingString) = $common->listResources($sqlTotal, $queryString, 'list');
$template->setVar('body', $pString);
$template->setVar('paging', $pagingString);
$sql will be (if the user is browsing a user bibliography that comprises three resources):
SELECT WKX_resource.id AS `resourceId`, `type`, `title`, `subtitle`, `noSort`, `url`, `isbn`, `field1`, `field2`, `field3`, `field4`, `field5`, `field6`, `field7`, `field8`, `field9`, `file`, `collection`, `publisher`, `miscField1`, `miscField2`, `miscField3`, `miscField4`, `tag`, `addUserIdResource`, `editUserIdResource`, `year1`, `year2`, `year3`, `pageStart`, `pageEnd`, `creator1`, `creator2`, `creator3`, `creator4`, `creator5`, `quotes`, `paraphrases`, `musings`, `publisherName`, `publisherLocation`, `publisherType`, `collectionTitle`, `collectionTitleShort`, `collectionType`, WKX_resource_timestamp.timestamp AS `timestamp`, FROM `WKX_resource` LEFT JOIN `WKX_resource_misc` ON WKX_resource.id = WKX_resource_misc.id LEFT JOIN `WKX_resource_year` ON WKX_resource.id = WKX_resource_year.id LEFT JOIN `WKX_resource_page` ON WKX_resource.id = WKX_resource_page.id LEFT JOIN `WKX_resource_creator` ON WKX_resource.id = WKX_resource_creator.id LEFT JOIN `WKX_resource_summary` ON WKX_resource.id = WKX_resource_summary.id LEFT JOIN `WKX_resource_note` ON WKX_resource.id = WKX_resource_note.id LEFT JOIN `WKX_publisher` ON `publisher` = WKX_publisher.id LEFT JOIN `WKX_collection` ON `collection` = WKX_collection.id LEFT JOIN `WKX_resource_timestamp` ON WKX_resource_timestamp.id = WKX_resource.id LEFT JOIN `WKX_creator` ON WKX_creator.id=COALESCE(COALESCE(`creator1`,`creator2`)) WHERE (WKX_resource.id = '652' OR WKX_resource.id = '653' OR WKX_resource.id = '654' OR WKX_resource.id = '655') ORDER BY `surname`, `firstname`, COALESCE(`year1`, `year2`, `year3`)