How to results fields from every nodes in a path with SQL?

[László Rékasi]

Hi,

I have the following datastructure (every entity has its own class):

vertexes : vA, vB, vC, vD, vE

there is eAB between vA and vB with one to many relation

eBC between vB and vC with one to many relation

eDC between vD and vC with one to many relation from D to C (so vC is a for resolving many to many between vB and vD, but having lots of properties)

eED between vE and vD with one to many relation from E to D

Now I would like to find vA and vD nodes where there is a path between with the following conditions and expectations:

• I need the ID and some fields from both vA and vD
• want to know the number of path between vA and vD
• want to sum() some fields in vC as weight
• want to filter on fields of all vertexes (vA.fld='X', vB.fld_b = 'Y', etc

Basically the situation is something like:

[person] - (having)->[user accounts]

[user account]-(post)->[message]-(to)->[recepients' account]

[recepient's account]-(belongs to)->[user]

So want to know who sent messages in certain time to X type of user and how many times. Wants to know the first and last date. The sender belongs to Y domain.

person.domain = 'Y'

message.sent_date between D1 and D2

user.type = 'hacker' 

I need it in SQL.

Any suggestion is very appreciated!

Thank you in advance!

L.

[Andrey Yesyev]

Hi there,

I have the similar problems. I solved them using Gremlin-Groovy.

I doubt you can do what you need using SQL.


-Andrey

[László Rékasi]

Hi,

Thank you!
Thought that it could not be easy :-) Meantime realized that there are nodes and egdes in chain :-)  but not projected result sets like in rdbms world.

Unfortunately I am not a developer, but a guy in business side with some it skill, so somehow I need to make it with out of the box solution to proof that it worth to spend time on it. So maybe javascript function can be the answer somehow.

@Andrey, could you please share your use case and solution with me? Orientdb lacks of complete, step by step tutorial, or at least I did not find too many.

Thx!
L.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/IKbefI2QA3M/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[László Rékasi]

Hi,

On the way heading to the office I have realised that I need hyperedges to be detected and created based on custom conditions, let's say, business rules.

Did anyone play already with hyperedge in OrientDB?

Thx!

L.

 

[Andrey Yesyev]

Hi László,

I found this site is very useful http://gremlindocs.com/

This is how I look for all paths between 2 vertices, with rids #21:7 and #16:10

g.v('#21:7').as('person').both('RELATED').loop('person'){it.object.id.toString() != '#16:10' && it.loops < 4}.filter{it.id.toString()=='#16:10'}.path()

This is how java code looks like

        OrientGraph graph = DatabaseManager.getInstance().getDatabase("remote:192.168.200.128/ePersona").getConnection();
        Pipe pipe = Gremlin.compile("_().as('person').both('RELATED').loop('person'){it.object.id.toString() != '#16:10' && it.loops < 4}.filter{it.id.toString()=='#16:10'}.path()[10..20]");
        pipe.setStarts(new SingleIterator<Vertex>(graph.getVertex("#21:7")));
        Merger m = new Merger();
        Path paths = m.mergePathsResults(pipe);
        for(Object o : pipe) {
            ArrayList<Vertex> path = (ArrayList<Vertex>)o;
            for(Vertex v:path){
                System.out.print("->" + v.getProperty("name") + "[" + v.getProperty("@class") + "]");
            }
            System.out.println();
        }

example with weights you can find on gremlindocs site.

-Andrey 

[László Rékasi]

Hi Andrew'

It's something useful to play with :-)
Meantime I have started to get it work in javascript function.
May I know if you have faced to performance problems? My friends told me that it was not easy to sort it out with gremlin on a bitcoin database. How much data you deal with?
I have approx:
- vA: 1 million
- vB: 2 million
- vC: around 200-300 million
- vD: 2 million
- vE: 200 k
And having few other class of vertices, 1 million each.

I think it still not "big data", but something I can manage on a commodity oracle server. But it is not good for realtime recommender engine, although the performance is pretty good :-)

Thx!
L.

[László Rékasi]

Did something like this, wich seems to be working (at least for me :-) ):

 

In my example the lowest level of nodes is "vC". Let's say it is the LOG of actions taken by the users, so it is a link-like node between user accounts and sites. (This is not the real use-case, but that one is not public). And it has the most elements.

 

select max(in('eCD').Field_Of_vD), vC.Field_1, vC.Field_2  from (select expand(in(eCD)) from vD) group by vC.Field_A, vC.field_B

 

In my example there is only 1 item attached as IN(class of vB) and OUT(class of vD) attached to vC.

One can argue that vertice vC should be an edge, but there are more class of nodes linked to vC.

[Andrey Yesyev]

Hi László,

Due to nature of my data I don't and won't have that amount of vertices as you have.

I don't have any problem with performance so far.

-Andrey