Show last comment on discussion listing page

jenis

We are using the vanilla forum for building our community platform, So in our requirements, we will have a discussion listing page and if the discussion has any comment then we need to show along with it.

So is there any way using which we can introduce "lastcomment" data inside currently provided discussion list data?

We do not want to have extra loops and multiple queries to prepare this data, any way to make use of "join" in existing discussion model?

Any help will be very much appreciated.

R_J

Discussions and Comments are in two different tables. There wouldbe some kind of joint needed. There is no DiscussionModel method which fetches the discussion and additionally the most recent comment. That is a feature which is not required anywhere. Therefore you would have to do it by yourself.

I wouldn't know how to get the newest dataset from a table without a subquery. Therefore I would do 2 steps and use caching in order not to waste resources (which is your main concern, I guess)

This is only hacked down, I haven't had a look for permissions and such:

        $recentDiscussions = Gdn::cache()->get('MyRecentDiscussions');
        if ($recentDiscussions !== Gdn_Cache::CACHEOP_FAILURE) {
            return $recentDiscussions;
        }
        $discussionModel = new DiscussionModel();
        $discussions = $discussionModel->getWhere(
            [],
            'd.DateInserted',
            'desc',
            10, // limit
            false, // no offset
            false // do not expand user and category data, but that#s up to you
        )->resultArray();

        $discussionIDs = array_column($discussions, 'DiscussionID');
        $comments = Gdn::sql()
            ->select('*') // You shouldn't do that. Get only the important columns
            ->select('max(CommentID)')
            ->from('Comment')
            ->whereIn('DiscussionID', $discussionIDs)
            ->groupBy('DiscussionID')
            ->get()
            ->resultArray();

        $recentDiscussions = [];
        foreach ($discussions as $discussion) {
            $comment = false;
            foreach ($comments as $key => $value) {
                if ($value['DiscussionID'] == $discussion['DiscussionID']) {
                    $comment = $value;
                    unset($comments[$key]); // since that will only 10 comments, this might be even slower than simply looping through all 10 comments 10 times.
                    break;
                }
            }

            $recentDiscussions[] = [
                'Discussion' => $discussion,
                'Comment' => $comment
            ];
        }

        Gdn::cache()->store(
            'MyRecentDiscussions',
            $recentDiscussions,
            [Gdn_Cache::FEATURE_EXPIRY => 60] // Cache that for one minute
        );

    return $recentDiscussions;

jenis

Okay, thanks for the inputs.