Selecting parent records by filtering multiple fields of collection of links

Question

I have been trying to figure out this for a couple of days know but I can't come up with a query that gives me the correct results. The essence of the task is that I am trying to retrieve all the nodes of a graph that have children with attributes that satisfy multiple constraints. The issue I have is that a node may have multiple linked nodes and when I try to apply criteria to restrict which nodes must be returned by the query the criteria need to be imposed against sets of nodes instead of individual nodes.

Let me explain the problem in more detail through an example. Here is a sample schema of companies and locations. Each company can have multiple locations.

create class company extends V;
create property company.name STRING;

create class location extends V;
create property location.name STRING;
create property location.type INTEGER;
create property location.inactive STRING;

Let me now create a couple of records to illustrate the problem I have.

create vertex company set name = 'Company1';
create vertex location set name = 'Location1', type = 5;
create vertex location set name = 'Location2', type = 7;
create edge from (select from company where name = 'Company1') to (select from location where name in ['Location1', 'Location2']);

create vertex company set name = 'Company2';
create vertex location set name = 'Location3', type = 6;
create vertex location set name = 'Location4', type = 5, inactive = 'Y';
create edge from (select from company where name = 'Company2') to (select from location where name in ['Location3','Location4']);

I want to retrieve all companies that either don't have a location of type 5 or have a location of type 5 that is inactive (inactive = 'Y'). The query that I tried initially is shown below. It doesn't work because the $loc.type is evaluated against a collection of values instead of a individual record so the is null is not applied against the individual field 'inactive' of each location record but against the collection of values of the field 'inactive' for each parent record. I tried sub-queries, the set function, append and so on but I can't get it to work.

select from company let loc = out() where $loc.type not contains 5 or ($loc.type contains 5 and $loc.type is null)

Answer 1

You can try with this query:

select expand($c)
let $a = ( select expand(out) from E where out.@class = "company" and in.@class="location" and in.type = 5 and in.inactive = "Y" ),
    $b = ( select from company where 5 not in out("E").type ),
    $c = unionAll($a,$b)

UPDATE

I have created this graph

You can use this query

select expand($f)
let $a = ( select from E where out.@class = "company" and in.@class="location" ),
    $b = ( select expand(out) from $a where in.type = 5 and in.inactive = "Y"),
    $c = ( select expand(out) from $a where in.type = 5 and in.inactive is null),
    $d = difference($b,$c),
    $e =  ( select from company where 5 not in out("E").type ),
    $f = unionAll($d,$e)

Hope it helps.