[ad_1]
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!
[ad_2]