Save to parent and child table (foreign key)

[michaelh1]

Hi i have following problem:

I am parsing XML document to store these data into to two tables Author and Book:

Parent table Author (it works fine) and at the same time persist into child table Book with attribute author_id (fk) from Author table.

Persistng author works fine so far (blue arrow show component that persist author), but how can i continue in persisitng books with author_id as foreign key, how can i get the ID of persisted author?

P.S. I am using postgresql DB so tlastinsertedID doe not work for me.

[manodwhb]

@michaelh, Yes you do not have lastInsertid component for PostgreSQL, so you need to design two jobs one for author and another one for Book. while loading Book you need to join with the author to get author_id  and insert into Book table.

[michaelh1]

manodwhb can you please give me some hint how to achieve this?

so letas say i have parsed xml with books and authors with tXMLMap component i have two outputs:

out1-authors

out2-books 

(please see attached pictures)

how should i continue in flow after tXMLMap component? (how to store authors and books at the same time(books have author_id as FK))

[manodwhb]

@michaelh, you need to separate two flows and read source file two separately. 

[michaelh1]

@manodwhb so firstly i have to parse authors save the and then parse books and save them right? 

but last thing is that i need this to be done in one transaction so is this pattern ok?

[manodwhb · Accepted solution]

@michaelh , Yes create separate jobs.