Orientdb: fetchPlan returning extra properties

[neridavide]

I'm using OrientDB v.2.1.1 with useLightweightEdges property set to true.
Using fetchPlan queries often return extra properties non included in the query or even some of properties excluded in more ways.

Here is an example:

Given this simple structure

User    V     #13
Event   V     #14
Org     V     #15
Media   V     #16

admin   E     User  --> Org
follow  E     User  --> Org
author  E     Event --> Org
avatar  E     Org   --> Media
thumb   E     Event --> Media

I need to write a query that fetches a specific Event, its relative thumb, author and the author's avatar. Nothing else.

1st query:

SELECT @this.toJSON('rid,class,fetchPlan:[*]in_*:-2 [*]out_*:-2 out_thumb:0 out_author:0 out_author.out_avatar:0') FROM Event WHERE @rid = #14:8

Returns:

{
    "@rid": "#14:8",
    "@class": "Event",
    "name": "EVENT_TEST",
    "out_author": [
        {
            "@rid": "#15:0",
            "@class": "Org",
            "name": "ORG_TEST",
            ! "in_admin": [ "#13:0", "#13:5", "#13:6", "#13:9", "#13:10", "#13:13" ],
            ! "in_follow": [ "#13:0", "#13:4", "#13:5", "#13:6", "#13:14", "#13:15" ],
            ! "in_author": [ "#14:6", "#14:7", "#14:8", "#14:9", "#14:10", "#14:11" ],
            "out_avatar": [
                {
                    "@rid": "#16:2",
                    "@class": "Media",
                    "url": "www.image.com",
                    ! "in_thumb": [ "#14:4", "#14:6", "#14:7", "#14:12", "#14:14", "#14:15" ],
                    ! "in_avatar": [ "#15:0" ]
                }
            ]
        }
    ],
    "out_thumb": [
        {
            "@rid": "#16:1",
            "@class": "Media",
            "url": "www.image.com",
            ! "in_thumb": [ "#14:2", "#14:5", "#14:8", "#14:9", "#14:10", "#14:13" ],
            ! "in_avatar": [ "#15:2" ]
        }
    ]
}

Where these properties should have been excluded:
out_author.in_admin
out_author.in_follow
out_author.in_author
out_author.out_avatar.in_thumb
out_author.out_avatar.in_avatar
out_thumb.in_thumb
out_thumb.in_avatar

2nd query:

_made by adding some parameters to the 1st query to explicitly exclude the extra properties_

SELECT @this.toJSON('rid,class,fetchPlan:[*]in_*:-2 [*]out_*:-2 out_thumb:0 out_author:0 out_author.out_avatar:0 [*]in_avatar:-2 [*]in_thumb:-2 [*]in_author:-2 [*]in_admin:-2 [*]in_follow:-2') FROM Event WHERE @rid = #14:8

Returns

{
    "@rid": "#14:8",
    "@class": "Event",
    "name": "EVENT_TEST",
    "out_author": [
        {
            "@rid": "#15:0",
            "@class": "Org",
            "name": "ORG_TEST",
            ! "in_admin": [ "#13:0", "#13:5", "#13:6", "#13:9", "#13:10", "#13:13" ],
            ! "in_follow": [ "#13:0", "#13:4", "#13:5", "#13:6", "#13:14", "#13:15" ],
            "out_avatar": [
                {
                    "@rid": "#16:2",
                    "@class": "Media",
                    "url": "www.image.com"
                }
            ]
        }
    ],
    "out_thumb": [
        {
            "@rid": "#16:1",
            "@class": "Media",
            "url": "www.image.com"
        }
    ]
}

Partially works since removes
out_author.in_author
out_author.out_avatar.in_thumb
out_author.out_avatar.in_avatar
out_thumb.in_thumb
out_thumb.in_avatar

But it doesn't remove
out_author.in_admin
out_author.in_follow

3nd query:

_made by adding some parameters to the 2st query to even more explicitly exclude the extra properties remained_

SELECT @this.toJSON('rid,class,fetchPlan:[*]in_*:-2 [*]out_*:-2 out_thumb:0 out_author:0 out_author.out_avatar:0 [*]in_avatar:-2 [*]in_thumb:-2 [*]in_author:-2 [*]in_admin:-2 [*]in_follow:-2 out_author.in_admin:-2 out_author.in_follow:-2') FROM Event WHERE @rid = #14:8

Returns:

// The same as for 2nd query 

[micha-nerdlichter]

I don't understand why you are using the toJSON-function, this looks so complicated... why not use the outE()-function with include()?

select outE("thumb").include("url","type","size"), outE("author").include("name"), outE("author").outE("avatar").include("url") from #14:8

Btw if you replace the ... from Event where @rid with select ... from #14:8 your query is faster.

[micha-nerdlichter]

...even nicer: use "as" to rename the edges:

select
  outE("thumb").include("url","type","size") as thumb,
  outE("author").include("name") as author,
  outE("author").outE("avatar").include("url") as avatar
from #13:0

[nightrise]

I'm also encountering a very similar issue.

The documentation states in one of the examples given:

[*]in_*:-2 out_*:-2 Fetches all properties, except for edges at any level.
http://orientdb.com/docs/2.1/Fetching-Strategies.html

Unfortunately this does not seem to work properly. I have a simple graph with:

Player (vertex)
Location (vertex)
PresentIn (edge, Player -> Location)
FriendOf (edge, Player <-> Player)

Bob -> FriendOf -> John
Harry -> FriendOf -> Bob
Bob -> PresentIn -> New York

What I'd like to do is simply retrieve a shallow graph for Bob which contains all of Bob's immediately adjacent vertices and their properties, but not their edges.

Therefore I use the following query:

select * from Player where id = 1 fetchplan *:2 [2]out_*:-2 [2]in_*:-2

What I expected to get was:

• Player node who's id = 1 (Bob)
• Bob's outgoing and incoming edges and their corresponding vertices, which would be, John, Harry and New York
• All of the properties associates with the above
• None of the edges John, Harry or New York might have with each other or other vertices

What I instead got was the above, but no properties and no edges. I've tried playing around with it, but the only thing I managed to do is include specific properties (name in this case) by using the following query:

select * from Player where gamer_tag = 1 fetchplan *:2 [2]out_*:-2 [2]in_*:-2 [2]name:0

I should note that the above is directly inspired from the documentation and that I tried this on the latest version of OrientDB (2.1.6).

[luigidellaquila]

The FetchPlans are being deprecated in v 3.0 in favor of nested projections, see http://orientdb.com/docs/3.0.x/sql/SQL-Projections.html#nested-projections

[lightjiao]

Hi @luigidellaquila
Is there any document to list the difference between 3.0 and 2.2.x ? especially deprecated feature!

[luigidellaquila]

Hi @lightjiao

This should be of help (WIP):

http://orientdb.com/docs/3.0.x/release/3.0/SQL-Changes.html
http://orientdb.com/docs/3.0.x/release/3.0/API-Changes.html
http://orientdb.com/docs/3.0.x/release/3.0/Upgrading-to-OrientDB-3.0.html

Thanks

Luigi

[lightjiao]

@luigidellaquila
Thank you very much