ORDER BY ignored in ACCUM query

GSQL
#1

In the following query, the ORDER BY is ignored - the result set is not ordered.

What am I missing?


CREATE QUERY getHelperOpenInvitations(VERTEX<Helper> keyHelper) FOR GRAPH SimplifyPilotADEV SYNTAX V2 { 
 
  ListAccum<STRING> @inv;
  ListAccum<ListAccum<STRING>> @@invAll;
  start = {keyHelper};
  
  vOpenCases = SELECT ci
    FROM start:s-(Replied>:repl)-CaseInvitation:ci-(reverse_TriggeredA>)-ProblemCase:pc-(reverse_StartedA>)-Learner:lr-(reverse_IsA>)-Person:p
      WHERE (repl.replyCode == 0 OR repl.replyCode == 2)
        AND (pc.status == 1 OR pc.status == 2)
        AND (lr.status > -1)
      ACCUM ci.@inv += [pc.id, ci.id, pc.problemText, pc.problemImage, datetime_format(ci.invitationTime), p.firstName, p.image, lr.id]
      POST-ACCUM @@invAll += [ci.@inv]
      ORDER BY ci.invitationTime DESC;
  
  PRINT @@invAll as Invitations; 
  }
#2

hi - what were you expecting to happen in this example? The ORDER BY is applied to your query and will indeed influence the result set, especially when combined with LIMIT. But in your case you are only printing the List accumulator, so were you expecting the order of the list to match? The ORDER BY runs at the very end. Would a SetAccum work better for you?

#3

Thanks!
I’m using a List accumulator as a workaround to be able to select a “join” from several vertices/edges.
Using SQL as a tool to explain, I want the selected set of records to be sorted.
Are you suggesting that the sorting cannot be done within the List accum?
Would a Set accum allow that?

#4

Hey, @morsagmon! To sort a collection, you will want to use a HeapAccum.

As per the docs,

The HeapAccum type maintains a sorted collection of tuples and enforces a maximum number of tuples in the collection. The output of a HeapAccum is a sorted collection of tuple elements.

Let me know if this helps or if you have any additional questions!

3 Likes
#5

There are a few ways I can think of, which would be better than using a ListAccum

  1. if you skip the global accumulators, and just collect the information that you want in the vertex level accumulators, then you can print the vertex set rather than the global accumulator. For instance, in your case, print vOpenCases rather than @@invAll. if you don’t want all of the attributes and accumulator values from the vertex set, the PRINT syntax allows you to just print what you want.

  2. if you find it cumbersome to assemble the values from different vertex types into one vertex set, then you can use the SQL-like style of query that allows you to print different attributes from different vertex types in a multi-hop query.

2 Likes
#6

Perfect!
I missed that heap in my peep :slight_smile:
Thank you, Shreya!

1 Like
#7

Thank, Mark.
The heap approach offered by Shreya is more suitable to this situation.
Can you show me an example of a multi-hop query implementing a “join”?

#8

A HeapAccum is great, but I just wanted to point out that if your goal was to sort a large result set, you don’t need to put everything into a HeapAccum just so that you can sort it.

For instance:

SELECT pc.id, ci.id, pc.problemText, pc.problemImage, ci.invitationTime, p.firstName, p.image, lr.id  
INTO  T
    FROM start:s-(Replied>:repl)-CaseInvitation:ci-(reverse_TriggeredA>)-ProblemCase:pc-(reverse_StartedA>)-Learner:lr-(reverse_IsA>)-Person:p
      WHERE (repl.replyCode == 0 OR repl.replyCode == 2)
        AND (pc.status == 1 OR pc.status == 2)
        AND (lr.status > -1)
      ORDER BY ci.invitationTime DESC;

PRINT T;
4 Likes
#9

Great example, Mark. Thank you!
Sure is handy for a large result set.

2 Likes