I’m quite new to OrientDb and I’m trying to figure out how to sort vertices according to a more complex schema than I am accustomed to…

In my (simplified for StackOverflow) database, I have a bunch of Containers. Each Container can be associated with any number of Items. There are also PriorityMappings, each of which specifies a Priority level (High, Medium, Low) per existing Item.

What I’d like to do is to retrieve a list of Containers sorted by the highest Priority of all of the associated Items according to a specified PriorityMapping.

My example schema is as follows:

CREATE CLASS VItem EXTENDS V;
CREATE CLASS VContainer EXTENDS V;
CREATE CLASS VPriorityMappings EXTENDS V;

CREATE CLASS EItem EXTENDS E;
CREATE CLASS EItemPriority EXTENDS E;
CREATE CLASS EItemPriorityLow EXTENDS EItemPriority;
CREATE CLASS EItemPriorityMedium EXTENDS EItemPriority;
CREATE CLASS EItemPriorityHigh EXTENDS EItemPriority;

CREATE VERTEX VItem SET Name = "Item 1"; // @rid #34:0
CREATE VERTEX VItem SET Name = "Item 2"; // @rid #35:0
CREATE VERTEX VItem SET Name = "Item 3"; // @rid #36:0
CREATE VERTEX VItem SET Name = "Item 4"; // @rid  #37:0

CREATE VERTEX VPriorityMappings SET Name = "Priority Mappings 1"; // @rid #50:0

CREATE EDGE EItemPriorityLow FROM #50:0 TO #34:0;
CREATE EDGE EItemPriorityLow FROM #50:0 TO #35:0;
CREATE EDGE EItemPriorityMedium FROM #50:0 TO #36:0;
CREATE EDGE EItemPriorityHigh FROM #50:0 TO #37:0;

CREATE VERTEX VContainer SET Name = "Container 1", Count=2; // @rid #42:0
CREATE VERTEX VContainer SET Name = "Container 2", Count=1; // @rid #43:0
CREATE VERTEX VContainer SET Name = "Container 3", Count=5; // @rid #44:0
CREATE VERTEX VContainer SET Name = "Container 4", Count=0; // @rid #45:0
CREATE VERTEX VContainer SET Name = "Container 5", Count=3; // @rid #46:0

// -- No items for Container 1
// -- Container2 > Item1(Low) & Item4(High)

CREATE EDGE EItem FROM #43:0 TO #34:0;
CREATE EDGE EItem FROM #43:0 TO #37:0;

// -- Container3 > Item1(Low) & Item3(Medium)
CREATE EDGE EItem FROM #44:0 TO #34:0;
CREATE EDGE EItem FROM #44:0 TO #36:0;

// -- Container4 > Item2(Low)
CREATE EDGE EItem FROM #45:0 TO #35:0;

// -- Container5 > Item3(Medium) & Item4(High)
CREATE EDGE EItem FROM #46:0 TO #36:0;
CREATE EDGE EItem FROM #46:0 TO #37:0;

and here it is in graph form :

So in this example, the desired outcome would be Containers 2(High), 5(High), 3(Medium), 4(Low), 1(No Items)

I could achieve this as follows using SQL but I can’t wrap my head around transposing this to OrientDB.

-- 4/ join calculated overall container priorities, use for ordering
select c.*
from container c
inner join (
    -- 3/ create pivot tabe of sum of each priority level per container 
    select container_id,
        count(case when priority = 'HIGH' then 1 end) as high,
        count(case when priority = 'MEDIUM' then 1 end) as medium,
        count(case when priority = 'LOW' then 1 end) as low
    from (
        -- 2/ apply priorities to all Container Items
        select c.id as container_id, ci.item_id, item_priorities.priority 
        from container c 
        left join container_items ci 
        on c.id = ci.container_id 
        left join (
            -- 1/ determine priorities for each Item according to specified priority mapping
            select mi.item_id, mi.priority 
            from "mapping" m  
            inner join mapping_items mi 
            on mi.mapping_id = m.id 
            where m.id = '#50:0'
        ) as item_priorities
        on item_priorities.item_id = ci.item_id
    ) as container_item_priorities
    group by container_id   
) as container_priorities
on container_priorities.container_id = c.id
order by container_priorities.high desc, container_priorities.medium desc, container_priorities.low desc;

Thanks in advance!