I’m trying to achieve a dynamic sort so that I can reuse this query and not have to build the same query for each type of sorting but I can’t seem to make it work.
I believe this should work as per the documentation on evaluate but I’m getting “no type can be inferred” error in GraphStudio with the “ORDER” marked red.
CREATE QUERY getUserUnitsSoldPerProduct(VERTEX<User> user, STRING sortBy) FOR GRAPH SimulatedData SYNTAX v2 {
SumAccum<INT> @unitsSold;
SumAccum<DOUBLE> @sales;
SumAccum<DOUBLE> @fees;
Start = {user};
Result = SELECT p
FROM Start-(sells)-Product:p-(purchases)-OrderItem:oi-(happened)-FinancialEvent:fe
ACCUM p.@unitsSold += oi.quantity,
CASE fe.eventType
WHEN "payment" THEN p.@sales += fe.amount
WHEN "commission" THEN p.@fees += fe.amount
WHEN "shipping" THEN p.@fees += fe.amount
END
ORDER BY evaluate("p.@unitsSold", "int") DESC;
PRINT Result;
PRINT Result[Result.@unitsSold];
PRINT Result[Result.@sales];
PRINT Result[Result.@fees];
}
What am I missing here ?
Ideally I’d like to just pass the field I want to sort on like “@unitsSold” rather than “p.@unitsSold” + “int”, is there a way to do this ?
I was thinking using a IF sortBy == “sales” THEN or CASE sortBy WHEN “sales” THEN but none of this seems to work in an ORDER BY clause.
From your comment below, it sounds like you’re trying to do this :
...
ORDER BY p.@unitsSold DESC;
Is that what you’re looking for?
No, I’m trying to sort by a dynamic parameter, I’ve simplified the evaluate as much as possible to reduce possible errors but the goal is to be able to pass a sortBy param and get it sorted either by units sold, sales or fees.
How about this? Rather than using the input param to change the ORDER BY clause, use it in the ACCUM cause to decide what to accumulate into a general @sortVal accumulator, then always ORDER BY @sortVal.
For instance - something like this
POST-ACCUM
CASE sortBy
WHEN “units” THEN p.@sortVal += p.@unitsSold
WHEN “sales” THEN p.@sortVal += p.@sales
WHEN “fees” THEN p.@sortVal += p.@fees
END
ORDER BY p.@sortVal DESC
That would solve the dynamic sorting part but I actually want to get data about all those fields and only change the sorting.
Imagine that you want to display a list of the user’s product in a table with the product as the first column and different metrics like units sold, sales, fees in the next columns. I want the user to be able to sort this table on any column. Therefore I need all data, but only the sorting changes when the user clicks one of the headers.
Sure - you could still do that - my POST ACCUM was meant to just adjust the sort, everything before that would be available
FROM Start-(sells)-Product:p-(purchases)-OrderItem:oi-(happened)-FinancialEvent:fe
ACCUM p.@unitsSold += oi.quantity,
CASE fe.eventType
WHEN “payment” THEN p.@sales += fe.amount
WHEN “commission” THEN p.@fees += fe.amount
WHEN “shipping” THEN p.@fees += fe.amount
END
POST-ACCUM
CASE sortBy
WHEN “units” THEN p.@sortVal += p.@unitsSold
WHEN “sales” THEN p.@sortVal += p.@sales
WHEN “fees” THEN p.@sortVal += p.@fees
END
ORDER BY p.@sortVal DESC
Thanks for your help, this would work if the rest of my query logic wasn’t flawed.
As I’m trying to get values per product but what I’m doing here is accumulating for every financial event found.
The schema is so that a product will have multiple order items which themselves will have 3 or 4 events attached.
I have 2 products in the graph, one has an orderItem with 3 attached financial events, and the other one has nothing. When I run this query my units sold come in at 3 times the value of the orderItem.quantity.
So I’ve managed to get the correct results with a GroupByAccum, but my problem is now I have no idea how to sort it and I’m not getting the full product anymore in the results.
Was there a simpler way to fix it while keeping local accumulators ?
Here is what I did to get the correct results
CREATE QUERY getUserUnitsSoldPerProduct(VERTEX<User> user, STRING sortBy = "p.@unitsSold") FOR GRAPH SimulatedData SYNTAX v2 {
GroupByAccum<VERTEX<Product> p, SumAccum<INT> units, SumAccum<DOUBLE> sales, SumAccum<DOUBLE> fees> @@pAcc;
Start = {user};
userProducts = SELECT p FROM Start-(sells)-Product:p ACCUM @@pAcc += (p -> 0, 0, 0);
orderItems = SELECT oi FROM userProducts:p-(purchases)-OrderItem:oi ACCUM @@pAcc += (p -> oi.quantity, 0, 0);
financialEvents = SELECT fe FROM userProducts:p-(purchases)-orderItems:oi-(happened)-FinancialEvent:fe
ACCUM CASE fe.eventType
WHEN "payment" THEN @@pAcc += (p -> 0, fe.amount, 0)
WHEN "commission" THEN @@pAcc += (p -> 0, 0, fe.amount)
WHEN "shipping" THEN @@pAcc += (p -> 0, 0, fe.amount)
END;
PRINT @@pAcc;
}
Actually this works also
CREATE QUERY getUserUnitsSold(VERTEX<User> u) FOR GRAPH SimulatedData SYNTAX v2 {
SumAccum<INT> @unitsSold = 0;
SumAccum<DOUBLE> @sales = 0;
SumAccum<DOUBLE> @fees = 0;
Start = {u};
userProducts = SELECT p FROM Start-(sells)-Product:p;
orderItems = SELECT oi FROM userProducts:p-(purchases)-OrderItem:oi ACCUM p.@unitsSold += oi.quantity;
financialEvents = SELECT fe FROM userProducts:p-(purchases)-OrderItem-(happened)-FinancialEvent:fe
ACCUM CASE fe.eventType
WHEN "payment" THEN p.@sales += fe.amount
WHEN "commission" THEN p.@fees += fe.amount
WHEN "taxes" THEN p.@fees += fe.amount
END;
PRINT userProducts;
}
Right - you have hit upon a key point for understanding traversal and accumulation. On a multi-hop query, you can erroneously duplicate counts of an intermediate vertex since you are going to potentially visit that multiple times if it has multiple edges. This can be solved in the way you have done it, by breaking it up into multiple steps.
Another suggestion is that if you want to avoid the CASE statement, you could use a
MapAccum < STRING, DOUBLE > @costs:
ACCUM p.@costs += (fe.eventType -> fe.amount) ;
This has the added advantage of being more flexible in case you decide to add more eventType values. Then later you could group together commission and taxes during post-processing.
Thanks I managed to solve it finally like so 
CREATE QUERY getUserUnitsSold(VERTEX<User> u, STRING sortBy = "units") FOR GRAPH SimulatedData SYNTAX v2 {
SumAccum<INT> @unitsSold = 0;
SumAccum<DOUBLE> @sales = 0;
SumAccum<DOUBLE> @fees = 0;
SumAccum<DOUBLE> @sortVal = 0;
Start = {u};
userProducts = SELECT p FROM Start-(sells:s)-Product:p;
orderItems = SELECT oi FROM userProducts:p-(purchases)-OrderItem:oi
ACCUM p.@unitsSold += oi.quantity;
financialEvents = SELECT fe FROM userProducts:p-(purchases)-OrderItem-(happened)-FinancialEvent:fe
ACCUM CASE
WHEN fe.eventType == "payment" THEN p.@sales += fe.amount
WHEN fe.eventType == "commission" THEN p.@fees += fe.amount
WHEN fe.eventType == "taxes" THEN p.@fees += fe.amount
END;
result = SELECT p FROM userProducts:p
ACCUM CASE sortBy
WHEN "units" THEN p.@sortVal += p.@unitsSold
WHEN "sales" THEN p.@sortVal += p.@sales
WHEN "fees" THEN p.@sortVal += p.@fees
END
ORDER BY p.@sortVal DESC;
PRINT result;
}
I’ll look into your suggestion with MapAccum to see if I can improve this
I also need to find a way to pass the direction (DESC or ASC) dynamically, I guess I could do a IF on this last result, I don’t suppose there is a way to pass the arg directly to the query like ORDER BY p.@sortVal sortDirection
1 Like
To handle the sort direction dynamically, why not multiply the @sortVal by -1 if the argument is descending? Perhaps not the most elegant, but should work fine.
1 Like
