CODEWITHSUNDEEP
sqlorientdb
G G
G G

Reputation: 1664

OrientDB Select on two related vertex

I have a scenario as shown below enter image description here,

I want to query the database so I get the following result,

User    Resource      Permissions

Edi     Plan A        [view]

Where

resource.name = 'Plan A' and user.name = 'Edi'

my query for above is 

SELECT name, 
       out('hasARole').out('ofType').in('isOfType')[name = 'Plan A'].name, 
       set(out('hasARole').out('hasA').name) as permission 
FROM user 
WHERE name = 'Edi'

It should display 

User    Resource      Permissions

Adrian   Plan A        [view,edit, delete]

if I change it to,

Where

resource.name = 'Plan A' and user.name = 'Adrian'

my query for above is

SELECT name, 
       out('hasARole').out('ofType').in('isOfType')[name = 'Plan A'].name, 
       set(out('hasARole').out('hasA').name) as permission 
FROM user 
WHERE name = 'Adrian'

Now above queries work as long as the users don't have another role on another type of resource. e.g. if Edi had Admin role on let's say a resource type of Workspace then the query gives me back all the permissions that an Admin would have , instead of just view as he only has view permission on Plan A

Upvotes: 0

Views: 392

Answers (1)

neRok
neRok

Reputation: 1005

I have used the following graph for my answer. Note that I have corrected some incositencies with your original edges.

Tweaked Graph

I see a number of possible queries for this problem. I am a bit confused why you would want to return the User and Resource in the query, as you probably already have these records due to the fact you use them to create the query. You can't 'nest' the full records in the results either (unless you JSON them). Further to this, querying on the name field, and returning only the name field seem a little nonsensical to me - but maybe you have done so to simplify the question. Regardless, the following queries will get you on your way to your desired results.

My first idea is to run a query to get all of the Roles related to a Resource. We then run a query over these results to filter for the Roles that include the User. This looks like the following;

select from (
    select expand(out('isOfType').in('ofType')) from Resource where name = "Plan A"
) where in('hasARole') contains first((select from User where name = "Edi"))

This query correctly returns just the Viewer record for both Edi and Adrian.

My second idea is to run 1 query for the Roles related to a Resource (similar to above), and another for the Roles related to a User, and then find the intersect. This looks like the following, and gives the same results as the query above;

select expand(intersect) from (
    select intersect($resource_roles, $user_roles)
    let
    $resource_roles = (select expand(out('isOfType').in('ofType')) from Resource where name = "Plan A"),
    $user_roles = (select expand(out('hasARole')) from User where name = "Edi")
)

Now if you really do want the User, Resource and Permissions all in the 1 result, you can use the following, or a variant of;

select first($user).name as User, first($resource).name as Resource, intersect(resource_roles,     user_roles).name as Permissions from (
    select $resource.out('isOfType').in('ofType') as resource_roles, $user.out('hasARole') as user_roles
    let
    $resource = (select from Resource where name = "Plan A"),
    $user = (select from User where name = "Edi")
)

or

select first($user).name as User, first($resource).name as Resource, intersect($resource_roles, $user_roles).name as Permissions
let
$resource = (select from Resource where name = "Plan A"),
$resource_roles = (select expand(out('isOfType').in('ofType')) from $parent.$resource),
$user = (select from User where name = "Edi"),
$user_roles = (select expand(out('hasARole')) from $parent.$user)

Upvotes: 2

Related Questions