/** * 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; } }); }