Summing edges properties for order by in select statement

I’m trying to do a select statement to order the result set by force of connections between them.

CREATE CLASS Entity EXTENDS V;
CREATE CLASS isConnectedTo EXTENDS E;

CREATE PROPERTY isConnectedTo.strength INTEGER;

‘isConnectedTo’ edges relate Entities to another Entities

The tricky part is I have double connections between the same entities as so:

CREATE VERTEX Entity SET name = "John";
CREATE VERTEX Entity SET name = "Mike";
CREATE VERTEX Entity SET name = "Susan";

CREATE EDGE isConnectedTo FROM (SELECT FROM Entity WHERE name = "Mike") TO (SELECT FROM Entity WHERE name = "John") SET strength = 3;
CREATE EDGE isConnectedTo FROM (SELECT FROM Entity WHERE name = "Mike") TO (SELECT FROM Entity WHERE name = "Susan") SET strength = 4;
CREATE EDGE isConnectedTo FROM (SELECT FROM Entity WHERE name = "John") TO (SELECT FROM Entity WHERE name = "Mike") SET strength = 2;

So Mike is connected to John which is connected to him too. Also Mike is connected to Susan.

In this case if I were to run my desired query on Mike it should return:

1. John
2. Susan

by this order because the sum of strength between Mike and John is 3 + 2 = 5 and between Mike and Susan is only 4.

I’ve iterated through many possible queries to no avail but I can’t seem to figure why this last one refuses to work.

LET $main = SELECT FROM Entity WHERE name = "John";
LET $vset = SELECT expand(in('isConnectedTo')) FROM $main;
SELECT @rid, eval('$ei.strength + $eo.strength') as total_strength FROM $vset
   LET $ei = (SELECT expand(inE('isConnectedTo')) FROM $current WHERE $main.@rid IN out),
   LET $eo = (SELECT expand(outE('isConnectedTo')) FROM $current WHERE $main.@rid IN in)
ORDER BY total_strength DESC;

Note: I already got the Entities’ names indexed with a hash dictionary and the ‘isConnectedTo’ edges are indexed with unique links between Entities (hence they cannot be repeated in the same direction)

All the other trials I did or help from the other answers here result in query times of >= 2 mins.

Answer

I’d like to propose a small change on your domain. Don’t create 2 edges in case both people are connected, but use the same edge with 2 properties.

CREATE CLASS Entity EXTENDS V;
CREATE CLASS isConnectedTo EXTENDS E;
CREATE PROPERTY isConnectedTo.strengthOut INTEGER;
CREATE PROPERTY isConnectedTo.strengthIn INTEGER;

CREATE VERTEX Entity SET name = "John";
CREATE VERTEX Entity SET name = "Mike";
CREATE VERTEX Entity SET name = "Susan";

CREATE EDGE isConnectedTo FROM (SELECT FROM Entity WHERE name = "Mike") TO (SELECT FROM Entity WHERE name = "John") SET strengthOut = 3, strengthIn = 2;
CREATE EDGE isConnectedTo FROM (SELECT FROM Entity WHERE name = "Mike") TO (SELECT FROM Entity WHERE name = "Susan") SET strengthOut = 4;

In this was you have less edges and you can use this super fast query:

SELECT out.name as name1, in.name as name2, eval('strengthOut + strengthIn') as strength
FROM (
  SELECT expand( bothE('isConnectedTo') ) FROM Entity WHERE name = "Mike"
) ORDER BY strength

NOTE: Remember to create an index on Entity.name to speedup the inner query.