Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

Displaying Reaction Points Total in Discussions Archive

SudoCatSudoCat Drowning under a Sea of Clients New
edited July 2014 in Vanilla 2.0 - 2.8

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 :smile:

Comments

  • hgtonighthgtonight ∞ · New Moderator

    Thanks for trying my addon!

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

    Where?

    Search first

    Check out the Documentation! We are always looking for new content and pull requests.

    Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.

  • SudoCatSudoCat Drowning under a Sea of Clients New

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

  • hgtonighthgtonight ∞ · New Moderator
    edited July 2014

    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;
    }
    

    Search first

    Check out the Documentation! We are always looking for new content and pull requests.

    Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.

  • R_JR_J Ex-Fanboy Munich Admin

    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

  • hgtonighthgtonight ∞ · New Moderator

    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.

    Search first

    Check out the Documentation! We are always looking for new content and pull requests.

    Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.

  • SudoCatSudoCat Drowning under a Sea of Clients New

    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 :smile:

  • hgtonighthgtonight ∞ · New Moderator

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

    Great find, wish I could give you five insightfuls!

    Search first

    Check out the Documentation! We are always looking for new content and pull requests.

    Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.

  • R_JR_J Ex-Fanboy Munich Admin

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

  • R_JR_J Ex-Fanboy Munich Admin

    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

  • hgtonighthgtonight ∞ · New Moderator

    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!

    Search first

    Check out the Documentation! We are always looking for new content and pull requests.

    Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.

  • SudoCatSudoCat Drowning under a Sea of Clients New
    edited July 2014

    @hgtonight‌ @R_J Thanks for all your help on this :D 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_JR_J Ex-Fanboy Munich Admin

    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.

  • SudoCatSudoCat Drowning under a Sea of Clients New

    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.

  • hgtonighthgtonight ∞ · New Moderator
    edited July 2014

    @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! :D

    Search first

    Check out the Documentation! We are always looking for new content and pull requests.

    Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.

Sign In or Register to comment.