How to sort vertices according to edge types OrientDb?

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.

So for instance, if Container1 links to Item1 and Item2 with High and Low priorities according to the specified PriorityMapping, then its "compound"/overall priority is High and this is what is used for this Container when sorting.

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)

(This is roughly equivalent to the following in SQL)

select distinct f.container_id, f.overall_priority
from (
    select c.id as container_id, ci.item_id, cp.overall_priority
    from container c 
    left join container_items ci 
    on c.id = ci.container_id 
    left join (
        select c.id as container_id, max (coalesce (ip.priority, 0)) as overall_priority 
        from container c
        left join container_items ci 
        on c.id = ci.container_id 
        left join (
          select item_id,
            case 
              when mi.priority = 'HIGH' then 3
              when mi.priority = 'MEDIUM' then 2
              when mi.priority = 'LOW' then 1
            end priority
          from parent p, mapping_items mi 
          where p.id = TARGET_ID
          and p.mapping_id = mi.mapping_id
        ) as ip
        on ci.item_id = ip.item_id
        where c.parent_id = TARGET_ID
        group by c.id
    ) as cp 
    on cp.container_id = c.id
    where c.parent_id = TARGET_ID
) as f
order by f.overall_priority desc;

or a weighted sort using a pivot:

select c.*
from container c
inner join (
    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 (
        select c.id as container_id, ci.item_id, mappings.priority 
        from container c 
        left join container_items ci 
        on c.id = ci.container_id 
        left join (
            select mi.item_id, mi.priority 
            from parent p
            inner join "mapping" m 
            on p.mapping_id = m.id 
            inner join mapping_items mi 
            on mi.mapping_id = m.id 
            where p.id = '#98:0'
        ) as mappings
        on mappings.item_id = ci.item_id
        where c.parent_id = '#98:0'
    ) as b
    group by container_id
) as something
on something.container_id = c.id
where c.parent_id = '#98:0'
order by something.high desc, something.medium desc, something.low desc;

Thanks in advance!

from Recent Questions - Stack Overflow https://ift.tt/3nE4pul
https://ift.tt/3qHSjCb