XF 1.5 DB-Tech Likes

[thenashy]

I found a thread on here regarding bringing across out DB-Tech likes using the following:

INSERT INTO broncos_xenforo.`xf_liked_content` (content_type, content_id, like_user_id, like_date, content_user_id)
    SELECT 'post', dbt.contentid, dbt.userid, dbt.dateline, post.userid
        FROM broncos_bhqvb.`dbtech_thanks_entry` AS dbt
        LEFT JOIN broncos_bhqvb.`post` AS post ON (post.postid = dbt.contentid)
ON DUPLICATE KEY UPDATE
        content_id = VALUES(content_id);

There is also a likes rebuild file which I have run.

I am seeing the posts state the likes are there, but the users aren't being credited with it. My account for example shows 0 under my user details, but there are posts which are liked.

Any ideas?

 

[Jake Bunce]

You are referring to this thread:

https://xenforo.com/community/threads/manually-import-dbtech-thanks.93417/

The script does rebuild user totals.

I am guessing your source and destination ids are not aligned. Try using this alternate query to insert the likes:

INSERT INTO XFdbname.`xf_liked_content` (content_type, content_id, like_user_id, like_date, content_user_id)
	SELECT 'post', xilp.new_id, xilu2.new_id, dbt.dateline, xilu.new_id
        FROM vBdbname.`dbtech_thanks_entry` AS dbt
        INNER JOIN vBdbname.`post` AS post ON (post.postid = dbt.contentid)
        INNER JOIN XFdbname.`xf_import_log` AS xilu ON (xilu.content_type = 'user' AND xilu.old_id = post.userid)
        INNER JOIN XFdbname.`xf_import_log` AS xilp ON (xilp.content_type = 'post' AND xilp.old_id = post.postid)
        INNER JOIN XFdbname.`xf_import_log` AS xilu2 ON (xilu2.content_type = 'user' AND xilu2.old_id = dbt.userid)
ON DUPLICATE KEY UPDATE
        content_id = VALUES(content_id);

This will fully map all ids to ensure they are correct. Note that your populated import log table might not be xf_import_log. The other common name is archived_import_log. So you might need to replace the 3 instances of xf_import_log with archived_import_log.

 

[thenashy]

You are referring to this thread:

https://xenforo.com/community/threads/manually-import-dbtech-thanks.93417/

The script does rebuild user totals.

I am guessing your source and destination ids are not aligned. Try using this alternate query to insert the likes:

INSERT INTO XFdbname.`xf_liked_content` (content_type, content_id, like_user_id, like_date, content_user_id)
    SELECT 'post', xilp.new_id, xilu2.new_id, dbt.dateline, xilu.new_id
        FROM vBdbname.`dbtech_thanks_entry` AS dbt
        INNER JOIN vBdbname.`post` AS post ON (post.postid = dbt.contentid)
        INNER JOIN XFdbname.`xf_import_log` AS xilu ON (xilu.content_type = 'user' AND xilu.old_id = post.userid)
        INNER JOIN XFdbname.`xf_import_log` AS xilp ON (xilp.content_type = 'post' AND xilp.old_id = post.postid)
        INNER JOIN XFdbname.`xf_import_log` AS xilu2 ON (xilu2.content_type = 'user' AND xilu2.old_id = dbt.userid)
ON DUPLICATE KEY UPDATE
        content_id = VALUES(content_id);

This will fully map all ids to ensure they are correct. Note that your populated import log table might not be xf_import_log. The other common name is archived_import_log. So you might need to replace the 3 instances of xf_import_log with archived_import_log.

Here's the query I used. The result was nothing happening. Now, I'm no expert in SQL, so I don't know.

INSERT INTO broncos_xenforo.`xf_liked_content` (content_type, content_id, like_user_id, like_date, content_user_id)
    SELECT 'post', xilp.new_id, xilu2.new_id, dbt.dateline, xilu.new_id
        FROM broncos_bhqvb.`vb_dbtech_thanks_entry` AS dbt
        INNER JOIN broncos_bhqvb.`post` AS post ON (post.postid = dbt.contentid)
        INNER JOIN broncos_xenforo.`archived_import_log` AS xilu ON (xilu.content_type = 'user' AND xilu.old_id = post.userid)
        INNER JOIN broncos_xenforo.`archived_import_log` AS xilp ON (xilp.content_type = 'post' AND xilp.old_id = post.postid)
        INNER JOIN broncos_xenforo.`archived_import_log` AS xilu2 ON (xilu2.content_type = 'user' AND xilu2.old_id = dbt.userid)
ON DUPLICATE KEY UPDATE
        content_id = VALUES(content_id);

This resulted in 0 changes apparently.

 

[Jake Bunce]

If you send me a PM with access to both databases then I can examine them and see what's going on.

 

[thenashy]

This is the code that we managed to get to work. I haven't rebuilt yet, am running that now.

Just an FYI for anyone else reading, when updating, because of the size of our DB, we were finding the browser process was failing. So it may be required to run the update script via CLI.

Open Putty > Go to the root folder with the rebuild (../home/user/public_html/xen for me) > type 'screen' > type 'php rebuildlikes.php' -- Once complete it will show the directory you're in again.

INSERT INTO XFDBNAME.`xf_liked_content` (
content_type,
content_id,
like_user_id,
like_date,
content_user_id
)
SELECT  'post', dbt.contentid, dbt.userid, dbt.dateline, dbt.receiveduserid
FROM VBDBNAME.`vb_dbtech_thanks_entry` AS dbt
LEFT JOIN VBDBNAME.`post` AS post ON ( post.postid = dbt.contentid ) ON DUPLICATE
KEY UPDATE content_id = VALUES (
content_id
), content_user_id =
VALUES (
content_user_id
);

 

[thenashy]

With the original two queries given, my mate who works in SQL doesn't think there was anything telling it to grab the vb table receiveduserid. So those entries were all blank. Obviously we already had the rest of the data, hence the need for the overwrites on duplicate.

This might be something to consider adding to the importer, as I doubt you'll find too many VB forums without DB-Tech Thanks and Likes running. Either way, it turned out to be a pretty painless experience. Thanks @Jake Bunce for your help too!

 

[Jake Bunce]

Hmm. receiveduserid wasn't one of the columns when I wrote this query. That is why I had to join post.

 

[thenashy]

It could be the fact we have pro, or I haven't updated it in years!

Thanks for your help