Question Importing voting data

How-to and Troubleshooting

Last Post by Zackar 2 years ago

3 Posts

2 Users

0 Reactions

4,479 Views

RSS

Posts: 19

powcom

Topic starter

Sep 25, 2023 1:26 pm

(@powcom)

Eminent Member

Joined: 5 years ago

Hi - We’ve got nearly 1 million votes in an old wp_commentsvote table from a previous plugin we ran before we switched to wpDiscuz some years back. (I looked at this a while back, but see you've updated your table structure)

We’d like to bring them over into wpDiscuz’s wp_wc_users_voted table, so we can drop the old table and slim down our database.

The following fields seem simple enough:

wp_wc_users_voted.comment_id = wp_commentsvote.commentID

wp_wc_users_voted.vote_type = wp_commentsvote.vote

wp_wc_users_voted.date = wp_commentsvote.voteTime

The ones that are a little more complex are user_id, is_guest and post_id. Can I check I’ve got the right understanding of those?

wp_wc_users_voted.user_id = wp_commentsvote.userID - unless it’s 0, in which case, is it as simple as using wp_commentsvote.ip? (btw - the data we have in wp_commentsvote is so old, there’s no IPv6 in there)

wp_wc_users_voted.is_guest = If (wp_commentsvote.userID = 0), they’re not logged in, so wp_wc_users_voted.is_guest should equal 1?

wp_wc_users_voted.post_id = This is going to have to be looked up in the wp_comments table to get the associated comment_post_ID, based on wp_wc_users_voted.comment_id?

Thanks in advance for your help.

Topic Tags

voting

2 Replies

Posts: 19

powcom

Topic starter

Sep 25, 2023 1:41 pm

(@powcom)

Eminent Member

Joined: 5 years ago

If the above is correct, would the following work?

INSERT INTO wp_wc_users_voted (user_id, comment_id, post_id, vote_type, is_guest, date)
SELECT
-- For user_id
CASE
WHEN cv.userID = 0 THEN CAST(cv.ip AS VARCHAR(255))
ELSE CAST(cv.userID AS VARCHAR(255))
END,
-- For comment_id
cv.commentID,
-- For post_id
c.comment_post_ID,
-- For vote_type
cv.vote,
-- For is_guest
CASE
WHEN cv.userID = 0 THEN 1
ELSE 0
END,
-- For date
cv.voteTime
FROM
wp_commentsvote cv
JOIN
wp_comments c ON cv.commentID = c.comment_ID;

Posts: 67

Zackar

Support

Sep 26, 2023 7:26 am

(@zackar)

Trusted Member

Joined: 10 years ago

hi @powcom

About SQL, if all your descriptions match then I think it will work. But one note: if there is no user identifier, in its place, there should be not 0 but md5(guest_ip). In any match, I would recommend doing all these operations on a test site, if everything goes well, only then repeat it on a real site. And don’t forget after all this Regenerate Comments' Data in Dashboard->wpDiscuz->Tools->Regenerate Comments' Data