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