/**
* Get the results from the database
*
* @param Int $forumHolderID ForumHolderID to limit it too
* @param String $query
* @param String $order
* @param Int Offset
* @param Int Limit
*
* @return DataObjectSet
*/
public function getResults($forumHolderID, $query, $order, $offset = 0, $limit = 10)
{
// Search for authors
$SQL_queryParts = split(' +', trim($query));
foreach ($SQL_queryParts as $SQL_queryPart) {
$SQL_clauses[] = "\"FirstName\" LIKE '%{$SQL_queryPart}%' OR \"Surname\" LIKE '%{$SQL_queryPart}' OR \"Nickname\" LIKE '%{$SQL_queryPart}'";
}
$potentialAuthors = DataObject::get('Member', implode(" OR ", $SQL_clauses), '"ID" ASC');
$SQL_authorClause = '';
$SQL_potentialAuthorIDs = array();
if ($potentialAuthors) {
foreach ($potentialAuthors as $potentialAuthor) {
$SQL_potentialAuthorIDs[] = $potentialAuthor->ID;
}
$SQL_authorList = implode(", ", $SQL_potentialAuthorIDs);
$SQL_authorClause = "OR \"Post\".\"AuthorID\" IN ({$SQL_authorList})";
}
// Work out what sorting method
switch ($order) {
case 'date':
$sort = "\"Post\".\"Created\" DESC";
break;
case 'title':
$sort = "\"ForumThread\".\"Title\" ASC";
break;
default:
$sort = "\"RelevancyScore\" DESC";
break;
}
$baseSelect = "SELECT \"Post\".\"ID\", \"Post\".\"Created\", \"Post\".\"LastEdited\", \"Post\".\"ClassName\", \"ForumThread\".\"Title\", \"Post\".\"Content\", \"Post\".\"ThreadID\", \"Post\".\"AuthorID\", \"ForumThread\".\"ForumID\"";
$baseFrom = "FROM \"Post\"\n\t\t\tJOIN \"ForumThread\" ON \"Post\".\"ThreadID\" = \"ForumThread\".\"ID\"\n\t\t\tJOIN \"" . ForumHolder::baseForumTable() . "\" \"ForumPage\" ON \"ForumThread\".\"ForumID\"=\"ForumPage\".\"ID\"";
// each database engine does its own thing
switch (DB::getConn()->getDatabaseServer()) {
case 'postgresql':
$queryString = "\n\t\t\t\t\t{$baseSelect}\n\t\t\t\t\t{$baseFrom}\t\n\t\t\t\t\t, to_tsquery('english', '{$query}') AS q";
$limitString = "LIMIT {$limit} OFFSET {$offset};";
break;
case 'mssql':
$queryString = "\n\t\t\t\t\t{$baseSelect}\n\t\t\t\t\t{$baseFrom}\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t(CONTAINS(\"ForumThread\".\"Title\", '{$query}') OR CONTAINS(\"Post\".\"Content\", '{$query}')\n\t\t\t\t\t\tAND \"ForumPage\".\"ParentID\"='{$forumHolderID}'";
// @todo fix this to use MSSQL's version of limit/offsetB
$limitString = false;
break;
default:
$queryString = "\n\t\t\t\t\t{$baseSelect},\n\t\t\t\t\tMATCH (\"Post\".\"Content\") AGAINST ('{$query}') AS RelevancyScore\n\t\t\t\t\t{$baseFrom}\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tMATCH (\"ForumThread\".\"Title\", \"Post\".\"Content\") AGAINST ('{$query}' IN BOOLEAN MODE)\n\t\t\t\t\t\t{$SQL_authorClause}\n\t\t\t\t\t\tAND \"ForumPage\".\"ParentID\"='{$forumHolderID}'\n\t\t\t\t\tORDER BY {$sort}";
$limitString = " LIMIT {$offset}, {$limit};";
}
// Find out how many posts that match with no limit
$allPosts = DB::query($queryString);
// Get the 10 posts from the starting record
if ($limitString) {
$query = DB::query("\n\t\t\t\t{$queryString}\n\t\t\t\t{$limitString}\n\t\t\t");
} else {
$query = $allPosts;
}
$allPostsCount = $allPosts ? $allPosts->numRecords() : 0;
$baseClass = new Post();
$postsSet = $baseClass->buildDataObjectSet($query);
if ($postsSet) {
$postsSet->setPageLimits($offset, $limit, $allPostsCount);
}
return $postsSet ? $postsSet : new DataObjectSet();
}
/**
* Return the GlobalAnnouncements from the individual forums
*
* @return DataObjectSet
*/
function GlobalAnnouncements()
{
/*return DataObject::get(
"ForumThread",
"\"ForumThread\".\"IsGlobalSticky\" = 1 AND \"ForumPage\".\"ParentID\"={$this->ID}",
"MAX(\"PostList\".\"Created\") DESC",
"INNER JOIN \"Post\" AS \"PostList\" ON \"PostList\".\"ThreadID\" = \"ForumThread\".\"ID\"
INNER JOIN \"" . ForumHolder::baseForumTable() . "\" \"ForumPage\" ON \"ForumThread\".\"ForumID\"=\"ForumPage\".\"ID\"");
*/
//Get all the forums with global sticky threads, and then get the most recent post for each of these
$threads = DataObject::get('ForumThread', "\"ForumThread\".\"IsGlobalSticky\"=1 AND \"ForumPage\".\"ParentID\"={$this->ID}", '', "INNER JOIN \"" . ForumHolder::baseForumTable() . "\" AS \"ForumPage\" ON \"ForumThread\".\"ForumID\"=\"ForumPage\".\"ID\"");
//Now go and get the most recent post for each of these forum threads
$trimmed = new DataObjectSet();
if ($threads) {
foreach ($threads as $thread) {
if ($thread->canView()) {
$post = DataObject::get_one('Post', "\"Post\".\"ThreadID\"={$thread->ID}", "\"Created\" DESC");
$thread->Post = $post;
$trimmed->Push($thread);
}
}
}
return $trimmed;
}
/**
* Return the GlobalAnnouncements from the individual forums
*
* @return DataObjectSet
*/
function GlobalAnnouncements()
{
//dump(ForumHolder::baseForumTable());
// Get all the forums with global sticky threads
return ForumThread::get()->filter('IsGlobalSticky', 1)->innerJoin(ForumHolder::baseForumTable(), '"ForumThread"."ForumID"="ForumPage"."ID"', "ForumPage")->where('"ForumPage"."ParentID" = ' . $this->ID)->filterByCallback(function ($thread) {
if ($thread->canView()) {
$post = Post::get()->filter('ThreadID', $thread->ID)->sort('Post.Created DESC');
$thread->Post = $post;
return true;
}
});
}
