How to sort vertices according to edge types OrientDb?

Question

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!

user_3240126 · Answer

Would this query be useful to you?

SELECT *, !highPriorityItemCount, !mediumPriorityItemCount, !lowPriorityItemCount FROM (
    SELECT 
        *, 
        out("EItem").in("EItemPriorityHigh").size() as highPriorityItemCount,
        out("EItem").in("EItemPriorityMedium").size() as mediumPriorityItemCount,
        out("EItem").in("EItemPriorityLow").size() as lowPriorityItemCount
    FROM VContainer
    ORDER BY 
        highPriorityItemCount DESC,
        mediumPriorityItemCount DESC,
        lowPriorityItemCount DESC
)

It returns VContainer instances in order depending on:

How many high priority items it has
How many medium priority items it has
How many low priority items it has

I don't know the background of your model, but it might be easier to let the priority levels be their own Vertices and instead use some kind of HasPriority edge which can be linked.

Container --HasItem--> Item --HasPriority--> Priority

Or with your naming convention perhaps something more like:

VContainer --EItem--> VItem --EPriority--> VPriority

It would probably also be easier to use some sortable number inside the VPriority class which you could project in the query to sort on.

The query could then be something simpler like:

SELECT *, max(out("EItem").out("EPriority").priorityLevel, null) as priority
    FROM VContainer ORDER BY priority DESC

This query example would not take the number of occurrences into account though, but I'm not sure if that is of importance.

How to sort vertices according to edge types OrientDb?

1 Answers