Comments ordered by popularity, latest, newest

[ilMagnifico]

Would be nice to be able to order comments by popularity, lasts posted or new like on Facebook and other many social network nowadays.

[ArchBlood]

@luke- maybe something like the following could be created?

Example

<?php

namespace humhub\modules\comment\filter;

class CommentFilter
{
    const FILTER_POPULARITY = 'popularity';
    const FILTER_LATEST = 'latest';
    const FILTER_NEWEST = 'newest';

    /**
     * Filter comments based on the given criteria.
     * 
     * @param array $comments Array of comments to filter
     * @param string $filter The filter criteria (popularity, latest, newest)
     * @return array The filtered comments
     */
    public function filterComments($comments, $filter)
    {
        switch ($filter) {
            case self::FILTER_POPULARITY:
                // Sort comments based on popularity
                usort($comments, function($a, $b) {
                    return $this->calculatePopularity($b) - $this->calculatePopularity($a);
                });
                break;
            case self::FILTER_LATEST:
                // Sort comments based on creation date
                usort($comments, function($a, $b) {
                    return strtotime($b->created_at) - strtotime($a->created_at);
                });
                break;
            case self::FILTER_NEWEST:
                // Sort comments based on creation date in descending order
                usort($comments, function($a, $b) {
                    return strtotime($b->created_at) - strtotime($a->created_at);
                });
                break;
            default:
                // Default to latest filtering
                usort($comments, function($a, $b) {
                    return strtotime($b->created_at) - strtotime($a->created_at);
                });
                break;
        }

        return $comments;
    }

    /**
     * Calculate popularity of a comment.
     * 
     * @param Comment $comment The comment object
     * @return int The popularity score of the comment
     */
    private function calculatePopularity($comment)
    {
        // Get the number of likes for the comment
        $likesCount = $comment->getLikes()->count();

        // Get the number of replies for the comment
        $repliesCount = $comment->getReplies()->count();

        // Calculate popularity score based on likes and replies
        $popularity = $likesCount * 2 + $repliesCount * 3;

        return $popularity;
    }
}

[luke-]

@ArchBlood Rather not, we need an approach to be able to perform the sorting at database level.

[ArchBlood]

@ArchBlood Rather not, we need an approach to be able to perform the sorting at database level.

Then how about something like the following, afterwards a migration could be created?

Example

<?php

namespace humhub\modules\comment\filter;

use humhub\modules\comment\models\Comment;
use humhub\modules\like\models\Like;
use yii\db\ActiveQuery;

class CommentFilter
{
    const FILTER_POPULARITY = 'popularity';
    const FILTER_LATEST = 'latest';
    const FILTER_NEWEST = 'newest';

    /**
     * Filter comments based on the given criteria.
     * 
     * @param array $comments to filter
     * @param string $filter criteria (popularity, latest, newest)
     * @return array filtered comments
     */
    public function filterComments($comments, $filter)
    {
        switch ($filter) {
            case self::FILTER_POPULARITY:
                return $this->filterByPopularity($comments);
            case self::FILTER_LATEST:
                return $this->filterByLatest($comments);
            case self::FILTER_NEWEST:
                return $this->filterByNewest($comments);
            default:
                return $this->filterByLatest($comments);
        }
    }

    /**
     * Filter comments based on popularity.
     * 
     * @param array $comments to filter
     * @return array filtered comments
     */
    private function filterByPopularity($comments)
    {
        // Retrieve the likes count for each comment
        $likesCountMap = $this->getLikesCountMap($comments);

        // Sort comments based on likes count
        usort($comments, function ($a, $b) use ($likesCountMap) {
            return ($likesCountMap[$b->id] ?? 0) - ($likesCountMap[$a->id] ?? 0);
        });

        return $comments;
    }

    /**
     * Get likes count map for the given comments.
     * 
     * @param array $comments Array of comments
     * @return array where keys are comment IDs and values are likes counts
     */
    private function getLikesCountMap($comments)
    {
        $likesCountMap = [];

        foreach ($comments as $comment) {
            $likesCountMap[$comment->id] = Like::find()
                ->where(['object_model' => Comment::class, 'object_id' => $comment->id])
                ->count();
        }

        return $likesCountMap;
    }

    /**
     * Filter comments based on creation date (latest).
     * 
     * @param array $comments to filter
     * @return array filtered comments
     */
    private function filterByLatest($comments)
    {
        // Sort comments based on creation date
        usort($comments, function ($a, $b) {
            return strtotime($b->created_at) - strtotime($a->created_at);
        });

        return $comments;
    }

    /**
     * Filter comments based on creation date (newest).
     * 
     * @param array $comments to filter
     * @return array filtered comments
     */
    private function filterByNewest($comments)
    {
        // Sort comments based on creation date in descending order
        usort($comments, function ($a, $b) {
            return strtotime($b->created_at) - strtotime($a->created_at);
        });

        return $comments;
    }
}

[luke-]

Sorting must always be done on the database side. A “usort” on PHP side, for example, does not scale.

[ArchBlood]

Sorting must always be done on the database side. A “usort” on PHP side, for example, does not scale.

Good point, it's not my favorite option as I hate working with queries myself but another option is as followed;

Example

<?php

namespace humhub\modules\comment\filters;

use humhub\modules\comment\models\Comment;
use humhub\modules\like\models\Like;
use yii\db\ActiveQuery;

class CommentFilter
{
    const FILTER_POPULARITY = 'popularity';
    const FILTER_LATEST = 'latest';
    const FILTER_NEWEST = 'newest';

    /**
     * Filter comments based on the given criteria.
     * 
     * @param string $filter criteria (popularity, latest, newest)
     * @return ActiveQuery filtered comments query
     */
    public function filterComments($filter)
    {
        switch ($filter) {
            case self::FILTER_POPULARITY:
                return $this->filterByPopularity();
            case self::FILTER_LATEST:
                return $this->filterByLatest();
            case self::FILTER_NEWEST:
                return $this->filterByNewest();
            default:
                return $this->filterByLatest();
        }
    }

    /**
     * Filter comments based on popularity.
     * 
     * @return ActiveQuery filtered comments query
     */
    private function filterByPopularity()
    {
        // Select comments with a subquery to calculate the popularity
        return Comment::find()
            ->alias('c')
            ->leftJoin(
                '(SELECT object_id, COUNT(*) as like_count FROM `like` WHERE object_model = :commentModel GROUP BY object_id) as l',
                'l.object_id = c.id'
            )
            ->params([':commentModel' => Comment::class])
            ->orderBy(['like_count' => SORT_DESC, 'c.created_at' => SORT_DESC]);
    }

    /**
     * Filter comments based on creation date (latest).
     * 
     * @return ActiveQuery filtered comments query
     */
    private function filterByLatest()
    {
        return Comment::find()
            ->orderBy(['created_at' => SORT_DESC]);
    }

    /**
     * Filter comments based on creation date (newest).
     * 
     * @return ActiveQuery filtered comments query
     */
    private function filterByNewest()
    {
        return Comment::find()
            ->orderBy(['created_at' => SORT_DESC]);
    }
}

Usage

$commentFilter = new CommentFilter();
$filteredCommentsQuery = $commentFilter->filterComments(CommentFilter::FILTER_POPULARITY);
$filteredComments = $filteredCommentsQuery->all();