Thread

I am importing thousands of database records in a one-to-many relationship. I used the CSV import plug-in, and am now beginning the programmatic creation of relationships. I understand the structure of the exp_relationships table, but am stumped by what I’m seeing.

To isolate the problem, I have created two test weblogs—one parent and one child. I have one record in each, and a relationship field in the parent weblog pointing to the child weblog. If I edit the parent record and allow ee to create the exp_relationships record, all works as expected. I then remove the relationship via the “Edit” screen.

If I then manually create an identical exp_relationships record (minus the rel_data serialized data), the relationship is NOT established when I edit the parent record. The relationship field is blank.

Now here’s the clincher: if I create the relationship from the “Edit” screen then clear the rel_data field in the database, THE RELATIONSHIP REMAINS. So the absence of the rel_data when manually creating the exp_relationships record is not the issue.

Apparently, there is some other place the relationship data is established/cached. I have repeatedly cleared all caches, to no avail.

This problem is holding up the development of a site on my fresh new personal license. I am using 1.6.0.

Can anyone help?

Thanks!

You’ll need the rel_id number in the exp_weblog_data table- matching the custom field to the exp_relationships table.  So- need to hit both exp_weblog_data and exp_relationships when you manually create it.  Did you hit both spots and add the proper info?

That was it! Thank you so much—now I can get on to my next show-stopper.

I have one follow-up question, though. I searched the docs, forums, wikis and web for information on this, and came up dry. Where is this level of detail re: EE’s inner workings available? If nowhere, are there plans for such documentation? I’m know I’m just the latest in a long string of developers who need this type of info.

Thanks again for the speedy reply. You folks are the BEST!

There generally isn’t a doc with this level of detail- and in truth, it would be difficult to keep one up.  It’s pretty deep into the code- and at that point, I’ll generally start looking at the files to see what happens on publish- for example.  I know the relationship stuff pretty well, since I’ve played with it a fair bit.  It’s definitely one of the more complex bits- I’ve actually got notes on it so I can try to keep things straight!

That said- the organization of the files is very intuitive.  If you’re looking for the code for a backend page?  It’s going to be in system/cp (for ‘control panel’)- and you can track down which file and function via the segments in the url.  Shared functions are generally going to be in ‘core’- and you can track down the class easily via the $REGEX->function()- it’s the regex class- so system/core/regex.  Takes a bit of playing to get used to the structure, but it’s a lot more intuitive than any other system I’ve played with.  By an order of magnitude in most cases!

I agree that EE’s structure is much more intuitive than most. What a blessing!

I still maintain, though, that an overview of the various tables and how they relate would be a big help to many. For example, something like the following would have saved me (and you) valuable time.

Manually Creating Weblog Relationships
———————————————————

The exp_relationships table holds the bulk of the data required to establish relationships between weblogs. In addition to an exp_relationships record, a relationship also requires that one field be set in the exp_weblog_data record of the parent entry.

The structure of the exp_relationships table is as follows.

rel_id: a unique id for the relationship record (this is auto-generated by MySQL)
rel_parent_id: the entry_id for the parent record
rel_child_id: the entry_id for the related child record
rel_type: the type of relationship (i.e. “blog”)
rel_data: auto-generated cache data to speed up regular relationship page builds
reverse_rel_data: similar cache data for reverse relationship pages

To establish a weblog or section relationship, only the first FOUR fields must be populated. EE generates the cached data as required.

In addition, the parent’s exp_weblog_data record must be set to contain the rel_id value of the exp_relationships record. This value is set in the field_id_### field that corresponds to the parent weblog’s relationship field.

For example:
On a magazine site, two distinct weblogs hold articles and authors. A relationship field (“author”) is added to the article weblog to point to the corresponding author record. This relationship field is field_id_23.

The article “The New iBlender” (articles weblog, entry_id 332) was written by Ken Mac (authors weblog, entry_id 75).

In this example, the final relationship is established by the following field data:

exp_relationships
————————-
rel_id: 783 (auto-generated)
rel_parent_id: 332 (article record entry_id)
rel_child_id: 75 (author record entry_id)
rel_type: “blog”

exp_weblog_data (for parent entry_id 332, in the article weblog’s “author” field)
———————-
field_id_23: 783 (rel_id value from exp_relationships)

Hopefully, someone else will find this helpful.

What a wonderful write up; I hope you’ll take a moment to add that to our wiki. You might also be interested in this, just take heed of the note at the top. =)

Wow—that’s quite an endorsement from the docs queen herself!

I have posted my article to the wiki, in the hopes that it will help someone else.

I am importing thousands of database records in a one-to-many relationship. I used the CSV import plug-in, and am now beginning the programmatic creation of relationships. I understand the structure of the exp_relationships table, but am stumped by what I’m seeing.

Hi ee4me, this is exactly the same situation I am in at the moment. I was hoping that “CSV import” plug-in would also take care of creating relationships automatically. Too bad it’s not the case :( and I have to manually edit those related fields.

Thank you anyway for the valuable information.

PS. Can you also post the wiki link please, I can’t seem to find it.

I just looked in the wiki, and couldn’t find it. A search turned it up here.

Note: my original link, and e-man’s below, broke. The article needed to be renamed, and now the link above is working.

Good luck!

Here’s the correct link: relationships under the hood.

e-man, could I ask you to have a look at this thread please?  Thank you. =)