function search($query, $course, &$bookids, $offset, &$countentries) { global $CFG, $USER, $DB; // Perform the search only in books fulfilling mod/book:read and (visible or moodle/course:viewhiddenactivities) if (empty($bookids)) { $bookids = book_search_get_readble_books($course); } // transform the search query into safe SQL queries $searchterms = explode(" ", $query); $parser = new search_parser(); $lexer = new search_lexer($parser); if ($lexer->parse($query)) { $parsearray = $parser->get_parsed_array(); list($messagesearch, $msparams) = search_generate_SQL($parsearray, 'bc.title', 'bc.content', null, null, null, null, null, null); } // Main query, only to allowed books and not hidden chapters. $selectsql = "SELECT DISTINCT bc.*"; $fromsql = " FROM {book_chapters} bc, {book} b"; list($insql, $inparams) = $DB->get_in_or_equal($bookids, SQL_PARAMS_NAMED); $params = array_merge(array('courseid' => $course->id), $inparams, $msparams); $wheresql = " WHERE b.course = :courseid\n AND b.id {$insql} \n AND bc.bookid = b.id \n AND bc.hidden = 0\n AND {$messagesearch} "; $ordersql = " ORDER BY bc.bookid, bc.pagenum"; // Set page limits. $limitfrom = $offset; $limitnum = 0; if ($offset >= 0) { $limitnum = BOOKMAXRESULTSPERPAGE; } $countentries = $DB->count_records_sql("select count(*) {$fromsql} {$wheresql}", $params); $allentries = $DB->get_records_sql("{$selectsql} {$fromsql} {$wheresql} {$ordersql}", $params, $limitfrom, $limitnum); return $allentries; }
/** * Returns a list of posts found using an array of search terms. * * @global object * @global object * @global object * @param array $searchterms array of search terms, e.g. word +word -word * @param int $courseid if 0, we search through the whole site * @param int $limitfrom * @param int $limitnum * @param int &$totalcount * @param string $extrasql * @return array|bool Array of posts found or false */ function forum_search_posts($searchterms, $courseid=0, $limitfrom=0, $limitnum=50, &$totalcount, $extrasql='') { global $CFG, $DB, $USER; require_once($CFG->libdir.'/searchlib.php'); $forums = forum_get_readable_forums($USER->id, $courseid); if (count($forums) == 0) { $totalcount = 0; return false; } $now = round(time(), -2); // db friendly $fullaccess = array(); $where = array(); $params = array(); foreach ($forums as $forumid => $forum) { $select = array(); if (!$forum->viewhiddentimedposts) { $select[] = "(d.userid = :userid{$forumid} OR (d.timestart < :timestart{$forumid} AND (d.timeend = 0 OR d.timeend > :timeend{$forumid})))"; $params = array_merge($params, array('userid'.$forumid=>$USER->id, 'timestart'.$forumid=>$now, 'timeend'.$forumid=>$now)); } $cm = $forum->cm; $context = $forum->context; if ($forum->type == 'qanda' && !has_capability('mod/forum:viewqandawithoutposting', $context)) { if (!empty($forum->onlydiscussions)) { list($discussionid_sql, $discussionid_params) = $DB->get_in_or_equal($forum->onlydiscussions, SQL_PARAMS_NAMED, 'qanda'.$forumid.'_'); $params = array_merge($params, $discussionid_params); $select[] = "(d.id $discussionid_sql OR p.parent = 0)"; } else { $select[] = "p.parent = 0"; } } if (!empty($forum->onlygroups)) { list($groupid_sql, $groupid_params) = $DB->get_in_or_equal($forum->onlygroups, SQL_PARAMS_NAMED, 'grps'.$forumid.'_'); $params = array_merge($params, $groupid_params); $select[] = "d.groupid $groupid_sql"; } if ($select) { $selects = implode(" AND ", $select); $where[] = "(d.forum = :forum{$forumid} AND $selects)"; $params['forum'.$forumid] = $forumid; } else { $fullaccess[] = $forumid; } } if ($fullaccess) { list($fullid_sql, $fullid_params) = $DB->get_in_or_equal($fullaccess, SQL_PARAMS_NAMED, 'fula'); $params = array_merge($params, $fullid_params); $where[] = "(d.forum $fullid_sql)"; } $selectdiscussion = "(".implode(" OR ", $where).")"; $messagesearch = ''; $searchstring = ''; // Need to concat these back together for parser to work. foreach($searchterms as $searchterm){ if ($searchstring != '') { $searchstring .= ' '; } $searchstring .= $searchterm; } // We need to allow quoted strings for the search. The quotes *should* be stripped // by the parser, but this should be examined carefully for security implications. $searchstring = str_replace("\\\"","\"",$searchstring); $parser = new search_parser(); $lexer = new search_lexer($parser); if ($lexer->parse($searchstring)) { $parsearray = $parser->get_parsed_array(); // Experimental feature under 1.8! MDL-8830 // Use alternative text searches if defined // This feature only works under mysql until properly implemented for other DBs // Requires manual creation of text index for forum_posts before enabling it: // CREATE FULLTEXT INDEX foru_post_tix ON [prefix]forum_posts (subject, message) // Experimental feature under 1.8! MDL-8830 if (!empty($CFG->forum_usetextsearches)) { list($messagesearch, $msparams) = search_generate_text_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum'); } else { list($messagesearch, $msparams) = search_generate_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum'); } $params = array_merge($params, $msparams); } $fromsql = "{forum_posts} p, {forum_discussions} d, {user} u"; $selectsql = " $messagesearch AND p.discussion = d.id AND p.userid = u.id AND $selectdiscussion $extrasql"; $countsql = "SELECT COUNT(*) FROM $fromsql WHERE $selectsql"; $searchsql = "SELECT p.*, d.forum, u.firstname, u.lastname, u.email, u.picture, u.imagealt FROM $fromsql WHERE $selectsql ORDER BY p.modified DESC"; $totalcount = $DB->count_records_sql($countsql, $params); return $DB->get_records_sql($searchsql, $params, $limitfrom, $limitnum); }
/** * Returns a list of posts found using an array of search terms. * * @global object * @global object * @global object * @param array $searchterms array of search terms, e.g. word +word -word * @param int $courseid if 0, we search through the whole site * @param int $limitfrom * @param int $limitnum * @param int &$totalcount * @param string $extrasql * @return array|bool Array of posts found or false */ function forum_search_posts($searchterms, $courseid = 0, $limitfrom = 0, $limitnum = 50, &$totalcount, $extrasql = '') { global $CFG, $DB, $USER; require_once $CFG->libdir . '/searchlib.php'; $forums = forum_get_readable_forums($USER->id, $courseid); if (count($forums) == 0) { $totalcount = 0; return false; } $now = round(time(), -2); // db friendly $fullaccess = array(); $where = array(); $params = array(); foreach ($forums as $forumid => $forum) { $select = array(); if (!$forum->viewhiddentimedposts) { $select[] = "(d.userid = :userid{$forumid} OR (d.timestart < :timestart{$forumid} AND (d.timeend = 0 OR d.timeend > :timeend{$forumid})))"; $params = array_merge($params, array('userid' . $forumid => $USER->id, 'timestart' . $forumid => $now, 'timeend' . $forumid => $now)); } $cm = $forum->cm; $context = $forum->context; if ($forum->type == 'qanda' && !has_capability('mod/forum:viewqandawithoutposting', $context)) { if (!empty($forum->onlydiscussions)) { list($discussionid_sql, $discussionid_params) = $DB->get_in_or_equal($forum->onlydiscussions, SQL_PARAMS_NAMED, 'qanda' . $forumid . '_'); $params = array_merge($params, $discussionid_params); $select[] = "(d.id {$discussionid_sql} OR p.parent = 0)"; } else { $select[] = "p.parent = 0"; } } if (!empty($forum->onlygroups)) { list($groupid_sql, $groupid_params) = $DB->get_in_or_equal($forum->onlygroups, SQL_PARAMS_NAMED, 'grps' . $forumid . '_'); $params = array_merge($params, $groupid_params); $select[] = "d.groupid {$groupid_sql}"; } if ($select) { $selects = implode(" AND ", $select); $where[] = "(d.forum = :forum{$forumid} AND {$selects})"; $params['forum' . $forumid] = $forumid; } else { $fullaccess[] = $forumid; } } if ($fullaccess) { list($fullid_sql, $fullid_params) = $DB->get_in_or_equal($fullaccess, SQL_PARAMS_NAMED, 'fula'); $params = array_merge($params, $fullid_params); $where[] = "(d.forum {$fullid_sql})"; } $selectdiscussion = "(" . implode(" OR ", $where) . ")"; $messagesearch = ''; $searchstring = ''; // Need to concat these back together for parser to work. foreach ($searchterms as $searchterm) { if ($searchstring != '') { $searchstring .= ' '; } $searchstring .= $searchterm; } // We need to allow quoted strings for the search. The quotes *should* be stripped // by the parser, but this should be examined carefully for security implications. $searchstring = str_replace("\\\"", "\"", $searchstring); $parser = new search_parser(); $lexer = new search_lexer($parser); if ($lexer->parse($searchstring)) { $parsearray = $parser->get_parsed_array(); list($messagesearch, $msparams) = search_generate_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum'); $params = array_merge($params, $msparams); } $fromsql = "{forum_posts} p,\n {forum_discussions} d,\n {user} u"; $selectsql = " {$messagesearch}\n AND p.discussion = d.id\n AND p.userid = u.id\n AND {$selectdiscussion}\n {$extrasql}"; $countsql = "SELECT COUNT(*)\n FROM {$fromsql}\n WHERE {$selectsql}"; $allnames = get_all_user_name_fields(true, 'u'); $searchsql = "SELECT p.*,\n d.forum,\n {$allnames},\n u.email,\n u.picture,\n u.imagealt\n FROM {$fromsql}\n WHERE {$selectsql}\n ORDER BY p.modified DESC"; $totalcount = $DB->count_records_sql($countsql, $params); return $DB->get_records_sql($searchsql, $params, $limitfrom, $limitnum); }
/** * Returns a list of posts found using an array of search terms. * @param $searchterms - array of search terms, e.g. word +word -word * @param $courseid - if 0, we search through the whole site * @param $page * @param $recordsperpage=50 * @param &$totalcount * @param $extrasql * @return array of posts found */ function forum_search_posts($searchterms, $courseid = 0, $limitfrom = 0, $limitnum = 50, &$totalcount, $extrasql = '') { global $CFG, $USER; require_once $CFG->libdir . '/searchlib.php'; $forums = forum_get_readable_forums($USER->id, $courseid); if (count($forums) == 0) { $totalcount = 0; return false; } $now = round(time(), -2); // db friendly $fullaccess = array(); $where = array(); foreach ($forums as $forumid => $forum) { $select = array(); if (!$forum->viewhiddentimedposts) { $select[] = "(d.userid = {$USER->id} OR (d.timestart < {$now} AND (d.timeend = 0 OR d.timeend > {$now})))"; } if ($forum->type == 'qanda') { if (!empty($forum->onlydiscussions)) { $discussionsids = implode(',', $forum->onlydiscussions); $select[] = "(d.id IN ({$discussionsids}) OR p.parent = 0)"; } else { $select[] = "p.parent = 0"; } } if (!empty($forum->onlygroups)) { $groupids = implode(',', $forum->onlygroups); $select[] = "d.groupid IN ({$groupids})"; } if ($select) { $selects = implode(" AND ", $select); $where[] = "(d.forum = {$forumid} AND {$selects})"; } else { $fullaccess[] = $forumid; } } if ($fullaccess) { $fullids = implode(',', $fullaccess); $where[] = "(d.forum IN ({$fullids}))"; } $selectdiscussion = "(" . implode(" OR ", $where) . ")"; // Some differences SQL $LIKE = sql_ilike(); $NOTLIKE = 'NOT ' . $LIKE; if ($CFG->dbfamily == 'postgres') { $REGEXP = '~*'; $NOTREGEXP = '!~*'; } else { $REGEXP = 'REGEXP'; $NOTREGEXP = 'NOT REGEXP'; } $messagesearch = ''; $searchstring = ''; // Need to concat these back together for parser to work. foreach ($searchterms as $searchterm) { if ($searchstring != '') { $searchstring .= ' '; } $searchstring .= $searchterm; } // We need to allow quoted strings for the search. The quotes *should* be stripped // by the parser, but this should be examined carefully for security implications. $searchstring = str_replace("\\\"", "\"", $searchstring); $parser = new search_parser(); $lexer = new search_lexer($parser); if ($lexer->parse($searchstring)) { $parsearray = $parser->get_parsed_array(); // Experimental feature under 1.8! MDL-8830 // Use alternative text searches if defined // This feature only works under mysql until properly implemented for other DBs // Requires manual creation of text index for forum_posts before enabling it: // CREATE FULLTEXT INDEX foru_post_tix ON [prefix]forum_posts (subject, message) // Experimental feature under 1.8! MDL-8830 if (!empty($CFG->forum_usetextsearches)) { $messagesearch = search_generate_text_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum'); } else { $messagesearch = search_generate_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum'); } } $fromsql = "{$CFG->prefix}forum_posts p,\n {$CFG->prefix}forum_discussions d,\n {$CFG->prefix}user u"; $selectsql = " {$messagesearch}\n AND p.discussion = d.id\n AND p.userid = u.id\n AND {$selectdiscussion}\n {$extrasql}"; $countsql = "SELECT COUNT(*)\n FROM {$fromsql}\n WHERE {$selectsql}"; $searchsql = "SELECT p.*,\n d.forum,\n u.firstname,\n u.lastname,\n u.email,\n u.picture,\n u.imagealt\n FROM {$fromsql}\n WHERE {$selectsql}\n ORDER BY p.modified DESC"; $totalcount = count_records_sql($countsql); return get_records_sql($searchsql, $limitfrom, $limitnum); }
function dcmetadata_search_metadata($searchterms, $courseid, $searchcourseid, $page = 0, $recordsperpage = 50, &$totalcountdc, $extrasql = '') { /// Returns a list of los metadatadc found using an array of search terms /// eg word +word -word /// global $CFG, $USER; // require_once('meta_searchlib.php'); /* if (!isteacher($courseid)) { $notteacherforum = "AND f.type <> 'teacher'"; $forummodule = get_record("modules", "name", "forum"); $onlyvisible = "AND d.forum = f.id AND f.id = cm.instance AND cm.visible = 1 AND cm.module = $forummodule->id"; $onlyvisibletable = ", {$CFG->prefix}course_modules cm, {$CFG->prefix}forum f"; if (!empty($sepgroups)) { $separategroups = SEPARATEGROUPS; $selectgroup = " AND ( NOT (cm.groupmode='$separategroups'". " OR (c.groupmode='$separategroups' AND c.groupmodeforce='1') )";//. $selectgroup .= " OR d.groupid = '-1'"; //search inside discussions for all groups too foreach ($sepgroups as $sepgroup){ $selectgroup .= " OR d.groupid = '$sepgroup->id'"; } $selectgroup .= ")"; // " OR d.groupid = '$groupid')"; $selectcourse = " AND d.course = '$courseid' AND c.id='$courseid'"; $coursetable = ", {$CFG->prefix}course c"; } else { $selectgroup = ''; $selectcourse = " AND d.course = '$courseid'"; $coursetable = ''; } } else { */ //$notteacherforum = ""; //$selectgroup = ''; //$onlyvisible = ""; //$onlyvisibletable = ""; //$coursetable = ''; //if ($courseid == SITEID && isadmin()) { if ($searchcourseid == '9999') { $mycourses = get_my_courses($USER->id); foreach ($mycourses as $mycourse) { $mcstring = $mcstring . " d.course = '{$mycourse->id}' OR"; } $mcstringfinal = meta_cut_final($mcstring); $selectcourse = " AND" . $mcstringfinal; } elseif ($searchcourseid == '0') { $selectcourse = ''; } elseif (!$searchcourseid == '9999' | !$searchcourseid == '0' | !empty($searchcourseid)) { $selectcourse = " AND d.course = '{$searchcourseid}'"; } else { if ($courseid == SITEID) { $selectcourse = ''; } else { $selectcourse = " AND d.course = '{$courseid}'"; } } $timelimit = ''; // if (!empty($CFG->forum_enabletimedposts) && (!((isadmin() and !empty($CFG->admineditalways)) || isteacher($courseid)))) { //if ((!((isadmin() and !empty($CFG->admineditalways)) || isteacher($courseid)))) { $now = time(); // $timelimit = " AND (d.userid = $USER->id OR ((d.timestart = 0 OR d.timestart <= $now) AND (d.timeend = 0 OR d.timeend > $now)))"; $timelimit = " AND (d.userid = {$USER->id} OR ((d.timemodified = 0 OR d.timemodified <= {$now}) AND (d.timemodified = 0 OR d.timemodified > {$now})))"; // $timelimit = " AND ((d.timemodified <= $now) OR (d.timemodified > $now)))"; // } $limit = sql_paging_limit($page, $recordsperpage); /// Some differences in syntax for PostgreSQL if ($CFG->dbtype == "postgres7") { $LIKE = "ILIKE"; // case-insensitive $NOTLIKE = "NOT ILIKE"; // case-insensitive $REGEXP = "~*"; $NOTREGEXP = "!~*"; } else { $LIKE = "LIKE"; $NOTLIKE = "NOT LIKE"; $REGEXP = "REGEXP"; $NOTREGEXP = "NOT REGEXP"; } $metasearch = ""; $searchstring = ""; // Need to concat these back together for parser to work. foreach ($searchterms as $searchterm) { if ($searchstring != "") { $searchstring .= " "; } $searchstring .= $searchterm; } // We need to allow quoted strings for the search. The quotes *should* be stripped // by the parser, but this should be examined carefully for security implications. $searchstring = str_replace("\\\"", "\"", $searchstring); $parser = new search_parser(); $lexer = new search_lexer($parser); if ($lexer->parse($searchstring)) { $parsearray = $parser->get_parsed_array(); //search_generate_sql($parsetree, $datafield, $metafield, $mainidfield, $useridfield,$userfirstnamefield, $userlastnamefield, $timefield, $instancefield) //$messagesearch = search_generate_SQL($parsearray,'p.message','p.subject','p.userid','u.id','u.firstname','u.lastname','p.modified', 'd.forum'); $metasearch = metasearch_generate_SQL($parsearray, 'd.title', 'd.alternative', 'd.type', 'd.format', 'd.audience', 'd.instructionalMethod', 'd.subject', 'd.description', 'd.abstract', 'd.tableOfContents', 'd.bibliographicCitation', 'd.coverage', 'd.creator', 'd.publisher', 'd.contributor', 'd.userid', 'd.timemodified', ''); } //'cm.modnameid','d.timemodified','d.course'); /* $selectsql = "{$CFG->prefix}forum_posts p, {$CFG->prefix}forum_discussions d, {$CFG->prefix}user u $onlyvisibletable $coursetable WHERE ($messagesearch) AND p.userid = u.id AND p.discussion = d.id $selectcourse $notteacherforum $onlyvisible $selectgroup $timelimit $extrasql"; $totalcount = count_records_sql("SELECT COUNT(*) FROM $selectsql"); return get_records_sql("SELECT p.*,d.forum, u.firstname,u.lastname,u.email,u.picture FROM $selectsql ORDER BY p.modified DESC $limit"); */ $selectsql = "{$CFG->prefix}metadatadc d\r\n WHERE ({$metasearch})\r\n \t\t{$selectcourse} {$extrasql}"; //AND cm.course = d.course AND d.resource = cm.modnameid $selectcourse $extrasql"; {$CFG->prefix}course_module cm $totalcountdc = count_records_sql("SELECT COUNT(*) FROM {$selectsql}"); return get_records_sql("SELECT d.* FROM\r\n {$selectsql} ORDER BY d.course, d.timemodified DESC {$limit}"); }
$sqlsubject = ''; if (!empty($search)) { $searchstring = str_replace("\\\"", "\"", $search); $parser = new search_parser(); $lexer = new search_lexer($parser); if ($lexer->parse($searchstring)) { $parsearray = $parser->get_parsed_array(); $sqlsubject = search_generate_text_SQL($parsearray, 'm.subject', '', 'm.userid', 'u.id', 'u.firstname', 'u.lastname', 'm.timecreated', ''); } } // BODY $sqlbody = ''; if (!empty($search)) { $searchstring = str_replace("\\\"", "\"", $search); $parser = new search_parser(); $lexer = new search_lexer($parser); if ($lexer->parse($searchstring)) { $parsearray = $parser->get_parsed_array(); $sqlbody = search_generate_text_SQL($parsearray, 'm.body', '', 'm.userid', 'u.id', 'u.firstname', 'u.lastname', 'm.timecreated', ''); $sqlsubjectbody = !empty($sqlsubject) ? " AND ( {$sqlsubject} OR {$sqlbody} " : ' AND ' . $sqlbody; } } else { if (!empty($sqlsubject)) { $sqlsubjectbody = ' AND ' . $sqlsubject; } else { $sqlsubjectbody = ''; } } $sqlcourse = " AND s.course = m.course AND m.course = {$courseid} AND s.course = {$courseid} "; // README: If you can search by to, this simple search mode don't get this results, you use advanced search. // Only search by: Folder and ( Subject or Body or From).
/** * Returns a list of posts found using an array of search terms. * @param $searchterms - array of search terms, e.g. word +word -word * @param $courseid - if 0, we search through the whole site * @param $page * @param $recordsperpage=50 * @param &$totalcount * @param $extrasql * @return array of posts found */ function forum_search_posts($searchterms, $courseid = 0, $limitfrom = 0, $limitnum = 50, &$totalcount, $extrasql = '') { global $CFG, $USER; require_once $CFG->libdir . '/searchlib.php'; $forums = forum_get_readable_forums($USER->id, $courseid); if (count($forums) == 0) { return false; } for ($i = 0; $i < count($forums); $i++) { if ($i == 0) { $selectdiscussion = " ((d.forum = {$forums[$i]->id}"; } else { $selectdiscussion .= " OR (d.forum = {$forums[$i]->id}"; } if (!empty($CFG->forum_enabletimedposts) && !$forums[$i]->viewhiddentimedposts) { $now = time(); $selectdiscussion .= " AND ( d.userid = {$USER->id}\n OR ((d.timestart = 0 OR d.timestart <= {$now})\n AND (d.timeend = 0 OR d.timeend > {$now})) )"; } if ($forums[$i]->type == 'qanda' && isset($forums[$i]->onlydiscussions)) { // This is a qanda forum. if (is_array($forums[$i]->onlydiscussions)) { // Show question posts as well as posts from discussions in // which the user has posted a reply. $onlydiscussions = implode(' OR d.id = ', $forums[$i]->onlydiscussions); $selectdiscussion .= " AND ((d.id = {$onlydiscussions}) OR p.parent = 0)"; } else { // Show only the question posts. $selectdiscussion .= ' AND (p.parent = 0)'; } } if (!$forums[$i]->accessallgroups) { if (!empty($forums[$i]->accessgroup)) { $groups = rtrim(implode(",", $forums[$i]->accessgroup), ","); $selectdiscussion .= " AND (d.groupid in ({$groups})"; $selectdiscussion .= ' OR d.groupid = -1)'; // -1 means open for all groups. } else { // User isn't in any group. Only search discussions that are // open to all groups. $selectdiscussion .= ' AND d.groupid = -1'; } } $selectdiscussion .= ")\n"; } $selectdiscussion .= ")"; // Some differences SQL $LIKE = sql_ilike(); $NOTLIKE = 'NOT ' . $LIKE; if ($CFG->dbfamily == 'postgres') { $REGEXP = '~*'; $NOTREGEXP = '!~*'; } else { $REGEXP = 'REGEXP'; $NOTREGEXP = 'NOT REGEXP'; } $messagesearch = ''; $searchstring = ''; // Need to concat these back together for parser to work. foreach ($searchterms as $searchterm) { if ($searchstring != '') { $searchstring .= ' '; } $searchstring .= $searchterm; } // We need to allow quoted strings for the search. The quotes *should* be stripped // by the parser, but this should be examined carefully for security implications. $searchstring = str_replace("\\\"", "\"", $searchstring); $parser = new search_parser(); $lexer = new search_lexer($parser); if ($lexer->parse($searchstring)) { $parsearray = $parser->get_parsed_array(); // Experimental feature under 1.8! MDL-8830 // Use alternative text searches if defined // This feature only works under mysql until properly implemented for other DBs // Requires manual creation of text index for forum_posts before enabling it: // CREATE FULLTEXT INDEX foru_post_tix ON [prefix]forum_posts (subject, message) // Experimental feature under 1.8! MDL-8830 if (!empty($CFG->forum_usetextsearches)) { $messagesearch = search_generate_text_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum'); } else { $messagesearch = search_generate_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum'); } } $fromsql = "{$CFG->prefix}forum_posts p,\n {$CFG->prefix}forum_discussions d,\n {$CFG->prefix}user u"; $selectsql = " {$messagesearch}\n AND p.discussion = d.id\n AND p.userid = u.id\n AND {$selectdiscussion}\n {$extrasql}"; $countsql = "SELECT COUNT(*)\n FROM {$fromsql}\n WHERE {$selectsql}"; $searchsql = "SELECT p.*,\n d.forum,\n u.firstname,\n u.lastname,\n u.email,\n u.picture,\n u.imagealt\n FROM {$fromsql}\n WHERE {$selectsql}\n ORDER BY p.modified DESC"; $totalcount = count_records_sql($countsql); return get_records_sql($searchsql, $limitfrom, $limitnum); }
/** * Returns a list of posts found using an array of search terms. * * @global object * @global object * @global object * @param array $searchterms array of search terms, e.g. word +word -word * @param int $courseid if 0, we search through the whole site * @param int $limitfrom * @param int $limitnum * @param int &$totalcount * @param string $extrasql * @return array|bool Array of posts found or false */ function hsuforum_search_posts($searchterms, $courseid = 0, $limitfrom = 0, $limitnum = 50, &$totalcount, $extrasql = '') { global $CFG, $DB, $USER; require_once $CFG->libdir . '/searchlib.php'; $forums = hsuforum_get_readable_forums($USER->id, $courseid); if (count($forums) == 0) { $totalcount = 0; return false; } $now = round(time(), -2); // db friendly $fullaccess = array(); $where = array(); $params = array('privatereply1' => $USER->id, 'privatereply2' => $USER->id); foreach ($forums as $forumid => $forum) { $select = array(); if (!$forum->viewhiddentimedposts) { $select[] = "(d.userid = :userid{$forumid} OR (d.timestart < :timestart{$forumid} AND (d.timeend = 0 OR d.timeend > :timeend{$forumid})))"; $params = array_merge($params, array('userid' . $forumid => $USER->id, 'timestart' . $forumid => $now, 'timeend' . $forumid => $now)); } if ($forum->type == 'qanda' && !has_capability('mod/hsuforum:viewqandawithoutposting', $forum->context)) { if (!empty($forum->onlydiscussions)) { list($discussionid_sql, $discussionid_params) = $DB->get_in_or_equal($forum->onlydiscussions, SQL_PARAMS_NAMED, 'qanda' . $forumid . '_'); $params = array_merge($params, $discussionid_params); $select[] = "(d.id {$discussionid_sql} OR p.parent = 0)"; } else { $select[] = "p.parent = 0"; } } if (!empty($forum->onlygroups)) { list($groupid_sql, $groupid_params) = $DB->get_in_or_equal($forum->onlygroups, SQL_PARAMS_NAMED, 'grps' . $forumid . '_'); $params = array_merge($params, $groupid_params); $select[] = "d.groupid {$groupid_sql}"; } if ($select) { $selects = implode(" AND ", $select); $where[] = "(d.forum = :forum{$forumid} AND {$selects})"; $params['forum' . $forumid] = $forumid; } else { $fullaccess[] = $forumid; } } if ($fullaccess) { list($fullid_sql, $fullid_params) = $DB->get_in_or_equal($fullaccess, SQL_PARAMS_NAMED, 'fula'); $params = array_merge($params, $fullid_params); $where[] = "(d.forum {$fullid_sql})"; } $selectdiscussion = "(" . implode(" OR ", $where) . ")"; $messagesearch = ''; $searchstring = ''; // Need to concat these back together for parser to work. foreach ($searchterms as $searchterm) { if ($searchstring != '') { $searchstring .= ' '; } $searchstring .= $searchterm; } // We need to allow quoted strings for the search. The quotes *should* be stripped // by the parser, but this should be examined carefully for security implications. $searchstring = str_replace("\\\"", "\"", $searchstring); $parser = new search_parser(); $lexer = new search_lexer($parser); if ($lexer->parse($searchstring)) { $parsearray = $parser->get_parsed_array(); // Experimental feature under 1.8! MDL-8830 // Use alternative text searches if defined // This feature only works under mysql until properly implemented for other DBs // Requires manual creation of text index for hsuforum_posts before enabling it: // CREATE FULLTEXT INDEX foru_post_tix ON [prefix]hsuforum_posts (subject, message) // Experimental feature under 1.8! MDL-8830 $usetextsearches = get_config('hsuforum', 'usetextsearches'); if (!empty($usetextsearches)) { list($messagesearch, $msparams) = search_generate_text_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum'); } else { list($messagesearch, $msparams) = search_generate_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.forum'); } $params = array_merge($params, $msparams); } $fromsql = "{hsuforum_posts} p,\n {hsuforum_discussions} d JOIN {hsuforum} f ON f.id = d.forum,\n {user} u"; foreach ($parsearray as $item) { if ($item->getType() == TOKEN_USER || $item->getType() == TOKEN_USERID) { // Additional user SQL for anonymous posts. $extrasql .= " AND ((f.anonymous != 1 OR p.userid = :currentuserid) OR p.reveal = 1) "; $params['currentuserid'] = $USER->id; break; } } $selectsql = "(p.privatereply = 0\n OR p.privatereply = :privatereply1\n OR p.userid = :privatereply2\n )\n AND {$messagesearch}\n AND p.discussion = d.id\n AND p.userid = u.id\n AND {$selectdiscussion}\n {$extrasql}"; $countsql = "SELECT COUNT(*)\n FROM {$fromsql}\n WHERE {$selectsql}"; $allnames = get_all_user_name_fields(true, 'u'); $searchsql = "SELECT p.*,\n d.forum,\n {$allnames},\n u.email,\n u.picture,\n u.imagealt,\n u.email\n FROM {$fromsql}\n WHERE {$selectsql}\n ORDER BY p.modified DESC"; $totalcount = $DB->count_records_sql($countsql, $params); return $DB->get_records_sql($searchsql, $params, $limitfrom, $limitnum); }
function mediagallery_search_items($searchterms, $courses, $limitfrom = 0, $limitnum = 50, $extrasql = '') { global $CFG, $DB, $USER; require_once $CFG->libdir . '/searchlib.php'; $collections = mediagallery_get_readable_collections($courses); if (count($collections) == 0) { return array(false, 0); } $fullaccess = array(); $where = array(); $params = array(); foreach ($collections as $collectionid => $collection) { $select = array(); $cm = $collection->cm; $context = $collection->context; if (!empty($collection->onlygroups)) { list($groupidsql, $groupidparams) = $DB->get_in_or_equal($collection->onlygroups, SQL_PARAMS_NAMED, 'grps' . $collectionid . '_'); $params = array_merge($params, $groupidparams); $select[] = "g.groupid {$groupidsql}"; } if ($select) { $selects = implode(" AND ", $select); $where[] = "(g.instanceid = :mediagallery{$collectionid} AND {$selects})"; $params['mediagallery' . $collectionid] = $collectionid; } else { $fullaccess[] = $collectionid; } } if ($fullaccess) { list($fullidsql, $fullidparams) = $DB->get_in_or_equal($fullaccess, SQL_PARAMS_NAMED, 'fula'); $params = array_merge($params, $fullidparams); $where[] = "(g.instanceid {$fullidsql})"; } $selectgallery = "(" . implode(" OR ", $where) . ")"; $messagesearch = ''; $searchstring = ''; // Need to concat these back together for parser to work. foreach ($searchterms as $searchterm) { if ($searchstring != '') { $searchstring .= ' '; } $searchstring .= $searchterm; } // We need to allow quoted strings for the search. The quotes *should* be stripped // by the parser, but this should be examined carefully for security implications. $searchstring = str_replace("\\\"", "\"", $searchstring); $parser = new search_parser(); $lexer = new search_lexer($parser); if ($lexer->parse($searchstring)) { if ($parsearray = $parser->get_parsed_array()) { list($messagesearch, $msparams) = mediagallery_generate_search_sql($parsearray); $params = array_merge($params, $msparams); } } $fromsql = "{mediagallery_item} i,\n {mediagallery_gallery} g,\n {user} u"; $selectsql = " {$messagesearch}\n AND i.galleryid = g.id\n AND g.userid = u.id\n AND {$selectgallery}\n {$extrasql}"; $countsql = "SELECT COUNT(*)\n FROM {$fromsql}\n WHERE {$selectsql}"; $searchsql = "SELECT i.*, g.name as galleryname, g.instanceid, u.firstname, u.lastname, u.email, u.picture, u.imagealt\n FROM {$fromsql}\n WHERE {$selectsql}\n ORDER BY i.id DESC"; $totalcount = $DB->count_records_sql($countsql, $params); $records = $DB->get_records_sql($searchsql, $params, $limitfrom, $limitnum); return array($records, $totalcount); }
function metadatadc_search_metadatadc($searchterms, $courseid, $page = 0, $recordsperpage = 50, &$totalcount, $sepgroups = 0, $extrasql = '') { /// Returns a list of posts found using an array of search terms /// eg word +word -word /// global $CFG, $USER; require_once $CFG->libdir . '/searchlib.php'; /* if (!isteacher($courseid)) { $notteacherforum = "AND f.type <> 'teacher'"; $forummodule = get_record("modules", "name", "forum"); $onlyvisible = "AND d.forum = f.id AND f.id = cm.instance AND cm.visible = 1 AND cm.module = $forummodule->id"; $onlyvisibletable = ", {$CFG->prefix}course_modules cm, {$CFG->prefix}forum f"; if (!empty($sepgroups)) { $separategroups = SEPARATEGROUPS; $selectgroup = " AND ( NOT (cm.groupmode='$separategroups'". " OR (c.groupmode='$separategroups' AND c.groupmodeforce='1') )";//. $selectgroup .= " OR d.groupid = '-1'"; //search inside discussions for all groups too foreach ($sepgroups as $sepgroup){ $selectgroup .= " OR d.groupid = '$sepgroup->id'"; } $selectgroup .= ")"; // " OR d.groupid = '$groupid')"; $selectcourse = " AND d.course = '$courseid' AND c.id='$courseid'"; $coursetable = ", {$CFG->prefix}course c"; } else { $selectgroup = ''; $selectcourse = " AND d.course = '$courseid'"; $coursetable = ''; } } else { $notteacherforum = ""; $selectgroup = ''; $onlyvisible = ""; $onlyvisibletable = ""; $coursetable = ''; if ($courseid == SITEID && isadmin()) { $selectcourse = ''; } else { $selectcourse = " AND d.course = '$courseid'"; } } $timelimit = ''; if (!empty($CFG->forum_enabletimedposts) && (!((isadmin() and !empty($CFG->admineditalways)) || isteacher($courseid)))) { $now = time(); $timelimit = " AND (d.userid = $USER->id OR ((d.timestart = 0 OR d.timestart <= $now) AND (d.timeend = 0 OR d.timeend > $now)))"; } */ $limit = sql_paging_limit($page, $recordsperpage); /// Some differences in syntax for PostgreSQL if ($CFG->dbtype == "postgres7") { $LIKE = "ILIKE"; // case-insensitive $NOTLIKE = "NOT ILIKE"; // case-insensitive $REGEXP = "~*"; $NOTREGEXP = "!~*"; } else { $LIKE = "LIKE"; $NOTLIKE = "NOT LIKE"; $REGEXP = "REGEXP"; $NOTREGEXP = "NOT REGEXP"; } $messagesearch = ""; $searchstring = ""; // Need to concat these back together for parser to work. foreach ($searchterms as $searchterm) { if ($searchstring != "") { $searchstring .= " "; } $searchstring .= $searchterm; } // We need to allow quoted strings for the search. The quotes *should* be stripped // by the parser, but this should be examined carefully for security implications. $searchstring = str_replace("\\\"", "\"", $searchstring); $parser = new search_parser(); $lexer = new search_lexer($parser); if ($lexer->parse($searchstring)) { $parsearray = $parser->get_parsed_array(); $messagesearch = search_generate_SQL($parsearray, 'p.message', 'p.subject', 'p.userid', 'u.id', 'u.firstname', 'u.lastname', 'p.modified', 'd.metadatadc'); } $selectsql = "{$CFG->prefix}metadatadc d,\r\n {$CFG->prefix}user u {$onlyvisibletable} {$coursetable}\r\n WHERE ({$messagesearch})\r\n AND p.userid = u.id\r\n AND p.discussion = d.id {$selectcourse} {$notteacherforum} {$onlyvisible} {$selectgroup} {$timelimit} {$extrasql}"; $totalcount = count_records_sql("SELECT COUNT(*) FROM {$selectsql}"); return get_records_sql("SELECT p.*,d.forum, u.firstname,u.lastname,u.email,u.picture FROM\r\n {$selectsql} ORDER BY p.modified DESC {$limit}"); }