Virtual Nodes and Virtual Relationships

Turn erp.po_line (purchase order lines), a table that is not exposed yet, into a virtual node and connect it to purchase orders and items with relationships. You will practice linking tables from different databases into a single graph.

What is a virtual node?#

A virtual node type defines one source table as a node type in the graph.

• One source row = one node, and columns = properties.
• The KEY column is the value that tells nodes apart (usually the primary key).
• No data is copied. Every time you run MATCH (n:PoLine), the source erp.po_line is read.
• It is read-only. If you CREATE or SET on a virtual node, the engine refuses. To change values you have to change them in the source system, and in Ontological Studio that is the job of actions (Part 6).

Try it — order lines as nodes#

1. In the Data Sources tab, select erp and click po_line in the table list. It still shows Not exposed.
2. In the Expose as a virtual node box at the bottom right, set the node type name to PoLine, the graph to sample and the KEY column to po_line_id.
3. If you leave the properties as they are, every column becomes a property with the same name. To rename one, edit the box on the right (for example, qty → quantity).
4. Check Statement to run and click Create virtual node.

This is the statement that runs.

CREATE VIRTUAL NODE PoLine IN sample FROM erp.po_line KEY po_line_id

If you rename properties, a clause such as PROPERTIES (po_line_id, po_id, item_id, qty AS quantity) is added. As soon as the node type is created, the preview shows the 9 rows from the source.

Viewing the virtual graph#

Click sample under the virtual graphs at the bottom left to see the nodes and relationships, grouped by data source, as a graph. PoLine is not connected to any other node yet.

Try it — connecting with relationships#

An order line means a particular item in a particular purchase order. Define these two connections as relationships.

1. In the relationship form at the bottom of the virtual graph view, enter the relationship name IN_ORDER.
2. Choose start node PoLine, property po_id → end node PurchaseOrder, property po_id.
3. Click Create relationship.

CREATE VIRTUAL RELATIONSHIP IN_ORDER IN sample FROM PoLine(po_id) TO PurchaseOrder(po_id)

Connect it to items the same way. This time, type the statement directly in the Portal (the Portal is covered in detail in Chapter 7; set the graph selector to sample).

CREATE VIRTUAL RELATIONSHIP OF_ITEM IN sample FROM PoLine(item_id) TO Item(item_id)

A virtual relationship copies no data. It is a join rule (PoLine.po_id = PurchaseOrder.po_id) saved under a name, and at query time the sources are joined by that rule.

Using it right away#

Now you can write a query that asks “Which items, and how many, did we order from each supplier?” It follows the relationships across four nodes: supplier → purchase order → order line → item.

MATCH (s:Supplier)<-[:ORDERED_FROM]-(po:PurchaseOrder)<-[:IN_ORDER]-(l:PoLine)-[:OF_ITEM]->(i:Item)
RETURN s.name, i.name, sum(l.qty) AS qty
ORDER BY s.name, i.name

Next, a question that spans two systems: “Which items are the shipments more than 3 days late carrying, and what is their safety stock?” Shipments are in WMS; purchase orders, order lines and items are in ERP.

MATCH (i:Item)<-[:OF_ITEM]-(l:PoLine)-[:IN_ORDER]->(po:PurchaseOrder)<-[:SHIPS]-(sh:Shipment)
WHERE sh.delay_days > 3
RETURN DISTINCT i.name, i.safety_stock, sh.shipment_no, sh.delay_days

Both shipments are carrying PCB boards. Now you know which item to flag to the inventory manager first.

Removing what you made#

DROP VIRTUAL RELATIONSHIP OF_ITEM IN sample
DROP VIRTUAL RELATIONSHIP IN_ORDER IN sample
DROP VIRTUAL NODE PoLine IN sample

You can't drop a node that still has relationships. The engine refuses with virtual relationships still join on 'PoLine' (IN_ORDER, OF_ITEM); drop them first, and the Data Sources tab restates this reason in plain language. Drop the relationships first. The source tables are not affected at all.

MATCH (s:Supplier)<-[:ORDERED_FROM]-(po:PurchaseOrder)<-[:IN_ORDER]-(l:PoLine)-[:OF_ITEM]->(i:Item)
WHERE po.status = 'OPEN'
RETURN s.name, i.name, sum(l.qty) AS qty ORDER BY s.name, i.name