How to create an edge to combine Classes on an id

[Curtis Mosters]

How is it possible to create an edge for creating a relationship between person and post.

CREATE edge hasAppln FROM (select FROM person) TO (select FROM post)

I already tried it with

CREATE edge hasAppln FROM (select FROM person) TO (select FROM post) where person.id = post.id

but it just combines everything. Let's say I have in both table 2 entries, so I get 4 edges. The Where is completely ignored somehow. Another thing is that this query on 4 entries takes 0,2 seconds. So this is not practicable. Is there another way to do that?

[Curtis Mosters]

I just did it with:

CREATE LINK out_hasAppln TYPE linkset FROM person.id To post.id
CREATE LINK in_hasAppln TYPE linkset FROM post.id To person.id

But for sure it's a link and not a edge. But maybe this could work. And now I search for those LINK and on every of them I create an EDGE. Wow this is very crappy but until it does not work it seems to be the best approach. ;)

[Alex Gann]

Are you trying to run one CREATE statement that would create edges for every pre-existing relationship between your person and post classes?

If you're creating an edge I would think you need to know the specific person(s) and post(s) you're trying to link ahead of time, and then use something like:

create edge hasAppIn from (select from person where id = personId) to (select from post where id = postId
)

from: http://www.orientechnologies.com/docs/1.7.8/orientdb.wiki/SQL-Create-Edge.html

Hope that helps!

[Curtis Mosters]

May I ask you what you mean with personID and postID?

Well I now tried:

CREATE edge hasPost FROM person.id To post.id

Because it's nearly the form of the LINK query. But that just gives me:

java.lang.IllegalArgumentException: Argument 'person.id' is not a RecordId in form of string. Format must be:

Here is a picture of the whole data:

http://i.imgur.com/oELqp4i.png

Ignore the LINK in the picture. So as you can see. I want to link (or let's say create an edge) from #14:1 to #13:1 to say that that Person has a post. Just ask if something is unclear =)

[odbuser]

Alex is right.  Just replace personID and postID with actual values (and use an appropriate link name).

In your example:

create edge hasPost from ( select from person where id = 1 ) to ( select from post where id = 1 )

[Curtis Mosters]

But image one million entries. So I need a for loop in SQL. Looking for an id in Person and then doing this statement above. Is that possible?

Edit: Also tried

create edge Wrote let $a = 1 from ( select from person where id = $a ) to ( select from post where id = $a )

but does not work. In that way I could do another SELECT on Person. But yeah it does not work =/

[odbuser]

Ok, sorry, I misunderstood your original intent.  I didn't realize that person.id=post.id.

create edge doesn't take a where clause so that won't work.

You can almost do it with update like so:

update person add out_Wrote=( select from post where id = $parent.current.id )
update post add in_Wrote=( select from person where id = $parent.current.id )
# Cleanup the empty collections
update person remove out_Wrote where out_Wrote.size() = 0
update post remove in_Wrote where in_Wrote.size() = 0

but that stores the edge as the wrong type even though graph functions seem to work fine...  this is also implementation specific and things probably break down if your edges have properties... or if the implementation changes over time... and it may not even be efficient.

Your only choice may be to do it programmatically in a loop using one of the apis... or create a js script and run it using the js or jss console commands...

#Note that function definition defaults to sql so you have to specify javascript
create function test "somejavascript" LANGUAGE javascript
select test()

The select statement will print a bogus empty record but it does execute your javascript.  functions are stored in the class OFunction so you can manage them using SQL

select from OFunction
delete from OFunction
etc.

Your javascript can include loops and access the database.  Check the wiki documentation.

[Curtis Mosters]

Will try it out for sure. But another idea maybe. When I run this:

CREATE LINK comments TYPE linkset FROM comment.postId To post.id INVERSE

This runs great. It connects the right entries with each other. Just missing the real edges and you cannot set properties this way. So maybe would it be good to do this. And after this another statement that looks for those connections and creating on them the real edges.

WDYT?

Am Mittwoch, 27. August 2014 22:33:05 UTC+2 schrieb odbuser:

Ok, sorry, I misunderstood your original intent.  I didn't realize that person.id=post.id.

create edge doesn't take a where clause so that won't work.

You can almost do it with update like so:

update person add out_Wrote=( select from post where id = $parent.current.id )

update car add in_Wrote=( select from person where id = $parent.current.id )

# Cleanup the empty collections
update person remove out_Wrote where out_Wrote.size() = 0

update car remove in_Wrote where in_Wrote.size() = 0

[odbuser]

I think you're still stuck with creating the edges programmatically using sql "create edge" or using the blueprint api.

create edge doesn't have a where clause to do the 'join'... but a syntax like this might be interesting:

# WARNING: this is not currenty valid syntax
create edge from ( select from car ) to ( select from car where $from.id=id )
or
create edge from ( select from car ) to ( select from car ) where $from.id=$to.id

create edge hasPost from ( select from person where id = 1 ) to ( select from post where id = 1 )

[Curtis Mosters]

Yeah that seems true. But it's very important to ahve such a thing.

But do you know how the JavaScript would look like. It's kind of

• 1.take first id of class Author
• 2.look for that id on class Post
• 3.create an edge on all entries
• return to 1.
  

[odbuser]

Here's an example of how to do this in Java.  The link method has the loop to create the edges.  I think the javascript will look very similar to the link method.

package orientdb.sql;

import com.orientechnologies.orient.core.sql.OCommandSQL;
import com.tinkerpop.blueprints.Vertex;
import com.tinkerpop.blueprints.impls.orient.OrientGraphFactory;
import com.tinkerpop.blueprints.impls.orient.OrientGraphNoTx;
import org.junit.Before;
import org.junit.Test;

public class LoopEdge {
    private OrientGraphFactory oGF;
    private OrientGraphNoTx graphNoTx;
   
    @Before
    public void before() {
        resetConnection();
    }
   
    @Test
    public void all() {
        dropDatabase();
        resetConnection();
        schema();
        data();
        link();
        select();
    }

    public void resetConnection() {
        if (oGF != null) {
            oGF.close();
        }
        oGF = new OrientGraphFactory("plocal:orientdb/loopedge");
        graphNoTx = oGF.getNoTx();
    }
    @Test
    public void schema() {
        graphNoTx.createVertexType("person");
        graphNoTx.createVertexType("post");
        graphNoTx.createEdgeType("Wrote");        
    }
    @Test
    public void dropDatabase() {
        graphNoTx.drop();
    }
    @Test
    public void data() {
        graphNoTx.addVertex("class:person", "name","John", "person_id", 1);
        graphNoTx.addVertex("class:post", "person_id", 1, "text", "hello");
        graphNoTx.addVertex("class:post", "person_id", 1, "text", "there");
        graphNoTx.addVertex("class:post", "person_id", 3, "text", "goodbye");
    }
    @Test
    public void link() {
        String sqlSelect = "select from person";
        Iterable<Vertex> persons = graphNoTx.command(new OCommandSQL(sqlSelect)).execute();
        for (Vertex person : persons) {
            String rid = person.getId().toString();
            Integer id = person.getProperty("person_id");
            String sqlCreate = "create edge Wrote from " + rid + " to ( select from post where person_id = " + id + " )";
            System.out.format("SQL:%s%n", sqlCreate);
            graphNoTx.command(new OCommandSQL(sqlCreate)).execute("person", rid, "id", id);
        }
    }
    @Test
    public void select() {
        String sqlSelect = "select *, out('Wrote').size() as postCount from person";
        Iterable<Vertex> persons = graphNoTx.command(new OCommandSQL(sqlSelect)).execute();
        for (Vertex person : persons) {
            System.out.format("name:%s person_id:%s postCount:%s%n",
                    person.getProperty("name"),
                    person.getProperty("person_id"),
                    person.getProperty("postCount"));
        }
    }
   
    public static void main(String[] args) {
        LoopEdge loopEdge = new LoopEdge();
        loopEdge.before();
        loopEdge.all();
    }
}

[MrFT]

Hello Curtis,

I had the same frustration (as you can see in this thread) and also check this related thread. People want JOIN syntax, because it's powerful, and then you could use that to create edges to unleash the full power of a graph database.

I didn't really get the message across, so I hope your mentioning it again will help the developers understand that some kind of 'join' syntax would be useful: for exploring the database, and creating new, previously inexistent edges based on various properties from different vertices, (and extremely useful if you have some existing data from a relational database that you want to play with).

I don't think there is a solution yet for what you want to do, but I do hope something ike that will be added soon to the SQL syntax.

Op donderdag 21 augustus 2014 20:00:58 UTC+2 schreef Curtis Mosters:

[Curtis Mosters]

I did it with Java API now. But yeah it would be would if the same would be available in the SQL like version. I gone throught the both topic and there was no real solution for my problem.

Or let's nothing to create an edge. A link is very easy tho.

Am Montag, 1. September 2014 11:17:52 UTC+2 schrieb MrFT:

Hello Curtis,

I had the same frustration (as you can see in this thread) and also check this related thread. People want JOIN syntax, because it's powerful, and then you could use that to create edges to unleash the full power of a graph database.

I didn't really get the message across, so I hope your mentioning it again will help the developers understand that some kind of 'join' syntax would be useful: for exploring the database, and creating new, previously inexistent edges based on various properties from different vertices, (and extremely useful if you have some existing data from a relational database that you want to play with).

I don't think there is a solution yet for what you want to do, but I do hope something ike that will be added soon to the SQL syntax.

Op donderdag 21 augustus 2014 20:00:58 UTC+2 schreef Curtis Mosters:

I just did it with: