Query on OrientDB graph

Question

I am building a chat room engine and I have a graph like so:

User---Started--->Room----Discuss--->Item<---Own---User

I would like to get all the Items and the roomId associated to the Item for a particular User.

select 
    *, in('Discuss').in('Started')[name ='julie'] as roomId
    from item

so this is going threw the right 'Room' and finds the one started by 'Julie' but it returns Julie's Id, how do I get with this query the Id of that room ? It's like i need to do a 'one back' and get @rid...

I am new to graphs so any pointers would be appreciated.

Answer 1

This answer is based upon option (1) from my comment to the original question, as you indicated here that "I would like to get all the Items not only the ones that have a room starter by julie".

SELECT *, $rooms_by_user FROM items LET $rooms_by_user = ( SELECT FROM ( SELECT expand(in('Discuss')) FROM $parent.$current ) WHERE in('Started').Name contains 'Julie' )

So the original query is getting all of the items, and is adding a custom field to the output (the LET clause and $rooms_by_user field).

The inner query of $rooms_by_user is expanding all off the 'Discuss' edges of each 'Item' (this runs per item), thus returning all of the 'Room' vertexes associated with the 'Item'. The outer part of the query then filters the 'Room' vertexes for only those started by 'User' with name 'Julie'.

I had to use contains in the outer query as in('Started').Name returns a list, eg ["Julie"].

It would probably be better to filter the user via rid, then you only have to filter on the out property of the 'Started' edge (ie the database would have only have to 'jump' once from vertex to edge, not from vertex to edge to vertex - this is only true if you aren't using lightweight edges though, which is the default setting in the current version.).

Answer 2

Assuming your graph has vertex and edge classes, and is of a similar structure to:

You can use a combination of select and the graph traverse query to return what you need. For example:

    select from (traverse both('discuss'), both('started') from #14:0)
    where (@class='room') 
    or (@class='user' and name='Julie')

This will return the following result (JSON format)

{
"result": [
    {
        "@type": "d",
        "@rid": "#13:0",
        "@version": 3,
        "@class": "room",
        "name": "Baking",
        "in_started": [
            "#15:0"
        ],
        "out_discuss": [
            "#16:0"
        ],
        "@fieldTypes": "in_started=g,out_discuss=g"
    },
    {
        "@type": "d",
        "@rid": "#12:0",
        "@version": 2,
        "@class": "user",
        "name": "Julie",
        "out_started": [
            "#15:0"
        ],
        "@fieldTypes": "out_started=g"
    }
],
"notification": "Query executed in 0.027 sec. Returned 2 record(s)"

}

Update:

If you only wanted to return the @rid of the room you can wrap the above query with another select:

select @rid from
    (select from (traverse both('discuss'), both('started') from #14:0)
    where (@class='room') 
    or (@class='user' and name='Julie'))
where @class='room'