Find legacy id of newly created node

Question

I have 3 non-drupal tables : items, item_actions, actions.

 items(id, title, body, published_at);   
 item_actions(item_id, action_id);   
 actions(id, name);   

I were able to migrate items to drupal 7. Now i want to migrate actions but since i don't know the node_id of the newly migrated items just the legacy id it is throwing an error.
How do i find the node_id knowing the legacy_id so that it finds the right node and migrate related content.

$query = Database::getConnection('default', 'for_migration')
  ->select('actions', 'actions')
  ->fields('actions', array('id', 'name'))
  ->orderBy('id', 'DESC');
// Join values from the action_items.
 $query->leftJoin('action_items', 'ai', '(actions.id = ai.action_id)'); 
 $query->addField('ai', 'item_id', 'item_list');    

Here is the error:

The referenced entity (node: 123456) is invalid.   

update
The migration was done with the migration module. So i had to write class migration and use queries like the one above to migrate to D7 the items table.
Can i create a table in the drupal DB and add the ids(old and new) for example: migrate_nodes(legacy_id, drupal_node_id) .
How do you capture the node_id?
I am new to drupal so this is still a learning process, thanks.

Answer 1

How did you migrate the content from the nond-drupal tables to nodes? Module or custom code?

I would suggest a 'custom' migration routine/module (if it is a lot of data you can do this as a cron and/or batch job - that is a separate discussion).

Any case, in your custom routine, when you migrate each record (so read a record form your table and create a new node) from items and actions - you will be able to 'capture' the node id (nid) if the new node. Save this value to your 'old' items/actions tables (new field required). Now when you step through your item actions table you can use the nid saved in items and actions as reference values.