Unexpected behavior for multiple 'with' calls separated by 'order'

[mvitale]

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!

[mrhardikjoshi]

Hello @mvitale , following should work fine and it also avoids additional with clause.

User.as(:user).friends.query_as(:friend)
  .with(:user, :friend)
  .order('user.id', 'friend.name ASC')
  .return(:user, 'collect(friend)[0..5] AS top_friends_alphabetically')

It generates following cypher, which returns correct results:

  MATCH (user:`User`)
  MATCH (user)-[rel1:`friend_of`]->(friend:`User`)
  WITH 
    user, 
    friend
  ORDER BY 
    user.id, 
    friend.name ASC
  RETURN 
    user, 
    collect(friend)[0..5] AS top_friends_alphabetically 

[mvitale]

Thanks! Actually, my example here is contrived and much simpler than the actual query I'm trying to write. In the real one, there's a bunch of matching that happens after the with calls. So, I think my question/bug report still stands: it seems like one should be able to call with multiple times without them getting lumped into a single WITH in the query.

[klobuczek]

@mvitale have you tried the break method? It usually prevents such unwanted shuffle.

[mvitale]

@klobuczek I haven't -- I'll give that a shot. Thanks for pointing it out. I'm new to the API.

[mvitale]

@klobuczek That works, thanks. I'll leave the decision to close this issue or leave it open up to you.