Relations - SurrealTools/Documentation GitHub Wiki

Connections

There are two types of 'connections' in SurrealDB...

Record pointers. These are one direction only...

CREATE person:tobie SET name = 'Tobie';
CREATE person:jaime SET name = 'Jaime';
CREATE company:surrealdb SET name = 'SurrealDB', founders = [person:tobie, person:jaime];
SELECT founders.*.name FROM company:surrealdb;

Graph edge connections. These can be queried in both directions...

CREATE person:tobie SET name = 'Tobie';
CREATE person:jaime SET name = 'Jaime';
CREATE company:surrealdb SET name = 'SurrealDB';
RELATE person:tobie->works_at->company:surrealdb;
RELATE person:jaime->works_at->company:surrealdb;
SELECT <-works_at<-person.name AS people FROM company:surrealdb;
SELECT ->works_at->company.name FROM person;

Graph Edges

-- Everything is directed in SurrealDB, so an arrow points out to another table, or points in.
RELATE product:macbook<-purchased<-person:tobie SET when = time::now();
-- Or...
RELATE person:tobie->purchased-product:macbook SET when = time::now();
-- Then you can use the arrows to traverse those edges...
SELECT ->purchased->product FROM person:tobie;
-- Or
SELECT <-purchased<-person FROM product:macbook;
-- Although you have to always define a direction when inserting an edge, you can use undirected edges when querying
SELECT <->purchased<->product FROM person:tobie;
-- This is useful if you don't necessarily know the direction of an edge
Note: if we have a vertex - edge - vertex then if either vertex is dropped, then the edge will also be dropped.

The graph edges (created using RELATE) can have metadata set on them, as they are themselves records. In addition, they can be traversed both ways when querying. So you can follow all edges 'out' or follow all edges 'in' (or both ways too).

Record links on the other hand, only point out...the record that it points to has no idea that it is being linked to.

We do have plans to create record link constraints (so when adding a record pointer link between records, it gets added to the record, and can then update the foreign record), but it doesn't do that yet.

DEFINE LINKS BETWEEN user ON TABLE email (REJECT UPDATE, REJECT DELETE) AND emails ON TABLE user (REJECT UPDATE, REJECT DELETE);
/*
WHEN email IS DELETED
    -> UPDATE user REMOVE emails.$this
    -> PREVENT

WHEN user IS DELETED
    -> UPDATE emails.* REMOVE user
    -> DELETE emails.* WHERE user
    -> PREVENT
*/

Relations are bi-directional, and allow single, single-to-many, or many-to-many relationships. However they do have more overhead because of this...

There is a reference added to the start record.
There is a node record added (the edge).
There is a reference added to the edge record (pointing to the start record).
There is a reference added to the edge record (pointing to the end record).
There is a reference added to the end record.

Record links are similar in the sense that they enable you to reference foreign values. However, RELATE is more robust as you can have an edge with its own properties and do more complex queries. Record links are easier to work with IMO as you can embed them directly into your models but they don't offer as much capabilities. The heuristics I use:

Use record links for simple direct relationships
Use Relate for handling more complex scenarios, especially when I'm interested in the properties of the edges themselves, e.g when working with geo-data.

The oversimplified idea of RELATE is: This(Noun)->does(verb)->that(Noun), and the reverse would be That<-does<-This.

Select Tobie's purchased products that were purchased by people who purchased within the last 3 weeks. or Find any products purchased in the last 3 weeks by anyone who bought the same products that Tobie did.

You start with Tobie record, as stated in the FROM clause... -> purchased -> product : this gives you Tobie's products... <- purchased <- person : this gives you all people who bought the same products... -> purchased[? created_at > time::now() - 3w] -> product : gives you products purchased by any of these people in last 3 weeks.

SELECT ->purchased->product<-purchased<-person->purchased[WHERE created_at > time::now() - 3w]->product as puchased_products_within_last_3_weeks FROM person:tobie;

Response

{ 
   "id": "person:tobie", 
    "puchased_products_within_last_3_weeks": ["product:1", "product:20", ...]
}

And if you fetch i.e (FETCH puchased_products_within_last_3_weeks), it loads the reference foreign values:

SELECT ->purchased->product<-purchased<-person->purchased[WHERE created_at > time::now() - 3w]->product as puchased_products_within_last_3_weeks FROM person:tobie FETCH puchased_products_within_last_3_weeks;

or without fetch
SELECT ->purchased->product<-purchased<-person->purchased[WHERE created_at > time::now() - 3w]->product.* as puchased_products_within_last_3_weeks FROM person:tobie FETCH puchased_products_within_last_3_weeks;

Response

{ 
   "id": "person:tobie", 
    "puchased_products_within_last_3_weeks": [ 
                     {id: "product:1", name: "Apple" }, 
                     {id: "product:20", name: "PS5"}, 
                     ...
                   ]
}

Select Tobie's purchased products that were purchased by Jamie within the last 3 weeks.

SELECT ->purchased->product<-purchased<-person[WHERE id = "person:jamie"]->purchased[WHERE created_at > time::now() - 3w]->product as puchased_products_within_last_3_weeks FROM person:tobie;

Select Any relation

The question mark basically means any type of relation so for example

SELECT ->likes->(? as rel) FROM user;

could capture both of these:

SELECT ->likes->product FROM user;
SELECT ->likes->picture FROM user;

Filter graph edge

SELECT
  ->connected->(sensor WHERE type = "fridge")->emit->(reading AS reading)
FROM
  printer:dtrtbz3427wi8431510l
FETCH
  sensor, reading

UnRelate records

A RELATE statement just creates a record in a table (and a few other fancy things), so you should just be able to delete the record that is created!

DELETE interested_in WHERE in.username = 'user_male' AND out = gender:female
-- This will search the whole 'friend' table
-- and filter based on the 'in' and 'out' fields
DELETE friend WHERE in = user:1 AND out=user:foo
-- This will iterate just the edges of the 'user:1'
-- record, filtering based on the 'out' field.
-- Therefore it's much more efficient
DELETE FROM user:1->friend WHERE out = user:foo;

LET $from = type::thing($baseTbl, $baseId);
LET $with = type::thing($otherTbl, $otherId);
RELATE $from->relation->$with;

Get all relationships

SELECT ->?->? AS relationships FROM person;

Add a graph edge between specific users and developers

LET $from = (SELECT users FROM company:surrealdb);
LET $devs = (SELECT * FROM user WHERE tags CONTAINS 'developer');
RELATE $from->like->$devs UNIQUE SET time.connected = time::now();

``` just to clarify...[1,2,3] -> knows -> [4,5,6] will create edges...

1 -> knows -> 4
1 -> knows -> 5
1 -> knows -> 6

2 -> knows -> 4
2 -> knows -> 5
2 -> knows -> 6

3 -> knows -> 4
3 -> knows -> 5
3 -> knows -> 6

Q and A

Quick question regarding graph and RELATE statement. Assume CONTENT is properties on the edge. Is the edge bidirectional? Also, can I filter results based on CONTENT in the edge? https://github.com/orgs/surrealdb/discussions/167#discussioncomment-3732624

CREATE person:tobie SET name = 'Tobie';
CREATE person:jaime SET name = 'Jaime';
RELATE person:tobie->knows->person:jaime SET settings.created_at = time::now();
-- The edge can be queried bidirectionally, but always has a direction...
SELECT ->knows->person.name FROM person:tobie; -- Following the out connections from person:tobie
SELECT <-knows<-person.name FROM person:jaime; -- Following the in connections from person:jaime
SELECT <->knows<->person.name FROM person:tobie; -- Following either direction from person:tobie
-- You can then select data from the connected edge...
SELECT ->knows.settings.created_at FROM person:tobie;
-- You can then query and filter on the edge fields/data...
SELECT ->(knows WHERE settings.created_at > time::now() - 1w)->person FROM person:tobie;
-- You also don't have to know the type of the edge, or the type of the connected node...
SELECT ->?->? FROM person:tobie;
-- Or filter on an unknown type...
SELECT ->(? WHERE settings.created_at > time::now() - 1w)->? FROM person:tobie;

From a query perspective, certain queries are slightly slower than others...

SELECT ->? FROM person; -- Will select a single edge direction, and all types (one range query over the edges)
SELECT ->knows FROM person; -- Will select a single edge direction, and a single type (one range query over the edges)
SELECT ->(knows, likes) FROM person; -- Will select a single edge direction, and two types (two range queries over the edges)
SELECT <->knows FROM person; -- Will select two edge directions, and a single type (two range queries over the edges)
SELECT <->(knows, likes) FROM person; -- Will select two edge directions, and a two types (four range queries over the edges)

SELECT *, $this->edge->vertex FROM node;

SELECT *, $this->sells->product AS products FROM store;

So basically you can improve the query performance by using the correct query that's right for a particular use-case..

SELECT ->knows->(? AS f1)->knows->(? AS f2)->(knows, likes AS e3 WHERE influencer = true)->(? AS f3) FROM person:tobie;

you can also use record links (which are just pointers, and not bi-directional)...

UPDATE person:tobie SET name = 'Tobie', friend = person:jaime;
UPDATE person:jaime SET name = 'Jaime', friend = person:tobie;
SELECT friend.name FROM person:tobie; -- Will return 'Jaime'
-- You can traverse records as many times as you want in a query
SELECT friend.friend.friend.friend.friend.name FROM person:tobie; -- Will return 'Jaime'

These are very efficient, because they point directly at a single record, and require no lookup or scan. You can then define field constraints...

DEFINE FIELD friend ON TABLE person TYPE record (person); -- single type
DEFINE FIELD friend ON TABLE person TYPE record (person, animal); -- polymorphic types
DEFINE FIELD friend ON TABLE person TYPE record (person) ASSERT $value != NONE; -- ensure a value is set

And then you could create a back reference if you want...

DEFINE EVENT friend ON TABLE person WHEN $after.friend THEN (UPDATE $after.friend SET friend += $this);

Think of a record id, as a foreign key which points out, but doesn't point back in. Think of a graph edge, as a bi-directional connection between 2 records.So you could do...

CREATE person:tobie SET name = 'Tobie', brother = person:jaime;
CREATE person:jaime SET name = 'Jaime', brother = person:tobie;
SELECT brother.name AS brother FROM person;tobie;

OR

CREATE person:tobie SET name = 'Tobie';
CREATE person:jaime SET name = 'Jaime';
RELATE person:tobie->is_brother_of->person:jaime;
SELECT ->is_brother_of->person.name AS brother FROM person;tobie;

LET $login = ( CREATE login SET ts = time::now() );
RELATE user:1->logged_in->$login;
SELECT *, ->logged_in->login.ts FROM user:1;

Prevent creating duplicate edges when using the RELATE

DEFINE INDEX test ON relates COLUMNS in,out UNIQUE

Question: The problem is that references will be ever increasing, so think of a transaction that has many different accounts. As transactions come in I want to link it to every account.But then I want to find every transaction for an account. That could be 100,000s not sure which is the more effecient way to handle

Answer: You'd want to use the graph edges (RELATE) for this case.If you are traversing all 'in' edges to an account, then fetching these 'in' edges is efficient.

LET $account = ( SELECT * FROM account WHERE name = 'apple' LIMIT 1 );
LET $payment = ( CREATE payment SET amount = 1991040.13 );
RELATE $account->made_payment->$payment SET created_at = time::now();
SELECT ->made_payment->(payment WHERE created_at > time::now() - 1d) FROM $account;

RELATE user:tobie->write->article:surreal SET tags = ['golang', 'rust'], time.written = time::now();
RELATE person:tobie->purchased->product:macbook SET time.purchased = time::now(), amount = 3000.00, currency = 'USD';

Question: I have a sample data of beverage->soldin->coffeeshop attaching a price to the soldin relationsip. I'm trying to get query.

SELECT name, ->(soldin AS soldin)->(coffeeshop AS shop) from beverage:latte;

Question: if I have two records executed with CREATE person:john set name="john" and CREATE person:peter set name="peter" then I called this, RELATE person:john->knows-person:peter, what is my query so that I can get all the persons who john knows

SELECT name, ->knows FROM person:john;

Question: How can I get all the id and names of the people john knows without calling select from each of them because that SELECT statement you showed will give only the document ids(knows:record_id) of the person john knows.

SELECT ->knows->person.* FROM person:john;
or
SELECT ->knows->(person AS connections) FROM person:john FETCH connections;

Question: Is there a way to do bi-directional relations? Use case would be friends in a social network– you want both ends of the friendship dropped if it's removed from one side.**

Answer I think both ends of the friendship will be dropped automatically.

Question: How do you get the attribute price from a RELATE? The price is set on the bought record...

SELECT *, ->(bought AS bought)->item.* AS items FROM user:1 FETCH bought;

Question: Does anyone know how you get fields on the relate edge in a query like this?

SELECT ->print->file.* FROM printer:1

Answer:

SELECT ->(print AS prints)->(file AS files) FROM printer:1 FETCH prints, files;

Question: Can we make a relation target another relation.As relations are tables like any other, can you do something like (it's a just an example)?