public function get_replies($recursive = true)
{
global $DB;
$out = array();
// replies are always ordered by ascending date creation
$DB->query('
SELECT nvc.*, nvwu.username, nvwu.avatar
FROM nv_comments nvc
LEFT OUTER JOIN nv_webusers nvwu
ON nvwu.id = nvc.user
WHERE nvc.reply_to = ' . $this->id . ' AND
nvc.website = ' . $this->website . ' AND
nvc.status = 0
ORDER BY nvc.date_created ASC');
$rs = $DB->result();
if ($recursive) {
for ($r = 0; $r < count($rs); $r++) {
$c = new comment();
$c->load_from_resultset(array($rs[$r]));
$replies = $c->get_replies();
$out[] = $rs[$r];
if (!empty($replies)) {
foreach ($replies as $reply) {
$out[] = $reply;
}
}
}
} else {
$out = $rs;
}
return $out;
}
function nvweb_comments_list($offset = 0, $limit = NULL, $permission = NULL, $order = 'oldest')
{
global $DB;
global $website;
global $current;
$limit = value_or_default($limit, 2147483647);
if ($order == 'newest' || $order == 'hierarchy_newest') {
$orderby = "nvc.date_created DESC";
} else {
$orderby = "nvc.date_created ASC";
}
$element = $current['object'];
if ($current['type'] == 'structure') {
if (empty($current['structure_elements'])) {
$current['structure_elements'] = $element->elements();
}
$element = $current['structure_elements'][0];
} else {
if ($current['type'] == 'item') {
$element = new item();
$element->load($current['id']);
}
}
if (strpos($order, 'hierarchy') !== false) {
// list comments keeping hierarchy
// MySQL (still) does not have recursive queries, meanwhile we apply the following procedure:
// find all comments of 0-depth (root level) and calculate if they have any reply
// then, in PHP, parse the results and load (recursively) all replies and subreplies
// in the result array, INSERT the additional results in the position where they must be respecting the order requested (oldest/newest)
// note 1: this procedure allows optimization, for now we've made it work
// note 2: the only drawback is that offset/limit it's only taken into account for the root level comments, so the
// number of results is variable on each request; we found that an acceptable drawback
$DB->query('
SELECT SQL_CALC_FOUND_ROWS nvc.*, nvwu.username, nvwu.avatar,
(SELECT COUNT(nvcr.id)
FROM nv_comments nvcr
WHERE nvcr.reply_to = nvc.id
AND nvcr.status = 0
) AS replies
FROM nv_comments nvc
LEFT OUTER JOIN nv_webusers nvwu
ON nvwu.id = nvc.user
WHERE nvc.website = ' . protect($website->id) . '
AND nvc.item = ' . protect($element->id) . '
AND nvc.status = 0
AND nvc.reply_to = 0
ORDER BY ' . $orderby . '
LIMIT ' . $limit . '
OFFSET ' . $offset);
$rs = $DB->result();
$out = array();
for ($r = 0; $r < count($rs); $r++) {
$rows_to_add = array();
if ($rs[$r]->replies > 0) {
$c = new comment();
$c->load_from_resultset(array($rs[$r]));
$rows_to_add = $c->get_replies();
}
$out[] = $rs[$r];
if (!empty($rows_to_add)) {
foreach ($rows_to_add as $rta) {
$out[] = $rta;
}
}
}
$rs = $out;
$total = count($rs);
} else {
$DB->query('
SELECT SQL_CALC_FOUND_ROWS nvc.*, nvwu.username, nvwu.avatar
FROM nv_comments nvc
LEFT OUTER JOIN nv_webusers nvwu
ON nvwu.id = nvc.user
WHERE nvc.website = ' . protect($website->id) . '
AND nvc.item = ' . protect($element->id) . '
AND nvc.status = 0
ORDER BY ' . $orderby . '
LIMIT ' . $limit . '
OFFSET ' . $offset);
$rs = $DB->result();
$total = $DB->foundRows();
}
return array($rs, $total);
}
