Talend community forum / Creating two joined output rows in different tables from one input row

ShaunWinters · 2011-09-15 22:59:38

I'm currently porting an old proprietary custom CMS to Wordpress, and I'm attempting to move over the post data (contained in the content table) from the original CMS while retaining all the extra data that's contained in the old CMS content table's structure. Wordpress has a posts table that only contains the bare essentials, while the rest of the post data is stored in the postmeta table.

I have no problem creating the new Wordpress posts record, but my problem is that the posts table generates a new primary key, one that is not shared with the original content table.... and the postmeta table needs to be keyed on the key that the mysql DB generates.

Now I've figured out how to use tMysqlLastInsertID and OnSubjobOK to pass on the new key .... but I'm lost on this part:

..... How can I pass on the data from the content table when it's not keyed on the same ID?

I don't have anywhere to store the content row's primary key in the new posts row .... how can I get these two keys associated so I can return and extract the pertinent data from the content table to store in the postmeta table?

I'm at a complete loss of what to do, and this is a time sensitive project. Any help would be *greatly* appreciated.

ShaunWinters · 2011-09-15 23:06:52

Please ignore the "tos3.0.0" tag... that was a misclick

ShaunWinters · 2011-09-15 23:43:08

I should add, I'm using TOS Version: 4.2.2 Build id: r63143-20110622-0628

ShaunWinters · 2011-09-16 15:35:17

This is starting to seem like a hopeless cause. Does anyone have any clue what I'm doing wrong?

shong · 2011-09-16 16:32:16

Hi
Can you upload a screenshot of job? It will be helpful for us understanding your problem.

Best regards
Shong

ShaunWinters · 2011-09-16 16:44:38

Shong, the problem is that I don't know how to structure my job. Showing you a screenshot of my current attempts would be fruitless as I know they're entirely wrong.

I can show you an example of want I want though (though my actual solution will contain many more fields):

Input table:
[content]
content.id
content.name
content.body
content.category
content.company

Output tables:
[posts]
posts.id
posts.name
posts.body

[postmeta]
postmeta.id
postmeta.postid
postmeta.metakey
postmeta.metavalue

Data flow:
posts.id = (automatically assigned by mysql)
posts.name = content.name
posts.body = content.body

(new postmeta record)
postmeta.id = (automatically assigned by mysql)
postmeta.postid = posts.id (as generated by the related posts insert)
postmeta.metakey = "old_id"
postmeta.metavalue = content.id

(new postmeta record)
postmeta.id = (automatically assigned by mysql)
postmeta.postid = posts.id (as generated by the related posts insert)
postmeta.metakey = "category"
postmeta.metavalue = content.category

(new postmeta record)
postmeta.id = (automatically assigned by mysql)
postmeta.postid = posts.id (as generated by the related posts insert)
postmeta.metakey = "company"
postmeta.metavalue = content.company

Last edited by ShaunWinters (2011-09-16 16:45:40)

alevy · 2011-09-18 13:06:59

I think what you want is:
content --> tMap -order1-> posts --> tMysqlLastInsertID --> tSetGlobalVar (to store the new ID)
-order2-> tSplitRow (separate into three records and put in the new ID from the globalMap) --> postmeta

You can't use the ID from the globalMap directly in tMap because of Don't populate output row until ready to execute branch.

ShaunWinters · 2011-09-19 16:17:37

Well for now, as a workaround, (and against my better judgement) I've added a new field to the Wordpress posts table to store the old CMS content ID... I'm just running two separate processes now.

I'm blown away that I even had to resort to this kind of hack for such a seemingly simple task.

ShaunWinters · 2011-09-21 03:03:33

I'd still like to know how to do this properly, if anyone's willing to indulge me

alevy · 2011-09-21 04:21:32

What was the problem with my suggestion?

Announcement

Unanswered posts

#1 2011-09-15 22:59:38

Creating two joined output rows in different tables from one input row

#2 2011-09-15 23:06:52

Re: Creating two joined output rows in different tables from one input row

#3 2011-09-15 23:43:08

Re: Creating two joined output rows in different tables from one input row

#4 2011-09-16 15:35:17

Re: Creating two joined output rows in different tables from one input row

#5 2011-09-16 16:32:16

Re: Creating two joined output rows in different tables from one input row

#6 2011-09-16 16:44:38

Re: Creating two joined output rows in different tables from one input row

#7 2011-09-18 13:06:59

Re: Creating two joined output rows in different tables from one input row

#8 2011-09-19 16:17:37

Re: Creating two joined output rows in different tables from one input row

#9 2011-09-21 03:03:33

Re: Creating two joined output rows in different tables from one input row

#10 2011-09-21 04:21:32

Re: Creating two joined output rows in different tables from one input row

Board footer