How to write a query that filters traversed elements

I'm struggling with the following query. For a family tree database, I have a vertex 'Person' and a lightweight edge 'Child', so the edge would go out of a parent and into a child (ie 'child-of'). From a person, I need to get their siblings who share the exact same parents.

I can get all of a persons siblings fairly easy, as follows;

SELECT 
FROM (
    TRAVERSE out_Child
    FROM (
        SELECT expand(in_Child)
        FROM #11:3
    )
    WHILE $depth <= 1
)
WHERE $depth = 1

So this gets the parents of the person in question, then gets all the children of the parents. The results might look like the following

@rid    in_Child
#11:2  #11:0
#11:3  #11:0, #11:1
#11:4  #11:0, #11:1
#11:5  #11:1

I need to filter these results though, as I only want records that have the exact same parents as #11:3. So in this instance, the query should only return #11:3 and #11:4. If the query were for #11:5, it should return #11:5 only. So basically, the in_Child fields must be the same.

I've tried all sorts of queries such as the following, but the query either doesnt run or doesnt filter.

SELECT
FROM (
    SELECT 
    FROM (
        TRAVERSE out_Child
        FROM (
            SELECT expand(in_Child)
            FROM #11:3
        )
        WHILE $depth <= 1
    )
    WHERE $depth = 1
)
LET $testinChild = (SELECT expand(in_Child) FROM #11:3)
WHERE in_Child CONTAINSALL $testinChild

Ultimately I would prefer to not do any sub-queries, but if it's required then so be it. I Also tried to use traversedElement(0) function, but it only returns the first record traversed (ie #11:0, but not #11:1), so it can't be used.

Update; If you copy-paste the following into orientdb console (change the password etc to suit your setup), you will have the same dataset described above.

create database remote:localhost/persondb root pass memory graph

alter database custom useLightweightEdges=true

create class Person extends V
create property Person.name string
create class Child extends E

create vertex Person set name = "Father"
create vertex Person set name = "Mother"

create vertex Person set name = "Child of father only"
create edge Child from #11:0 to #11:2

create vertex Person set name = "Child of father+mother #1"
create edge Child from #11:0 to #11:3
create edge Child from #11:1 to #11:3
create vertex Person set name = "Child of father+mother #2"
create edge Child from #11:0 to #11:4
create edge Child from #11:1 to #11:4

create vertex Person set name = "Child of mother only"
create edge Child from #11:1 to #11:5

Solution

Okay, I've found some solutions.

First of all, the way I used CONTAINSALL in the question is not correct, as pointed out to me here. CONTAINSALL does not check that all the items on the 'right' are in the 'left', but actually loops over each item in the 'left' and uses that item in the expression on the 'right'. SO WHERE in_Child CONTAINSALL (sex = 'Male) will filter for records where all of the in_Child records are only Male (ie no females). It's basically checking that in_Child[0:n].sex = 'Male'.

So I tried this query;

SELECT
FROM (
    SELECT 
    FROM (
        TRAVERSE
            out('Child')
        FROM (
            SELECT
                expand(in('Child'))
            FROM
                #11:3
        )
        WHILE
            $depth <= 1
    )
    WHERE
        $depth = 1
)
WHERE
    (SELECT expand(in('Child')) from #11:3) CONTAINSALL (@rid in $current.in_Child)

I think OrientDB might have a bug here. The above query return #11:2, #11:3 and #11:4, which doesn't make sense to me. I changed this query slightly...

SELECT
FROM (
    SELECT 
    FROM (
        TRAVERSE
            out('Child')
        FROM (
            SELECT
                expand(in('Child'))
            FROM
                #11:3
        )
        WHILE
            $depth <= 1
    )
    WHERE
        $depth = 1
)
LET
    $parents = (SELECT expand(in('Child')) from #11:3)
WHERE
    $parents CONTAINSALL (@rid in $current.in_Child)

This works better. The above query correctly returns #11:3 and #11:4, but a query on #11:2 or #11:5 also incorrectly includes both #11:3 and #11:4. This makes sense, because it checking the parent rids of eg #11:2 (which is only 1) is in the parents of the rest, which they are. So I added a check to ensure they had the same amount of parents.

SELECT
FROM (
    SELECT 
    FROM (
        TRAVERSE
            out('Child')
        FROM (
            SELECT
                expand(in('Child'))
            FROM
                #11:3
        )
        WHILE
            $depth <= 1
    )
    WHERE
        $depth = 1
)
LET
    $parents = (SELECT expand(in('Child')) from #11:3)
WHERE
    $parents CONTAINSALL (@rid in $current.in_Child)
    AND
    $parents.size() = in('Child').size()

Now the query is working correctly for all instances. However, I still wasn't happy with this query. I abandonned the use of CONTAINSALL and eventually came up with the following...

SELECT
FROM (
    SELECT 
    FROM (
        TRAVERSE
            out('Child')
        FROM (
            SELECT
                expand(in('Child'))
            FROM
                #11:3
        )
        WHILE
            $depth <= 1
    )
    WHERE
        $depth = 1
)
LET
    $parents = (SELECT expand(in('Child')) from #11:3)
WHERE
    in_Child.asSet() = $parents.asSet()

This appears the best/safest, and is the one I will use.