Question Migrate votes to wpdiscuz

How-to and Troubleshooting

Last Post by Zackar 2 years ago

3 Posts

2 Users

1 Reactions

1,746 Views

RSS

Posts: 15

sugar76

Topic starter

Sep 28, 2023 1:23 pm

(@sugar76)

Eminent Member

Joined: 2 years ago

I'd like to migrate votes from another plugin to wpdiscuz.

The old votes are stored in this table

CREATE TABLE `xre2_comment_vote` (
`comment_vote_id` bigint(20) UNSIGNED NOT NULL,
`post_id` bigint(20) UNSIGNED NOT NULL,
`comment_id` bigint(20) UNSIGNED NOT NULL,
`user` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL, -- random uuid
`vote` tinyint(1) NOT NULL -- '1' is up vote, '-1' is down vote
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Example of a row in this table:

comment_vote_id;post_id;comment_id;user;vote
1;1037;50;28928512-88f8-4ac6-830a-db94c175c2c5;1

I migrated the votes using this query:

INSERT INTO `xre2_wc_users_voted` (user_id, comment_id, vote_type, is_guest, post_id, date)
SELECT SUBSTR(user, 1, 32), comment_id, vote, '1', post_id, UNIX_TIMESTAMP() FROM xre2_comment_vote;

After performing this query the table xre2_wc_users_voted is filled with votes.

I know that the resulting user id is not "valid" but I'd assume it to be sufficient so that no votes are lost.

But for any reason the migrated votes are missing in the comments section of a post. Only the "new" votes (which have been voted by wpdiscuz) are counted.

What am I missing?

This topic was modified 2 years ago 3 times by sugar76

Topic Tags

2 Replies

Posts: 15

sugar76

Topic starter

Sep 28, 2023 7:11 pm

(@sugar76)

Eminent Member

Joined: 2 years ago

I think I found it out! I missed the entries in commentmeta table.

From my understanding meta_key wpdiscuz_votes stands for the sum of all up and down votes whereas meta_key wpdiscuz_votes_seperate contains the separate up and down votes.

The complete migration script is this:

INSERT INTO `xre2_wc_users_voted` (user_id, comment_id, vote_type, is_guest, post_id, date)
SELECT SUBSTR(user, 1, 32), comment_id, vote, '1', post_id, UNIX_TIMESTAMP() FROM xre2_comment_vote;

INSERT INTO `xre2_commentmeta` (comment_id, meta_key, meta_value)
SELECT comment_id, 'wpdiscuz_votes', SUM(vote) FROM xre2_comment_vote GROUP BY comment_id;

CREATE TEMPORARY TABLE comment_likes -- all upvotes
SELECT comment_id, SUM(vote) AS likes
FROM xre2_comment_vote
WHERE vote > 0
GROUP BY comment_id;

CREATE TEMPORARY TABLE comment_dislikes  -- all downvotes
SELECT comment_id, SUM(vote) AS dislikes
FROM xre2_comment_vote
WHERE vote < 0
GROUP BY comment_id;

CREATE TEMPORARY TABLE comment_votes -- all comments with up/down votes
SELECT DISTINCT(comment_id)
FROM xre2_comment_vote;

INSERT INTO xre2_commentmeta (comment_id, meta_key, meta_value)
SELECT comment_votes.comment_id, 'wpdiscuz_votes_seperate', CONCAT('a:2:{s:4:"like";i:', COALESCE(comment_likes.likes, 0), ';s:7:"dislike";i:', COALESCE(comment_likes.likes, 0), ';}')
FROM comment_votes
LEFT JOIN comment_likes ON comment_votes.comment_id = comment_likes.comment_id
LEFT JOIN comment_dislikes ON comment_votes.comment_id = comment_dislikes.comment_id;

I'm not quite sure what the marked as bold values a:2:{s:4:"like";i:3;s:7:"dislike";i:3;} mean?

Still it seems ok that they have the same value for each comment.