/**
* search for some keywords articles anchored to one precise section
* (and its subsections) or array of sections.
*
* This function also searches in sub-sections, with up to three levels of depth.
*
* @see search.php
*
* Modification dates are taken into account to prefer freshest information.
*
* @link http://www.artfulcode.net/articles/full-text-searching-mysql/
*
* @param mixed the id of the section or array of sections to look in
* @param string the search string
* @param float maximum score to look at
* @param int the number of items to display
* @param mixed the layout, if any
* @return NULL on error, else an ordered array of array($score, $summary)
*/
public static function &search_in_section($section_id, $pattern, $offset = 1.0, $count = 10, $layout = 'search')
{
global $context;
// sanity check
if (!($pattern = trim($pattern))) {
$output = NULL;
return $output;
}
// restrict the query to addressable content
$where = Articles::get_sql_where();
// search is restricted to one section
if (is_numeric($section_id)) {
// look for children
$anchors = Sections::get_branch_at_anchor('section:' . $section_id);
// the full set of sections searched
$where .= " AND (anchor IN ('" . join("', '", $anchors) . "'))";
} elseif (is_array($section_id)) {
$where .= " AND (anchor IN ('" . join("', '", $section_id) . "'))";
}
// anonymous surfers and subscribers will see only published articles
if (!Surfer::is_member()) {
$where .= " AND NOT ((publish_date is NULL) OR (publish_date <= '0000-00-00'))" . " AND (publish_date < '" . $context['now'] . "')";
}
// only consider live articles
$where .= " AND ((expiry_date is NULL) " . "OR (expiry_date <= '" . NULL_DATE . "') OR (expiry_date > '" . $context['now'] . "'))";
// how to compute the score for articles
$score = "(MATCH(title, source, introduction, overlay, description)" . " AGAINST('" . SQL::escape($pattern) . "' IN BOOLEAN MODE)" . "/SQRT(GREATEST(1.1, DATEDIFF(NOW(), edit_date))))";
// the list of articles
$query = "SELECT *, " . $score . " AS score FROM " . SQL::table_name('articles') . " AS articles" . " WHERE (" . $where . ") AND (" . $score . " < " . $offset . ") AND (" . $score . " > 0)" . " ORDER BY score DESC" . " LIMIT " . $count;
$output =& Articles::list_selected(SQL::query($query), $layout);
return $output;
}
/**
* thread newest comments
*
* Result of this query should be processed with a layout adapted to articles
*
* @param int the offset from the start of the list; usually, 0 or 1
* @param int the number of items to display
* @param string the list variant, if any
* @return NULL on error, else an ordered array with $url => ($prefix, $label, $suffix, $icon)
*
* @see comments/index.php
*/
public static function &list_threads_by_date_for_anchor($anchor, $offset = 0, $count = 10, $variant = 'date')
{
global $context;
// restrict the query to addressable content
$where = Articles::get_sql_where();
// provide published pages to anonymous surfers
if (!Surfer::is_logged()) {
$where .= " AND NOT ((articles.publish_date is NULL) OR (articles.publish_date <= '0000-00-00'))" . " AND (articles.publish_date < '" . $context['now'] . "')";
// logged surfers that are non-associates are restricted to their own articles, plus published articles
} elseif (!Surfer::is_empowered()) {
$where .= " AND ((articles.create_id=" . Surfer::get_id() . ") OR (NOT ((articles.publish_date is NULL) OR (articles.publish_date <= '0000-00-00'))" . " AND (articles.publish_date < '" . $context['now'] . "')))";
}
// only consider live articles for non-associates
if (!Surfer::is_empowered()) {
$where .= " AND ((articles.expiry_date is NULL) " . "OR (articles.expiry_date <= '" . NULL_DATE . "') OR (articles.expiry_date > '" . $context['now'] . "'))";
}
// if not associate, restrict to comments at public published not expired pages
if (!Surfer::is_associate()) {
$where = " AND NOT ((articles.publish_date is NULL) OR (articles.publish_date <= '0000-00-00'))" . " AND ((articles.expiry_date is NULL)" . "\tOR (articles.expiry_date <= '" . NULL_DATE . "') OR (articles.expiry_date > '" . gmstrftime('%Y-%m-%d %H:%M:%S') . "'))";
}
// avoid blank records on join
$where .= ' AND (articles.id > 0)';
// several anchors
if (is_array($anchor)) {
$items = array();
foreach ($anchor as $token) {
$items[] = "articles.anchor LIKE '" . SQL::escape($token) . "'";
}
$where_anchor = join(' OR ', $items);
} else {
$where_anchor = "articles.anchor LIKE '" . SQL::escape($anchor) . "'";
}
// the list of comments
$query = "SELECT articles.* FROM " . SQL::table_name('comments') . " AS comments" . ", " . SQL::table_name('articles') . " AS articles" . " WHERE ((comments.anchor_type LIKE 'article') AND (comments.anchor_id = articles.id))" . "\tAND (" . $where_anchor . ") AND " . $where . " GROUP BY articles.id" . " ORDER BY articles.edit_date DESC LIMIT " . $offset . ',' . $count;
// return a list of articles
$output =& Articles::list_selected(SQL::query($query), $variant);
return $output;
}
/**
* list the articles by rating sum related to a given category or to any other anchor
*
* Actually list articles by rating sum, then by date. Note that articles are never ranked into a category list.
*
* Only articles matching following criteria are returned:
* - article is visible (active='Y')
* - article is restricted (active='R'), but surfer is a logged user
* - article is restricted (active='N'), but surfer is an associate
* - article has been officially published
* - an expiry date has not been defined, or is not yet passed
*
* @param the target anchor
* @param int the offset from the start of the list; usually, 0 or 1
* @param int the number of items to display
* @param string the list variant, if any
* @return NULL on error, else an ordered array with $url => ($prefix, $label, $suffix, $icon)
*
* @see categories/print.php
* @see categories/view.php
*/
public static function &list_articles_by_rating_for_anchor($anchor, $offset = 0, $count = 10, $variant = NULL, $lang = false)
{
global $context;
// locate where we are
if (!$variant) {
$variant = $anchor;
}
// limit the scope of the request
$where = "(articles.active='Y'";
if (Surfer::is_logged()) {
$where .= " OR articles.active='R'";
}
if (Surfer::is_empowered('S')) {
$where .= " OR articles.active='N'";
}
// include managed sections
if ($my_sections = Surfer::assigned_sections()) {
$where .= " OR articles.anchor IN ('section:" . join("', 'section:", $my_sections) . "')";
}
// include managed pages for editors
if ($my_articles = Surfer::assigned_articles()) {
$where .= " OR articles.id IN (" . join(', ', $my_articles) . ")";
}
$where .= ")";
// limit the scope by language
if ($lang) {
$where .= " AND ( articles.language='" . SQL::escape($lang) . "' OR articles.language='')";
}
// see only published articles in categories
$where .= " AND NOT ((articles.publish_date is NULL) OR (articles.publish_date <= '0000-00-00'))" . " AND (articles.publish_date < '" . $context['now'] . "')";
// only consider live articles
$where .= " AND ((articles.expiry_date is NULL) " . "OR (articles.expiry_date <= '" . NULL_DATE . "') OR (articles.expiry_date > '" . $context['now'] . "'))";
// the list of articles
$query = "SELECT articles.*" . " FROM (" . SQL::table_name('members') . " AS members" . ", " . SQL::table_name('articles') . " AS articles)" . " WHERE (members.anchor LIKE '" . SQL::escape($anchor) . "')" . "\tAND (members.member_type LIKE 'article')" . "\tAND (articles.id = members.member_id)" . "\tAND " . $where . " ORDER BY rating_sum, edit_date DESC LIMIT " . $offset . ',' . $count;
// use existing listing facility
$output =& Articles::list_selected(SQL::query($query), $variant);
return $output;
}
