[Resolved] How to import data from a database with M-N relationships?

[Author]

Posts

[Martin Sauter]

I need to migrate data from a former database with many-to-many relationships to WordPress/Toolset. I'm struggling with the data import.

To simplify things, let's assume the data has the following structure (SEE ATTACHED DIAGRAM):

Basically, we are managing PERSONS and EVENTS. They are stored in two separate CSV files. Both files use IDs as primary keys, since neither person names nor event names are unique.

We are also managing REGISTRATIONS (which PERSON is registered for which EVENT). This is the third CSV file and represents the many-to-many relationship, since a person can attend multiple events, and an event can have multiple participants as well. We also have the date of each registration in this file.

In WordPress/Toolset I have created two custom post types PERSONS and EVENTS with the necessary custom fields. I have also defined a many-to-many relationship REGISTRATIONS, including a custom field for the registration date, which resulted in an intermediary post type REGISTRATIONS.

For the import of the CSV files, I am using WP All Import Pro with the Toolset Types Add-On.

I am facing two challenges:

[1] As mentioned above, neither person names nor event names are unique, so I cannot use them as an identifier. I need to work with the existing IDs. According to your documentation, this should be possible: «Instead of using the title of the post you can also use the GUID. This is helpful for the case you do not have unique titles.» But I cannot find any information on how to do this.

[2] Your documentation describes how to import the *reference* to an intermediary post type. But how do I import *data* for the custom fields belonging to an intermediary post type (the registration date in our example)?

Thanks for your support.

[Beda]

I think there is a confusion about the features of each of the methods described in the DOC.

Here is (almost) everything gathered:
https://toolset.com/documentation/user-guides/export-import/
As seen, we can import with CSV Files, using WP All Import and the AddOn, or CSV Importer Plugin, or Ultimate CSV Importer Plugin

None of those methods mentions that a GUID can be used, they indeed mention that title or post ID could be used:
https://toolset.com/documentation/user-guides/export-import/how-to-import-content-into-wordpress-using-csv/how-to-import-data-from-a-csv-file-using-the-wp-all-import-toolset-types-add-on-plugin/
https://toolset.com/documentation/user-guides/export-import/how-to-import-content-into-wordpress-using-csv/import-content-csv-importer-plugin/
https://toolset.com/documentation/user-guides/export-import/how-to-import-content-into-wordpress-using-csv/import-csv-ultimate-csv-importer-plugin/
https://toolset.com/documentation/user-guides/export-import/how-to-import-content-into-wordpress-using-csv/import-csv-ultimate-csv-importer-plugin/how-to-import-posts-from-csv-files-and-maintain-relationships-when-using-the-csv-importer-plugin/

Now, where the GUID is mentioned is https://toolset.com/documentation/user-guides/export-import/importing-content-from-csv-with-post-relationships/#how-toolset-associations-can-be-added-to-a-csv

If you refer to that workflow you technically do not need the WP All Import Pro with AddOn, you can proceed with any CSV Import mechanism.

You would ensure to have:

1. at least 2 existing post types, in a relationship (in the "empty" website where you want to import to)
1a. If Intermediary are required this should as well be setup already.
2. You have a CSV with all the information as required for all posts import (as shortly introduced in the doc https://toolset.com/documentation/user-guides/export-import/importing-content-from-csv-with-post-relationships/#how-csv-importers-work). Child, Parent and Intermediary posts are all posts.
3. You now also want to add information about what post (which you will import) is related to which another post of the other kind, (which can be imported in the same file)

You can do so by extending the CSV file by the column with heading _toolset_associations_%relationship_slug%, where %relationship_slug% needs to be replaced by the exact slug of the relationship. It's like adding a new Custom Field to the CSV.

Now you can add the parent and intermediary Posts as the value of the new column you created (as in a postmeta, in fact).
You can see this explained here https://toolset.com/documentation/user-guides/export-import/importing-content-from-csv-with-post-relationships/#how-toolset-associations-can-be-added-to-a-csv

In these values you can, instead of using the title of the post, you can fallback to the GUID. I think this is what you referred to, earlier.
Basically, instead of {!{parent_title}!} + {!{intermediary_title}!} you'd enter {!{GUID}!} + {!{GUID}!}, or in a more realistic example, :

{!{<em><u>hidden link</u></em>}!}, {!{<em><u>hidden link</u></em>}!}

(where PARENT and INTERMEDIARY correspond to the respective post types, of course)
Pay attention to the syntax, which uses + to add Intermediary posts

There is as well an example of this syntax in the technical appendix here https://toolset.com/documentation/user-guides/export-import/importing-content-from-csv-with-post-relationships/#toolset_export_associations_of_child-child_id
You can see the syntax in the Example Output

Please let me know if this helps to proceed with Step 1

Related to Step 2 which you mention, intermediary posts are basically "just posts"
If you import the Parent Posts and want to connect child posts to them thru intermediary - then those must as well be imported (as other posts). They would obey the same process, meaning if they have fields you'd add them to the CSV (for those posts).

Please let me know if this clears things up.

[Martin Sauter]

Hi Beda

Thanks for your answer.

I realize that I have mixed up Post ID and GUID – apologies for that. Forget about the GUID.

I read all the linked documents before, but they did not answer my question. Maybe I have to rephrase what I am looking for.

If you take the example from above (PERSONS, EVENTS, REGISTRATIONS in three separate CSV files): How can I bring them into WordPress under the following conditions:
1. relationships must use the ID since the names are not unique
2. since we are talking of thousands of records, we need a procedure which does not require manual reformatting of the data

I understand that Example 3 on the page https://toolset.com/documentation/user-guides/export-import/how-to-import-content-into-wordpress-using-csv/how-to-import-data-from-a-csv-file-using-the-wp-all-import-toolset-types-add-on-plugin/post-relationships/ is similar to our case. But this example again uses names instead of IDs, and it would also mean that I had to integrate all data from registrations.csv into events.csv, which looks like a manual procedure to me.

Generally speaking I am looking for a solution that allows me to import our existing data in three separate steps (one CSV file at a time, since intermediate posts are just posts as well) as I would in any other database. Since I think this is a very common use case I hope there is a way to solve this.

[Beda]

I'm consulting with Agnes, she wrote one of the related DOCs back in time and is more expert than me on this area, eventually, but I am not sure there is a way to achieve this the way you outline.

I will feedback as soon as I know better.

[Beda]

So, this is as I suspected.

1. With the WP All Import Pro Many-to-many relationships that use the intermediary post type are not supported.
If you need this kind of support please mention it here:
https://toolset.com/home/contact-us/suggest-a-new-feature-for-toolset/

2. Hence, you'd need to use the suggested approach as above elaborated in my previous replies and that allows to use the name OR the GUID in that syntax you need to use to write the connection between the posts (remember, this is like this {!{hidden link}!}, {!{hidden link}!})
But, not the ID.

There is no way, it seems, to add an ID to that syntax, and it's the only way for you to import since you have intermediary posts.
I am clarifying one last thing with the developer, which is if it'd take a lot of effort to also support the ID, which if known, really would make a lot of sense to be used there (and we do know the IDs because we could import the posts in an earlier import or just earlier up the CSV.)

[Beda]

I have discussed this as well with the developers and the issue persists.

The problem with the ID is that in WordPress database tables, it's an AUTO_INCREMENT column, which means that whenever you import a post, its ID changes. Always. We have no control over the value, and even if we try to set the IDs manually on import, there is no guarantee that the ID of the imported post isn't taken already.

Theoretically speaking, this could be solved by some sort of a temporary mapping of the old (import) IDs and new IDs and using the import IDs when importing associations between posts.
But it would be a lot of hassle with a heightened risk of creating some data integrity issues.

This and other reasons are why Toolset relies on the GUID instead, which is stable unless some third-party software explicitly changes with it. The alternative of using the post name is to make things "easier", although there's also a certain risk of ambiguity, which you already noticed in your workflow.

What you could do for now is suggest such a feature to https://toolset.com/home/contact-us/suggest-a-new-feature-for-toolset/, however that will not help you immediately.

As for now, if your CSV uses the ID I'd suggest running those thru a small program, or eventually, the app with which you created them allows you editing the CSV values "in bulk", so too, for example, generate a GUID for each of the relatable posts and update the child posts entries with that data generated.

Your requirements are to have a name or GUID of the intermediary and parent posts.
Then you can update your CSV to match this requirement: https://toolset.com/documentation/user-guides/export-import/importing-content-from-csv-with-post-relationships/#how-toolset-associations-can-be-added-to-a-csv

Looking at your example CSV, registrations type is a child to both other types, hence you need to update the data of that post type to have a meta field with key _toolset_associations_%%relationship_slug%% and value {!{parent_1_title}!}, {!{parent_2_title}!} (or GUID)

Currently, if you import this you can only do it with the name or GUID of the post to be connected.

I will run tests myself, but I need to know:
- the data you have, is in the <em<exact format as you show in the screenshot?
- could you send me a sample with connections?

I will, considering your details, create a local test with such data (I can generate it myself if I know exactly how it is structured, or maybe you can provide a sample of yours)
Maybe I can suggest some tricks to convert it or import it successfully.
I've enabled private replies in case you have sample data you can share.

[Martin Sauter]

Hi Beda

Thank you for the huge effort you made so far to solve my problem. While it's a pity that the import will not work as I have imagined it, it's still helpful to know that it's simply not possible and that I should not spend more time on this. I will therefore proceed according to your suggestions, but this will take some time. I'll report back as soon as I have a result.

Thanks again and have a nice day.

Martin

[Beda]

I suggest, to make a test run with a *bit* of your data, not all.

Then you can quickly spot structure and syntax issues or challenges.

Please let me know if you need more information or help with it.

[Martin Sauter]

I think I found a solution. Let me know what you think:

hidden link

Kind regards,
Martin

[Beda]

That is pretty much what we describe using WP All Import Pro and add-ons, just that it is using IDs instead of Name or GUID, which confuses me, as previously we found out exactly IDs are not supported...
https://toolset.com/forums/topic/how-to-import-data-from-a-database-with-m-n-relationships/#post-1446391

I would of course for now suggest following the official DOC, but I have also forwarded this discrepancy to Agnes so she can help us figure out why this difference (eventually one of the instructions is out of date)

I'll let you know what we find.