Displaying Reaction Points Total in Discussions Archive

SudoCat

Hallor,

Client wants to display points on the discussions archives page; I was just wondering if there was a relatively easy way to display the total quantity of points the article has accumulated across the comments on the discussions list. Halfway through writing this, I've finally realised that the $Discussion->Score is the total points on the start post of the discussion (Using only one test user was not making the test process easy!), so how challenging would it be to add ScoreTotal, which is updated on each reaction within the discussion? It doesn't sound too horrible challenging, but it is the end of the day and my brain is not at it's best, so I figured I'd ask the community! (And I apologise if this makes little sense/sounds like ramblings of nonsense)

Thanks in advance

hgtonight

Thanks for trying my addon!

You want the score of the discussion and any subsequent comments displayed somewhere?

Where?

SudoCat

Discussion Archive pages - so on the discussion list. The aim was to add it alongside the Views and Comments counts.

hgtonight

The SQL you are looking for would be something like this:

SELECT sum(d.Score)+sum(c.Score) AS TotalScore
FROM `gdn_discussion` d
LEFT JOIN
     (select cs.DiscussionID, sum(cs.Score) as Score
      from `gdn_comment` cs
     ) c
     ON d.DiscussionID = c.DiscussionID
WHERE d.DiscussionID = {$DiscussionID}

Ideally, you would hook into the discussion model and append this to your query before it is ran. I don't know how to subselect using the sql driver though.

Hopefully someone else can chime in as to how you could accomplish that.

Once you get the total score into the dataset, you can hook into your view and spit out the score like so:

public function CategoriesController_BeforeDiscussionContent_Handler($Sender) {
  $this->_RenderScoreBox($Sender);
}

public function DiscussionsController_BeforeDiscussionContent_Handler($Sender) {
  $this->_RenderScoreBox($Sender);
}

private function _RenderScoreBox($Sender) {
  $Discussion = GetValue('Discussion', $Sender->EventArguments);
  $String = Wrap(
    Wrap(T('Score')) .
    Gdn_Format::BigNumber($Discussion->TotalScore),
    'span',
    array('class' => 'StatsBox ScoreBox')
  );
  echo $String;
}

R_J

If I remember it right, a subselect is not possible. But you can create a view with hard coded SQL and use that later on with the query builder

hgtonight

You could hook into the model after the retrieval, loop through the discussion objects, and perform the query on each one. Not very efficient, but it can be done:

public function DiscussionModel_AfterAddColumns_Handler($Sender) {
  $Data = & $Sender->EventArguments['Data'];
  $Result = $Data->Result();

  foreach($Result as $Row) {
    $Database = Gdn::Database();
    $Px = $Database->DatabasePrefix;
    $Query = "SELECT SUM(d.Score) + SUM(c.Score) AS TotalScore "
            . "FROM {$Px}Discussion AS d "
            . "LEFT JOIN "
            . "(SELECT cs.DiscussionID, SUM(cs.Score) AS Score "
            . "FROM {$Px}Comment AS cs) AS c "
            . "ON d.DiscussionID = c.DiscussionID "
            . "WHERE d.DiscussionID = :DiscussionID";

    $RowScore = $Database->Query($Query, array(':DiscussionID' => $Row->DiscussionID))->FirstRow();
    $Row->TotalScore = (int)$RowScore->TotalScore;
  }

  $Data->ImportDataset($Result);
}

Another option would be to add a denormalized field on the discussion table that is updated whenever one of it's children has a score change. This would definitely be more scalable and could be done by hooking into the reaction model after save reaction event.

SudoCat

Thank you so much for the help guys, I'll let you know how it goes when I get back onto this. (Sorry for the delayed response!)

I think the best option would certainly be the extra table route, we really don't want to have to load every discussion on page load just to count the votes. Sometime this week my boss should be making time for one of the back end dev's to give me a helping hand on this, so we'll probably look into implementing it then - I'll be sure to post up the code if anyone else is interested

R_J

@hgtonight said:
The SQL you are looking for would be something like this:

SELECT sum(d.Score)+sum(c.Score) AS TotalScore
FROM `gdn_discussion` d
LEFT JOIN
     (select cs.DiscussionID, sum(cs.Score) as Score
      from `gdn_comment` cs
     ) c
     ON d.DiscussionID = c.DiscussionID
WHERE d.DiscussionID = {$DiscussionID}

Ideally, you would hook into the discussion model and append this to your query before it is ran. I don't know how to subselect using the sql driver though.

Hopefully someone else can chime in as to how you could accomplish that.

I found a Vanilla subselect: https://github.com/vanilla/vanilla/blob/2.1/applications/vanilla/models/class.commentmodel.php#L143-155

Its magic is in those two lines:

$Select = $Sql2->GetSelect();
$this->SQL->Join("($Select) c2", "c.CommentID = c2.CommentID");

hgtonight

I can actually just put the sub-select right into the join.

Great find, wish I could give you five insightfuls!

R_J

... but you didn't even gave me one

R_J

The catch is, that you cannot "just" join the select because it would be prefixed and so the prefix is set to "" when the join is made and afterwards it is restored to the old setting

hgtonight

Thanks to the input of @R_J‌, here is the plugin I came up with:

public function DiscussionModel_BeforeGet_Handler($Sender) {
    $Sql2 = clone $Sender->SQL;
    $Sql2->Reset();
    $Sql2->Select('cs.DiscussionID')
       ->Select('cs.Score', 'sum', 'Score')
       ->From('Comment cs');

    $Select = $Sql2->GetSelect();

    $Px = $Sender->SQL->Database->DatabasePrefix;
    $Sender->SQL->Database->DatabasePrefix = '';

    $Sender->SQL->Join("($Select) c", "d.DiscussionID = c.DiscussionID", 'left');
    $Sender->SQL->Database->DatabasePrefix = $Px;
    $Sender->SQL->Select('c.Score', 'sum', 'ChildScore');
}

public function CategoriesController_BeforeDiscussionContent_Handler($Sender) {
    $this->_RenderScoreBox($Sender);
}

public function DiscussionsController_BeforeDiscussionContent_Handler($Sender) {
    $this->_RenderScoreBox($Sender);
}

private function _RenderScoreBox($Sender) {
    $Discussion = GetValue('Discussion', $Sender->EventArguments);
    $String = Wrap(
            Wrap(T('Score')) .
            Gdn_Format::BigNumber($Discussion->ChildScore + $Discussion->Score), 'span', array('class' => 'StatsBox ScoreBox')
    );
    echo $String;
}

It should be perform better than the previous one.

I still don't think this is a very great idea since you are essentially assigning all the great commenters' content value to the initial discussion starter. That said, I hope you find it useful!

SudoCat

@hgtonight‌ @R_J Thanks for all your help on this Saved me a whole lotta headaches and file trawling
And no, to be frank I don't think it's great either, but some clients don't give a flying fig what you have to say sigh

R_J

As far as I have understood it, it will display the sum of all comments in the discussions index.
I find that far more interesting that just the "worth" of the opening discussion. An interesting Discussion doesn't need to have any Comment worth reading at all and a worthless opening post could end up in a very good discussion. So taking a look at the sum of Discussion + Comments makes far more sense to me, than only showing one aspect of it.

SudoCat

That was the thinking behind it - the client's desire was to have separate votes to reactions (And then thanks separate to the both of them as well), which really is just over complicating it all. Reactions is one of the most advanced methods of judging the overall rating of something, thanks to the ability to apply different point values to different reactions etc. resulting in a much more organic representation of a discussion or questions worth.

hgtonight

@R_J said:
As far as I have understood it, it will display the sum of all comments in the discussions index.
I find that far more interesting that just the "worth" of the opening discussion. An interesting Discussion doesn't need to have any Comment worth reading at all and a worthless opening post could end up in a very good discussion. So taking a look at the sum of Discussion + Comments makes far more sense to me, than only showing one aspect of it.

If you are looking for great content, check out the best controller, which sorts all content based on score and date.

Don't listen to this cantankerous old fart. Do what you want!