Migrating comments from K2

[isidrobaq]

Hi there!

As J3 goes EOL, many clients are emerging asking for J4 migration. I've had a few who were using K2 in their old sites, and if they were using comments, the "easiest" path for them would be using a 3rd party blog extension with integrated comments and out-of-the-box migration for them, like EasyBlog. While I think it's a good extension, I'd really like to be able to get those posts migrated to com_content, and get the comments managed by an extension like Akeeba Engage (AE), so I'm evaluating if an "easy" migration path from K2 comments to AE would be possible (basically, manually restoring a custom made CSV, or maybe using roCSVI extension with custom table import).

I've been checking K2 comments table vs AE one, and I think most fields are easy to migrate, except for the "main" one: item_id. In AE table I can see these 2 columns:

parent_id
asset_id

There's also an "asset_id" column in core's "_content" table, but I'm not sure that's the value I should use to get comments linked to their joomla articles. So at this point I have basically 2 questions:

1. Is "_content" table "asset_id" value the one I should assign to the "asset_id" column in comments I want to import?
2. In case no, is there any "easy" way (ie, using a single SQL sentence or executing a short PHP script) to get that value generated from articles?

Thanks in advance!

[nikosdion]

Is "_content" table "asset_id" value the one I should assign to the "asset_id" column in comments I want to import?

Correct!

You might be wondering “why the heck is Nicholas using the asset ID instead of the article ID?”. The short answer to that is forwards compatibility.

The longer answer is that Akeeba Engage is currently limited to core Joomla! articles. In the future, core Joomla! could very well add custom content types. If this were to happen, my reasonable assumption is that content types would be constructed out of custom fields. All Joomla! really needs to do is normalise the custom fields' data to JSON primitives, change a single field type from TEXT to JSON, and add an index to do almost all of the work needed for that feature. The other reasonable assumption is that if this were to happen, every custom content item would need to have customisable permissions i.e. it would need an #__assets table record. Therefore, it would need to have some sort of asset_id column. This is why I chose to use the asset ID instead of the article ID in Engage. By doing so, I can make very limited changes to Engage to create a comment solution for custom content types.

By the way, this was the reason why the original Joomla 4 Working Group had prescribed custom fields as a necessary feature for Joomla 4. I don't know if there's anyone left in Joomla who remembers it. Even if there is nobody left, though, custom fields are a fact and what I outlined is the reasonable way to go about creating custom types. Right now, regular core content items with custom fields and template overrides are 90% into being custom content types. The other 10% is about making custom fields indexable, searchable, and sortable (can be done with MySQL's and PostgreSQL's JSON features), and refining template overrides so that the “correct” view template would be used by default for each custom content type. Hopefully, someone, sometime, will remember or rediscover the reason custom fields exist in Joomla! in the first place and do the rest of the work necessary. I led the proverbial horses to the water back in 2015. It's up to them to drink up.

[isidrobaq]

In case anyone is interested in migrating comments from K2, here's how I did it :)

1. When migrating K2 items into Joomla com_content, create a custom field in Joomla called for example K2 ID, where you'll store K2 items' IDs.
2. Then you just need to run this SQL query:

INSERT INTO yourdb_engage_comments (asset_id, body, name, email, enabled, created, created_by)
SELECT 
    c.asset_id, 
    k2.commentText, 
    k2.userName, 
    k2.commentEmail, 
    k2.published, 
    k2.commentDate, 
    k2.userID
FROM 
    yourdb_k2_comments k2
JOIN 
    yourdb_fields_values v ON k2.itemID = v.value
JOIN 
    yourdb_content c ON v.item_id = c.id
WHERE 
    v.field_id = XX;

Just replace yourdb with your own DB's name and XX with your K2 ID field id, and you're done :)

[nikosdion]

This is awesome! Thank you :)