How to query Graph in recursive way?

[shiyewuxian]

Given that I have a person table. And I insert thousands of record:

[
  {  
    id: 1,
    name: andy,
  },
  {
    id: 2,
    name: bob,
  },
  {
    id: 3,
    name: cathy
  },
  ...
]

Then I make the relation like:

RELATE person:1 knows person:2;
RELATE person:2 knows person:3;
RELATE person:2 knows person:4;
RELATE person:3 knows person:4;
...

Since the length of knows is arbitrary. I could not know how many levels there shall be.
But the result I would like to have is to get the response including all the levels. The format may like this:

{
  person:{
   id:1,
   name: andy,
   knows:[
    {
      name: bob,
      id: 2
      knows: [
        {
          id: 3,
          name: cathy
          knows:[
            {
              id: 4,
              name: ...
            }
          ]
       
        },
        {
          id: 4,
          name: ...
        }
      ]
    }
   ]
}
}

Can anyone share how to deal with this user case?

[Bokitza]

No one?
I want to do a comparison between neo4j and surreal and this pretty much settles it as uncomparable

[Mautriz]

Recursive queries are a requirement for my use case, is there no way to do them ?

[dkr8toha]

There are only Record Links, which in my opinion are quite enough to work with. And they work, even if queried multiple times. Based on the CREATEs from the documentation you are able to do:

SELECT * FROM person WHERE name = "Tobie" FETCH friends
    ,friends.friends
    ,friends.friends.friends
    ,friends.friends.friends.friends
    ,friends.friends.friends.friends.friends

I dont see any use case for real recursive queries without having cursor support.

Or you may provide one?

[Bokitza]

Leta say I have departments. And every department can have a sub-department, related in a MANAGED_BY relation. And it goes all the way from the lowest department and up to the CEO office in an hierarchical order. I now want to draw the path from the lowest department to the CEO via MANAGED_BY. yet I don't know how many level would be. In one branch of the tree it's three, yet in another branch it's 20... How can you draw this path only by keeping surreal db busy, and not the server's CPU? Get Outlook for Android<https://aka.ms/AAb9ysg>

…

________________________________ From: dkr8toha ***@***.***> Sent: Tuesday, May 14, 2024 12:36:16 AM To: surrealdb/surrealdb ***@***.***> Cc: Bokitza ***@***.***>; Comment ***@***.***> Subject: Re: [surrealdb/surrealdb] How to query Graph in recursive way? (Discussion #2998) There are only Record Links<https://surrealdb.com/docs/surrealdb/surrealql/datamodel/records>, which in my opinion are quite enough to work with. And they work, even if queried multiple times. Based on the CREATEs from the documentation you are able to do: SELECT * FROM person WHERE name = "Tobie" FETCH friends ,friends.friends ,friends.friends.friends ,friends.friends.friends.friends ,friends.friends.friends.friends.friends I dont see any use case for real recursive queries without having cursor support. Or you may provide one? — Reply to this email directly, view it on GitHub<#2998 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AN3UDNWKW52RMILD6LV7F73ZCEW5BAVCNFSM6AAAAAA7RYXVHCVHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TIMRWGIZTI>. You are receiving this because you commented.Message ID: ***@***.***>

[dkr8toha]

Currently this is the only possible way i think:

CREATE department:one SET name = 'Office 1';
CREATE department:two SET name = 'Office 2';
CREATE department:three SET name = 'Office 3';
CREATE department:four SET name = 'Office 4';
CREATE department:ten SET name = 'Office 10';
CREATE department:eleven SET name = 'Office 11';
CREATE department:twelve SET name = 'Office 12';
CREATE department:thirteen SET name = 'Office 13';

RELATE department:one->managed_by->department:two;
RELATE department:two->managed_by->department:three;
RELATE department:three->managed_by->department:four;
RELATE department:ten->managed_by->department:three;
RELATE department:thirteen->managed_by->department:ten;
RELATE department:twelve->managed_by->department:thirteen;
RELATE department:eleven->managed_by->department:ten;

The following query will work:

(SELECT *,array::flatten([
    ->managed_by.out,
    ->managed_by.out->managed_by.out,
    ->managed_by.out->managed_by.out->managed_by.out,
    ->managed_by.out->managed_by.out->managed_by.out->managed_by.out,
    ->managed_by.out->managed_by.out->managed_by.out->managed_by.out->managed_by.out,
]) AS parents FROM department WHERE count(<-managed_by) < 1);

but must be extended (currently supports a depth of 5)...

will result in:

-------- Query 1 (2.2ms) --------

[
	{
		id: department:eleven,
		name: 'Office 11',
		parents: [
			department:ten,
			department:three,
			department:four
		]
	},
	{
		id: department:one,
		name: 'Office 1',
		parents: [
			department:two,
			department:three,
			department:four
		]
	},
	{
		id: department:twelve,
		name: 'Office 12',
		parents: [
			department:thirteen,
			department:ten,
			department:three,
			department:four
		]
	}
]

This works but well is a bit ugly :-)

I agree that there should be some query operator to recursively collect and expand the item like:

->managed_by->

->(managed_by->).out

->managed_by...

->managed_by..->managed_by

something like this:

SELECT *, ->managed_by-> as parents FROM department WHERE id = xxx

Ye i know, this is not the nested set you want to have but i personally hate the nesting ......

[dkr8toha]

should be

->managed_by(..3)->

or

->managed_by..3->

to define a maximum depth .....

[ssalka]

I have a similar use case, one table that can link back on itself. A really nice way IMO to write the query would be to introduce an UNTIL keyword (I don't think that's used for Surreal already?):

# $depth is a parameter managed internally
->knows-> UNTIL $depth = 5

# or, you can go until some conditions on the related record are true
->knows-> UNTIL <-hasNoFriends

[Dhghomon]

Recursive queries have been added with version 2.1! 🚀 Here are the main pages in the documentation that have it:

https://surrealdb.com/docs/surrealql/datamodel/idioms#recursive-paths

https://surrealdb.com/docs/surrealql/statements/relate#recursive-graph-queries

As well as Chapter 8 of the book.

https://surrealdb.com/learn/book/chapter-08#longer-relational-queries

[dkr8toha]

awesom!!! thank youuuuu! 🚀 👍🏻 🤪
i'll go playing with that soon

[SultanNasyrovDeveloper]

New graph traversal functionality is great! i read documentaion but still have some questions. Is there a way to sort and/or filter results on each itetation? can new graph traversal feature be used in delete or update queries?

[SultanNasyrovDeveloper]

Is there a way to select all columns with a star?

person:tobie.{..4}.{ *, connections: ->knows->person.@ };