CODEWITHSUNDEEP
sqlgraphgraph-databasesorientdb
retrography
retrography

Reputation: 6822

OrientDB SQL: How to find vertices and create edges between them?

On my database I run the following query: 

SELECT @rid AS module_rid, out('USES').out('BELONGS_TO').@rid AS project_rid FROM MODULES LIMIT 10

And I received the following response:

module_rid | project_rid
-----------|----------------
#12:0      | []
#12:1      | []
#12:2      | []
#12:3      |        
#11:48677  | #11:48677 #11:48677 #11:48677 #11:48677 #11:48677 ..More(49)
#12:4      |        
#11:48677  | #11:48677 #11:48677 #11:48677 #11:48677 #11:48677 ..More(49)
#12:5      |        
#11:2526   | #11:2526 #11:2526 #11:47148 #11:47148 #11:25338 ..More(30)
#12:6      | []

How can I create edges (e.g. RELIES_ON) between modules and the projects they rely on (they use at least one module of the project)?

Upvotes: 2

Views: 1613

Answers (2)

retrography
retrography

Reputation: 6822

This is what I ended up doing:

Define function createEdges with three parameters: createEdges(from, to, type)

// Check whether "from" is invalid or empty
if (from instanceof java.util.Collection) {
  if (from.isEmpty()) {
    return [];
  } else {
    var it = from.iterator();
    var obj = it.next();
    if (!(obj instanceof com.orientechnologies.orient.core.id.ORecordId)) {
      throw "Bad Input: createdEdges() only accepts ORecordIds or Collections of ORecordIds";
    }
  } 
} else if (!(from instanceof com.orientechnologies.orient.core.id.ORecordId)) {
  throw "Bad Input: createdEdges() only accepts ORecordIds or Collections of ORecordIds";
}

// Check whether "to" is invalid or empty
if (to instanceof java.util.Collection) {
  if (to.isEmpty()) {
    return [];
  } else {
    var it = to.iterator();
    var obj = it.next();
    if (!(obj instanceof com.orientechnologies.orient.core.id.ORecordId)) {
      throw "Bad Input: createdEdges() only accepts ORecordIds or Collections of ORecordIds";
    }
  } 
} else if (!(to instanceof com.orientechnologies.orient.core.id.ORecordId)) {
  throw "Bad Input: createdEdges() only accepts ORecordIds or Collections of ORecordIds";
}

var g = orient.getGraph();
var cmd = "CREATE EDGE " + type + " FROM " + from + " TO " + to;

return g.command("sql", cmd);

Define function uniq with one parameter: uniq(collection)

if (collection instanceof java.util.Collection) {
  if (collection.isEmpty()) {
    return collection
  } else {
    return new java.util.HashSet(collection)
  }
} else {
  throw "Bad Input: uniq() only accepts Java collections as input"
}

Now I can run the following SQL command:

SELECT createEdges(src, dst, 'RELIES_ON') FROM
(SELECT @rid AS src, uniq(out('USES').out('BELONGS_TO').@rid) AS dst FROM Modules)

This will create unique edges between the modules and the projects they rely on, and it is pretty fast.

Credits go to @vitorenesduarte who provided the initial response and the main inspiration...

Upvotes: 1

vitorenesduarte
vitorenesduarte

Reputation: 1579

create class Module extends V
create class Project extends V

create class Uses extends E
create class ReliesOn extends E


create vertex Module set name = 'm1'
create vertex Module set name = 'm2'
create vertex Module set name = 'm3'

create vertex Project set name = 'p1'
create vertex Project set name = 'p2'
create vertex Project set name = 'p3'


create edge Uses from (select from Module where name = 'm2') to (select from Project where name = 'p1')
create edge Uses from (select from Module where name = 'm3') to (select from Project where name = 'p2')
create edge Uses from (select from Module where name = 'm3') to (select from Project where name = 'p3')

I understand the situation above is a little different from what you have, but I believe it will be enough to understand a possible solution to your problem.

You can define a function createEdges e.g., like this:

var gdb = orient.getGraph();

if(to.size() != 0){
    var command = "create edge ReliesOn from " + from + " to " + to;
    gdb.command("sql", command);
}
return;

enter image description here

And now the following query will find the vertices whilst creating the edges:

select from (
    select @rid as module_rid, out('Uses').@rid as project_rid from Module
)
let $ce = createEdges(module_rid, project_rid)

UPDATE:

If you want to assure that "to" does not contains duplicates, you can:

select from (
    select @rid as module_rid, $aux[0].set.@rid as project_rid from Module
    let $aux = ( select set(out('Uses')) from $current )
) 
let $ce = createEdges(module_rid, project_rid)

Upvotes: 2

Related Questions