RELATE statement

The RELATE statement can be used to generate graph edges between two records in the database.
This allows you to traverse related records efficiently without needing to pull data from multiple tables and merging that data together using SQL JOINs.

Edges created using the RELATE statement are nearly identical to tables created using other statements.
The key differences are that

• Edge tables have two required fields in and out, which specify the directions of the relationships.
• Edge tables are deleted once there are no existing relationships left.

Edge tables behave like normal tables in terms of updating, defining a schema or indexes.

Record links are the alternative option when connecting data.
The key differences are that graph relations

• Offer bi-directional querying.
• Offer referential integrity.
• Allow you to store data alongside the relationship.

Statement syntax

RELATE [ ONLY ] @from_record -> @table -> @to_record
	[ CONTENT @value
	  | SET @field = @value ...
	]
	[ RETURN [ NONE | BEFORE | AFTER | DIFF | @fields ... ]
	[ TIMEOUT @duration ]
	[ PARALLEL ]
;

Example usage

Basic usage

The following query shows the basic structure of the RELATE statement, which creates a relationship between a record in the person table and a record in the article table.

For table names we prioritise readability, such that normal tables are singular, person instead of persons, but edge tables can be plural.

RELATE person:l19zjikkw1p1h9o6ixrg->wrote->article:8nkk6uj4yprt49z7y3zm;

[
	{
		"id": "wrote:ctwsll49k37a7rmqz9rr",
		"in": "person:l19zjikkw1p1h9o6ixrg",
		"out": "article:8nkk6uj4yprt49z7y3zm"
	}
]

There is no relationship information stored in either the person or article table.
Instead, an edge table (in this case a table called wrote) stores the relationship information. As you see, the structure in -> id -> out mirrors the record IDs from the RELATE statement, with the addition of the automatically generated ID for the wrote edge table.

By default, the edge table gets created as a schemaless table when you execute the RELATE statement. You can however make the table schemafull by either defining a schema before or after executing the RELATE statement.

A common use case is to make sure only unique relationships get created. You can do that by defining an index.

DEFINE INDEX unique_relationships 
    ON TABLE wrote 
    COLUMNS in, out UNIQUE;

Adding data using SET and CONTENT

Let's look at the two ways you can add record data in the RELATE statement.
Both of these queries will produce the same result.

RELATE person:l19zjikkw1p1h9o6ixrg->wrote->article:8nkk6uj4yprt49z7y3zm 
    SET time.written = time::now();


RELATE person:l19zjikkw1p1h9o6ixrg->wrote->article:8nkk6uj4yprt49z7y3zm 
	CONTENT {
		time: {
			written: time::now()
		}
	};

Creating a single relation with the ONLY keyword

Using the ONLY keyword, just an object for the relation in question will be returned. This, instead of an array with a single object.

RELATE ONLY person:l19zjikkw1p1h9o6ixrg->wrote->article:8nkk6uj4yprt49z7y3zm;

Basics of querying graphs

Let's look at how the above gets queried, for more examples see the SELECTdocs.

For the questions below, each of the queries will give you the same answer.

-- Who wrote the articles?
SELECT in FROM wrote;

SELECT ->wrote.in FROM person;
SELECT ->wrote<-person FROM person;

SELECT <-wrote.in FROM article;
SELECT <-wrote<-person FROM article;

-- Which articles did the person write?
SELECT out FROM wrote;

SELECT ->wrote.out FROM person;
SELECT ->wrote->article FROM person;

SELECT <-wrote.out FROM article;
SELECT <-wrote->article FROM article;

-- When was the article written?
SELECT time.written as time_written FROM wrote;
SELECT ->wrote.time.written as time_written FROM person;
SELECT <-wrote.time.written as time_written FROM article;

For a more complicated query like the one below you can use a simple rule of thumb:
Place the subject in front of the graph selection, then read it backward.

-- This query
SELECT ->purchased->product<-purchased<-person->purchased->product FROM person:tobie

-- Then becomes
person:tobie->purchased->product<-purchased<-person->purchased->product SELECT

Reading this backwards then makes more sense:
Select every product that was purchased by a person who purchased a product that was also purchased by person Tobie.

Alternatively, you can break it down in steps:

-- Focusing on Tobie
person:tobie

-- Which products did he purchase?
->purchased->product

-- which persons also purchased those products?
<-purchased<-person

-- and what did they purchase?
->purchased->product

Putting it all together it would be:
based on all the products Tobie purchased, which person also purchased those products and what did they purchase?

Using LET parameters in RELATE statements

You can also use parameters to specify the record IDs.

-- These two statements store the result of the subquery in a parameter
-- The subquery returns an array of IDs
LET $person = (SELECT VALUE id FROM person); 
LET $article = (SELECT VALUE id FROM article);

-- This statement creates a relationship record for every combination of Record IDs
-- Such that if we have 10 records each in the person and article table
-- We get 100 records in the wrote edge table (10*10 = 100)
-- In this case it would mean that each article would have 10 authors
RELATE $person->wrote->$article SET time.written = time::now();

Using the RETURN clause

By default, the relate statement returns the record value once the changes have been made. To change the return value of each record, specify a RETURN clause, specifying either NONE, BEFORE, AFTER, DIFF, or a comma-separated list of specific fields to return.

-- Don't return any result
RELATE person:l19zjikkw1p1h9o6ixrg->wrote->article:8nkk6uj4yprt49z7y3zm 
    SET time.written = time::now() 
    RETURN NONE;

-- Return the changeset diff
RELATE person:l19zjikkw1p1h9o6ixrg->wrote->article:8nkk6uj4yprt49z7y3zm 
    SET time.written = time::now() 
    RETURN DIFF;

-- Return the record before changes were applied
RELATE person:l19zjikkw1p1h9o6ixrg->wrote->article:8nkk6uj4yprt49z7y3zm 
    SET time.written = time::now() 
    RETURN BEFORE;

-- Return the record after changes were applied (the default)
RELATE person:l19zjikkw1p1h9o6ixrg->wrote->article:8nkk6uj4yprt49z7y3zm 
    SET time.written = time::now() 
    RETURN AFTER;

-- Return a specific field only from the updated records
RELATE person:l19zjikkw1p1h9o6ixrg->wrote->article:8nkk6uj4yprt49z7y3zm 
    SET time.written = time::now() 
    RETURN time;

The TIMEOUT clause

When processing a large result set with many interconnected records, it is possible to use the TIMEOUT keyword to specify a timeout duration for the statement. If the statement continues beyond this duration, then the transaction will fail, and the statement will return an error.

-- Cancel this conditional filtering based on graph edge properties
-- if it's not finished within 5 seconds
SELECT * FROM person WHERE ->knows->person->(knows WHERE influencer = true) TIMEOUT 5s;

The PARALLEL clause

When processing a large result set with many interconnected records, it is possible to specify the PARALLEL keyword to signify that edges and remote records should be fetched and processed in parallel.

-- Fetch and process the person, purchased and product targets in parallel
-- Select every product that was purchased by a person that purchased a product that person tobie also purchased
SELECT ->purchased->product<-purchased<-person->purchased->product FROM person:tobie PARALLEL;

Deleting graph edges

You can also graph edges between two records in the database by using the DELETE statement.

For example the graph edge below:

RELATE person:tobie->bought->product:iphone;

[
	{
		"id": "bought:ctwsll49k37a7rmqz9rr",
		"in": "person:tobie",
		"out": "product:iphone"
	}
]

Can be deleted by:

DELETE person:tobie->bought WHERE out=product:iphone;