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.

[MYSQL Snippet] Update "CountComments"-value for every Discussion

oliverraduneroliverraduner Contributing to Vanilla since 2010Switzerland ✭✭
edited January 2011 in Vanilla 2.0 - 2.8
Today I imported roughly 1'100 discussions & about 17'000 comments to my current Vanilla installation.

To sweeten this up even more, I was able to discover an SQL-snippet in the web, that loops through the GDN_Discussion table & updates the "CountComments"-value (amount of Comments per Discussion) for every single Discussion.

I thought I share it here, as it might be helpful for somebody else importing Discussions & Comments to Vanilla...

Beware: this is a direct on-database command - use on your own risk!!

UPDATE GDN_Discussion SET CountComments = ( SELECT SUM(GDN_Comment.DiscussionID = GDN_Discussion.DiscussionID) FROM GDN_Comment );

Cheers

Comments

  • edited February 2011
    if you want to improve the performance on a query like that try changing it to something like this.

    update GDN_Discussion D JOIN GDN_Comment C ON D.DiscussionID = C.DiscussionID SET D.CountComments = (SELECT count(C.DiscussionID) as comment_count FROM GDN_Comment C where D.DiscussionID = C.DiscussionID )



    ---
    Also if you want to test the results first just adjust it to be like this

    select d.DiscussionID, count(*) from GDN_Discussion D , GDN_Comment C where C.DiscussionID = D.DiscussionID group by d.DiscussionID order by 1;


    :)
  • Added code tags for readability

    There was an error rendering this rich post.

Sign In or Register to comment.