Order by on edge attributes

GSQL
#1

HI Guys,

I would need your help to achieve below requirement . I wanted to order by(DESC) the edges based on amount attributes and fetch top 100 source vertexes. example
sender={all sender}
sender_grt_10000= select s from sender:s - (send_to:e) - receiver:t where e.amount>10000

Here i wanted to get top 100 senders who sent money greater than 10000 . looks like TG does not allow to order based on the edge attributes , so how to achieve this? please help me on this

#2

It’s not exactly true that GSQL can’t order by edge attributes, but in your case, your sort variable not part of your output set. One way to satisfy your need would be to create a vertex-attached accumulator as a temporary variable to hold a copy of the edge attribute that you want to use for sorting, copy the value from the edge to the accumulator, and then sort by the accumulator value. Something like this (not tested):

SumAccum<INT> @amount;
sended_grt_10000 = SELECT s 
   FROM sender:s -(send_to:e) - receiver:t
   WHERE e.amount > 10000
   ACCUM s.@amount += e.amount
  ORDER BY s.@amount DESC;
#3

Thank you lee,
But SumAccum will sum up the amount of a sender who sends the money to multiple receiver. so Order By will be applied to summed up amount , how this help my case?

#4

Good question. That actually raises a semantic issue with the original problem you posed, because you are ranking vertices based on an edge, where multiple edges can connect to that vertex. It means you do not have a 1-1 relationship between vertices and edges, so how should we rank?

" top 100 senders who sent money greater than 10000 "
If one person sends $100 AND $125, is that higher or lower rank than another person who sends $200 once?

My code was intended to show you a technique, not an exact solution. If you want to add the value of multiple sendings by a user, then use SumAccum. If you want to use the maximum value of multiple sendings, then use MaxAccum. If you want to use average amount, then use AvgAccum.

#5

Thanks for response , let me explain with example
person sends receiver
peter 20000 kate
peter 9000 jhon
peter 12000 nancy
peter 11000 andrew
peter 8000 scott

lets say top 2 amount wanted to select
in this example, i wanted to select only peter and his receiver kate,nancy who received top 2 greater than 10k. Hope I explained in easy way

#6

This probably would have been faster to discuss in a face-to-face chat :slight_smile:

If you want the top Persons (vertices) based on which Persons sent the largest amounts (we only care about their biggest transaction; we don’t care about the size of their 2nd biggest, etc.), then use the solution I suggested with MaxAccum. MaxAccum records the biggest value it has seen.

If you want the top Transactions (edges) based on the largest transaction amounts, then use a global accumulator that stores a collection of edges. Because you want the N “top” edges, you probably want to use a HeapAccum. This is a little more complex, but we do it all the time.

However, I think you want the first case.