Can't select data from connected document

[Borov]

Hi guys, I posted this question on stackoverflow last week, but didn't get an answer, so I decided to re-posted it here.

Using OrientDB v2.2.12. I'm trying to execute a simple sql query to retrieve data from the document searched as well as few properties of a connected document. But, for some reason ran into a dead wall on how to select the property from the connected document. Here is my simple scenario:

I have 2 documents, an Account and a User. Account document has an edge to a User named 'Employs'. I'm trying to login a user by email and password. If record for the user is found, I simply need to get some user data from the User class (works fine) and few properties from an account document (doesn't work right) to be stored in user's session.

Here is my query:

try (ODatabaseDocumentTx db = DbPool.getConnection()) {
    String sql
            = " select @rid.asString() as userRid, firstName, lastName, "
            + "     active as userActive, in('Employs') as account "
            + "from User "
            + "where email = ? and password = ?";

    OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<>(sql);
    List<ODocument> result = db.command(query).execute('te...@test.com', 'abc');

    ODocument d = result.get(0);

    System.out.println("1 "+ d.field("account.id"));      // <-- fails here
    System.out.println("2 "+ d.field("userRid"));
    System.out.println("3 "+ d.field("account.company")); // <-- fails here
    System.out.println("4 "+ d.field("firstName"));
    System.out.println("5 "+ d.field("lastName"));
    System.out.println("6 "+ d.field("account.active"));  // <-- fails here
    System.out.println("7 "+ d.field("userActive"));

    return new SessionUser(
            d.field("account.id"), 
            d.field("userRid"), 
            d.field("account.company"),
            d.field("firstName"), 
            d.field("lastName"),
            d.field("account.active"),
            d.field("userActive"));
}

It fails to create the SessionUser object. More specifically, it fails on retrieval of the account properties. Here is how the data looks in System.out:

1 [17]
2 #37:0
3 [Seller 1]
4 Mike
5 Maloney
6 [true]
7 true
WARN : 2016-11-21 17:53:53,036 WARN c.t.e.k.c.ExceptionHandler - Error: java.util.LinkedHashSet cannot be cast to java.lang.Integer

Here is how a single account.id property looks like in the debugger

I do see that the account properties are coming in as objects, just can't figure out how to select them easy. I didn't want to go through manually casting list then set, etc. to get to a simple account.id element. I'm sure there is a straight forward way, just can't see it. For example, if I can't use this d.field("account.id") then how do I get the ID 17?

What is the right way to select data of a connected document? Or, is there a better way to construct the query itself?

Thanks.

[Ivan Mainetti]

Hi,

in('Employs') returns a list, if you know that it can be only one document in it you can try modifying your query adding [0]

String sql
            = " select @rid.asString() as userRid, firstName, lastName, "

            + "     active as userActive, in('Employs')[0] as account "

            + "from User "
            + "where email = ? and password = ?";

EDIT

Alternatively you can change a little your code as follow (I've simplified the query just for testing):

                String dbName = "stack40732762";
                
                OrientGraphFactory dbfactory = new OrientGraphFactory("remote:127.0.0.1:2424/"+dbName, "root", "root").setupPool(1, 50);
                
                OrientGraph db = dbfactory.getTx();
//              ODatabaseDocumentTx db = DbPool.getConnection()
                
                try {
                    String query = "select name, in('Employs')[0] as account from user where name = ?";
//
//                  OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<>(sql);
//                  List<ODocument> result = db.command(query).execute();
                    Iterable<OrientVertex> result = db.command(new OCommandSQL(query)).execute("pippo");
                    
                    while(result.iterator().hasNext()){
                        OrientVertex d = result.iterator().next();
                        
//                                          System.out.println("1 "+ d.field("account.id"));      // <-- fails here
                                            System.out.println("1 " + d.getProperty("account.id"));  // <-- this works!
//                                          System.out.println("2 "+ d.field("name"));
                                            System.out.println("2 " + d.getProperty("name"));
                    }
                    

                } catch (Exception e) {
                        // TODO: handle exception
                        System.out.println(e);
                }

Bye,

Ivan

Il giorno lunedì 28 novembre 2016 21:09:14 UTC+1, Borov ha scritto:

Hi guys, I posted this question on stackoverflow last week, but didn't get an answer, so I decided to re-posted it here.

Using OrientDB v2.2.12. I'm trying to execute a simple sql query to retrieve data from the document searched as well as few properties of a connected document. But, for some reason ran into a dead wall on how to select the property from the connected document. Here is my simple scenario:

I have 2 documents, an Account and a User. Account document has an edge to a User named 'Employs'. I'm trying to login a user by email and password. If record for the user is found, I simply need to get some user data from the User class (works fine) and few properties from an account document (doesn't work right) to be stored in user's session.

Here is my query:

try (ODatabaseDocumentTx db = DbPool.getConnection()) {
    String sql
            = " select @rid.asString() as userRid, firstName, lastName, "
            + "     active as userActive, in('Employs') as account "
            + "from User "
            + "where email = ? and password = ?";

    OSQLSynchQuery<ODocument> query = new OSQLSynchQuery<>(sql);
    List<ODocument> result = db.command(query).execute('test...@test.com', 'abc');

    ODocument d = result.get(0);

    System.out.println("1 "+ d.field("account.id"));      // <-- fails here
    System.out.println("2 "+ d.field("userRid"));
    System.out.println("3 "+ d.field("account.company")); // <-- fails here
    System.out.println("4 "+ d.field("firstName"));
    System.out.println("5 "+ d.field("lastName"));
    System.out.println("6 "+ d.field("account.active"));  // <-- fails here
    System.out.println("7 "+ d.field("userActive"));

    return new SessionUser(
            d.field("account.id"), 
            d.field("userRid"), 
            d.field("account.company"),
            d.field("firstName"), 
            d.field("lastName"),
            d.field("account.active"),
            d.field("userActive"));
}

[Borov]

Thanks Ivan. So simple to just add [0]. 

As a suggestion to the OrientDB doc writers - it would be nice to have few cases like these documented in the manual for new users.

[Borov]

One more question,

What is the way to get account @rid from this field "in('Employs')[0] as account"? I can get all fields, but not the @rid.

On Monday, November 28, 2016 at 2:42:13 PM UTC-8, Ivan Mainetti wrote:

[Ivan Mainetti]

using getProperty, like in my example code, you can do 

System.out.println("3 " + d.getProperty("account.@rid"));

I don't know if it works even with field() method

[Borov]

Hi Ivan,

In my original logic "d" is of type ODocument (http://orientdb.com/javadoc/2.2.x/com/orientechnologies/orient/core/record/impl/ODocument.html) and it doesn't have .getProperty(...) method.

Can I get the account.@rid from the SQL API and not the Graph API?

[Ivan Mainetti]

Did you try with one of these?

d.field("account.@rid")

d.field("account")

[Borov]

I did, but it doesn't work. It pulls the entire 'account' object.

System.out.println("acc rid = "+ d.field("account.@rid"));
System.out.println("acc rid = "+ d.field("account"));

acc rid = Account#25:0{...}
acc rid = Account#25:0{...}

[Ivan Mainetti]

well, getProperty returns something very similar actually

System.out.println("3 " + d.getProperty("account.@rid"));

System.out.println("3 " + d.getProperty("account"));

3 v(Account)[#25:0]

3 v(Account)[#25:0]

But! I've found this!

String query = "select name, in('Employs')[0] as account, in('Employs')[0].asString() as accRid from user where name = ?";

System.out.println("3 " + d.getProperty("accRid"));
//System.out.println("3 " + d.field("accRid"));

--
3 #25:0

[Borov]

In case of the SQL query, which I'm using in this case, for your working solution it will traverse the 'in' connection twice I think.  When we get the account object via in('Employs')[0] as account we get the full object with the @rid so I find it limiting that Orient doesn't allow to retrieve rid from it.

Is there a way to get it in a single traversal or is it just not possible? The reason its important for us because we'll have other similar queries that can go deeper in traversals and really need to avoid the multiple traversals.

[Ivan Mainetti]

I'm afraid there's not a direct way to get it. You may consider of manipulate the output string from d.field("account")     Account#25:0{...} in order to extract the rid

[Borov]

That's what I was trying to avoid :)

Thanks for looking into this Ivan.