Coding standards
Security in ResourceSpace
Developer reference
Database
Action functions
Admin functions
Ajax functions
Annotation functions
API functions
Collections functions
Comment functions
Config functions
CSV export functions
Dash functions
Debug functions
Encryption functions
Facial recognition functions
File functions
General functions
Language functions
Log functions
Login functions
Message functions
Migration functions
Node functions
PDF functions
Plugin functions
Render functions
Reporting functions
Request functions
Research functions
Slideshow functions
Theme permission functions
User functions
Video functions
Database functions
Metadata functions
Resource functions
Search functions
Map functions
Job functions
Tab functions
Test functions

get_comments_by_ref()

Description

Return comments for a resource or collection. There are two options for the output:
1. A flat list of comments ordered by creation date, newest first. Config $comments_flat_view = true
2. A tree view of comments, top level ordered most recent first with lower levels also most recent first while
respecting a hierarchy of nested comments. Config $comments_flat_view = false (default)
User permissions are also checked to ensure users can view comments. $comments_responses_max_level limits the
number of levels returned in the comments tree (only applies when $comments_flat_view = false).


to view comments.

Parameters

ColumnTypeDefaultDescription
$ref int Resource or collection ID.
$collection_mode bool True if the $ref provided is a collection ID. False if $ref providing resource ID (default)

Return

array Array of comments sorted per $comments_flat_view. Empty array if user doesn't have permission to

Location

include/comment_functions.php lines 517 to 560

Definition

 
function get_comments_by_ref(int $ref, bool $collection_mode = false): array
{
    if (get_resource_access($ref) === RESOURCE_ACCESS_CONFIDENTIAL) {
        return array();
    }

    global $lang, $comments_flat_view, $comments_responses_max_level;

    $sql_columns = "c.ref, c.ref_parent, c.annotation, c.hide, c.created, c.body, c.website_url, c.email, u.username, u.ref AS 'user_ref', u.profile_image, parent.created AS 'responseToDateTime',";
    $sql_columns .= " IFNULL(IFNULL(c.fullname, u.fullname), ?) AS 'name', IFNULL(IFNULL(parent.fullname, uparent.fullname), ?) AS 'responseToName'";
    $sql_columns_values = array('s', $lang['comments_anonymous-user'], 's', $lang['comments_anonymous-user']);

    $sql_from = "comment c LEFT JOIN (user u) ON (c.user_ref = u.ref) LEFT JOIN (comment parent) ON (c.ref_parent = parent.ref) LEFT JOIN (user uparent) ON (parent.user_ref = uparent.ref)";

    // first level will look for either collection or resource comments
    $sql_where = $collection_mode ? "c.collection_ref = ?" : "c.resource_ref = ?";
    $sql_values_where = array("i", $ref);

    if ($comments_flat_view) {
        return ps_query("SELECT {$sql_columns}, 0 AS 'level' FROM {$sql_from} WHERE {$sql_where} ORDER BY c.created DESC", array_merge($sql_columns_values, $sql_values_where));
    }

    $comments_cte = "WITH RECURSIVE tree AS (
        SELECT
            {$sql_columns},
            0 AS 'level',
            CAST(CONCAT(LPAD(9999999999 - UNIX_TIMESTAMP(c.created), 10, 0), '-', LPAD(c.ref, 10, 0)) AS CHAR(220)) AS `path`
        FROM {$sql_from}
        WHERE {$sql_where} AND c.ref_parent IS NULL
        UNION ALL
        SELECT
            {$sql_columns},
            tree.`level` + 1,
            CONCAT(tree.path, '/', LPAD(9999999999 - UNIX_TIMESTAMP(c.created), 10, 0), '', LPAD(c.ref, 10, 0))
        FROM {$sql_from}
        JOIN tree ON c.ref_parent = tree.ref
        WHERE tree.`level` + 1 < ?
    )

    SELECT ref, ref_parent, annotation, hide, created, body, website_url, email, username, user_ref, profile_image, responseToDateTime, `name`, responseToName, `level`
    FROM tree ORDER BY `path` ASC, ref ASC;";

    return ps_query($comments_cte, array_merge($sql_columns_values, $sql_values_where, $sql_columns_values, array('i', $comments_responses_max_level)));
}

This article was last updated 3rd March 2026 18:05 Europe/London time based on the source file dated 3rd March 2026 15:10 Europe/London time.