0

I am experimenting with dates in neo4j. Now I'd like to sort my results by the ISODateString. I created a cypher query like this: 

MATCH(e:Expedition {id : "BJGYmzwZb"})-[pje]-(u:User) 
WHERE (e)-[:POSSIBLY_JOINS_EXPEDITION]-(u) OR (e)-[:JOINS_EXPEDITION]-(u) 
WITH e, u, apoc.date.parse(pje.createdAt, 's',"yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") as date
ORDER by date 
OPTIONAL MATCH(u)<-[invitee:POSSIBLY_JOINS_EXPEDITION]-(e) 
OPTIONAL MATCH(u)-[attendee:JOINS_EXPEDITION]->(e)
OPTIONAL MATCH(u)-[applicant:POSSIBLY_JOINS_EXPEDITION]->(e) 
RETURN date, {user: properties(u), isInvitee: COUNT(invitee) > 0, isApplicant: COUNT(applicant) > 0, isAttendee: COUNT(attendee) > 0} as u

The returned results are not sorted properly. Whereas the following query does return the results in the right order. I just removed the parts with OPTIONAL MATCH.

MATCH(e:Expedition {id : "BJGYmzwZb"})-[pje]-(u:User) 
WHERE (e)-[:POSSIBLY_JOINS_EXPEDITION]-(u) OR (e)-[:JOINS_EXPEDITION]-(u) 
WITH e, u, apoc.date.parse(pje.createdAt, 's',"yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") as date 
ORDER by date
RETURN date, {user: properties(u)} as u

Any suggestions what I am doing wrong? Do I need to deal differently with the OPTIONAL MATCH-additions?

janwo
  • 754
  • 2
  • 8
  • 17

1 Answers1

1

Put ORDER by date after the RETURN statement, like this:

MATCH(e:Expedition {id : "BJGYmzwZb"})-[pje]-(u:User) 
WHERE (e)-[:POSSIBLY_JOINS_EXPEDITION]-(u) OR (e)-[:JOINS_EXPEDITION]-(u) 
WITH e, u, apoc.date.parse(pje.createdAt, 's',"yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") as date
OPTIONAL MATCH(u)<-[invitee:POSSIBLY_JOINS_EXPEDITION]-(e) 
OPTIONAL MATCH(u)-[attendee:JOINS_EXPEDITION]->(e)
OPTIONAL MATCH(u)-[applicant:POSSIBLY_JOINS_EXPEDITION]->(e) 
RETURN date, {user: properties(u), isInvitee: COUNT(invitee) > 0, isApplicant: COUNT(applicant) > 0, isAttendee: COUNT(attendee) > 0} as u
ORDER by date
Bruno Peres
  • 15,845
  • 5
  • 53
  • 89
  • This does not work, if I do not want to return the date. Any workaround? – janwo May 30 '17 at 14:23
  • Try: `MATCH(e:Expedition {id : "BJGYmzwZb"})-[pje]-(u:User) WHERE (e)-[:POSSIBLY_JOINS_EXPEDITION]-(u) OR (e)-[:JOINS_EXPEDITION]-(u) WITH e, u, apoc.date.parse(pje.createdAt, 's',"yyyy-MM-dd'T'HH:mm:ss.SSS'Z'") as date OPTIONAL MATCH(u)<-[invitee:POSSIBLY_JOINS_EXPEDITION]-(e) OPTIONAL MATCH(u)-[attendee:JOINS_EXPEDITION]->(e) OPTIONAL MATCH(u)-[applicant:POSSIBLY_JOINS_EXPEDITION]->(e) WITH date, u, invitee, applicant, attendee ORDER BY date RETURN {user: properties(u), isInvitee: COUNT(invitee) > 0, isApplicant: COUNT(applicant) > 0, isAttendee: COUNT(attendee) > 0} as u` – Bruno Peres May 30 '17 at 14:35
  • @janwo Please, edit your question and post a sample of your data set. This way I will be able to reproduce your scenario here! Thanks! – Bruno Peres May 30 '17 at 21:50