How to sort vertices according to edge types OrientDb?

Hello,

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;

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

I can achieve this in SQL with a pivot (see below) but I can’t wrap my head around how to transpose 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;

Any ideas?

Thanks!