Unexpected behavior for multiple 'with' calls separated by 'order' #1642
Description
I'm trying to produce a query similar to the following via method chaining. It gets the top five friends, ordered by name, of each user.
MATCH (user:User)-[:friend_of]->(friend:User)
WITH user, friend
ORDER BY user.id, friend.name ASC
WITH user, collect(friend)[0..5] AS top_friends_alphabetically
RETURN user, top_friends_alphabetically
I would expect something like this to work:
User.as(:user).friends.query_as(:friend)
.with(:user, :friend)
.order('user.id', 'friend.name ASC')
.with(:user, 'collect(friend)[0..5] AS top_friends_alphabetically')
.return(:user, :top_friends_alphabetically)
However, this results in the following invalid cypher query, lumping both .with calls into a single WITH before the ORDER BY:
MATCH (user:User)
MATCH (user)-[:friend_of]->(friend:User)
WITH user, friend, user, collect(friend)[0..5] AS top_friends_alphabetically
ORDER BY user.id, friend.name ASC
RETURN user, top_friends_alphabetically
Is this expected behavior? If so, is there an alternate way to accomplish what I'm trying to do without resorting to ActiveGraph::Base.query? Thanks!