ORDER BY before a Collect and With

Question

I have a large Cypher query in which I gather a number of pieces of information for a view in my app. At one part I use an OPTIONAL MATCH to get upcoming meetups but I need them order by ASC before they go into a COLLECT and passed forward with a WITH statement. I tried to add it below the WHERE clause by, obviously, it's throwing an error.

Is there a way to order by in a OPTIONAL MATCH or in a COLLECT statement?

Here is my Cypher query

MATCH (o:Person)<-[:OWNED_BY]-(g:Group {group_slug:"'.$group_slug.'"})<-[:MEMBER_OF]-(m:Person)-[:JOB_TITLE]->(title:Title)
WITH o,g,COLLECT({first_name: m.first_name, last_name: m.last_name, image_name: m.image_name, username: m.username, job_title: title.job_title}) as members,title,COUNT(m) as member_count
OPTIONAL MATCH (g)-[:TOPIC_OF]->(t:Topic)
WITH o,members,g,COLLECT(DISTINCT t.name) as topics,title,member_count
OPTIONAL MATCH (g)-[:CREATED]->(mu:Meetup{status:"Active"})-[:MEETUP_AT]->(l:Location)
WHERE toInt(mu.meet_date) >= '.time().'
WITH o,members,g,title,topics,COLLECT({location_name:l.location_name, address:l.address,city:l.city,state:l.state,zip_code:l.zip_code,meet_date:mu.meet_date,meeting_id:id(mu)}) as meetups,member_count
OPTIONAL MATCH (g)-[]->(amu:Meetup)
WITH o,members,g,title,topics,meetups,COUNT(amu) as meetup_count,member_count
OPTIONAL MATCH (g)<-[:REQUESTED]-(pm:Person)-[:JOB_TITLE]->(pmt:Title)
WITH o,members,g,title,topics,meetups,meetup_count,COLLECT({first_name: pm.first_name, last_name: pm.last_name, image_name: pm.image_name, username: pm.username, job_title: pmt.job_title}) as requested,member_count
OPTIONAL MATCH(g) <-[:FAVORITED]-(fp:Person)
WITH o,members,g,title,topics,meetups,meetup_count,requested,COUNT(fp) as favorited,member_count
RETURN o.username as owner_username, o.first_name as owner_first, o.last_name as owner_last, g.group_name as group_name, g.description as description, g.access as access, g.group_slug as group_slug,
topics, members,meetups,meetup_count, favorited,requested,member_count

where the offending query part is

OPTIONAL MATCH (g)-[:CREATED]->(mu:Meetup{status:"Active"})-[:MEETUP_AT]->(l:Location)
WHERE toInt(mu.meet_date) >= time()

What is the error message? Also, note that the timestamp function is timestamp() not time(). — William Lyon

cybersam cybersam · Accepted Answer · 2015-10-20T01:18:37

[Edited]

A WITH clause can be followed by ORDER BY, SKIP, and LIMIT clauses.

Does this query work for you?

WITH timestamp() AS now
MATCH (o:Person)<-[:OWNED_BY]-(g:Group {group_slug:"'.$group_slug.'"})<-[:MEMBER_OF]-(m:Person)-[:JOB_TITLE]->(title:Title)
WITH o,g,COLLECT({first_name: m.first_name, last_name: m.last_name, image_name: m.image_name, username: m.username, job_title: title.job_title}) as members,title,COUNT(m) as member_count
OPTIONAL MATCH (g)-[:TOPIC_OF]->(t:Topic)
WITH o,members,g,COLLECT(DISTINCT t.name) as topics,title,member_count
OPTIONAL MATCH (g)-[:CREATED]->(mu:Meetup{status:"Active"})-[:MEETUP_AT]->(l:Location)
WHERE mu.meet_date >= now
WITH o,members,g,topics,title,member_count,mu,l
ORDER BY mu.meet_date
WITH o,members,g,title,topics,COLLECT({location_name:l.location_name, address:l.address,city:l.city,state:l.state,zip_code:l.zip_code,meet_date:mu.meet_date,meeting_id:id(mu)}) as meetups,member_count
OPTIONAL MATCH (g)-[]->(amu:Meetup)
WITH o,members,g,title,topics,meetups,COUNT(amu) as meetup_count,member_count
OPTIONAL MATCH (g)<-[:REQUESTED]-(pm:Person)-[:JOB_TITLE]->(pmt:Title)
WITH o,members,g,title,topics,meetups,meetup_count,COLLECT({first_name: pm.first_name, last_name: pm.last_name, image_name: pm.image_name, username: pm.username, job_title: pmt.job_title}) as requested,member_count
OPTIONAL MATCH(g) <-[:FAVORITED]-(fp:Person)
WITH o,members,g,title,topics,meetups,meetup_count,requested,COUNT(fp) as favorited,member_count
RETURN o.username as owner_username, o.first_name as owner_first, o.last_name as owner_last, g.group_name as group_name, g.description as description, g.access as access, g.group_slug as group_slug,
topics, members,meetups,meetup_count, favorited,requested,member_count;

For efficiency, we first create a now variable, instead of recalculating the current time every time we need it. This will also ensure consistent results:

WITH timestamp() AS now

The WHERE clause in the "offending" query part has been replaced by:

WHERE mu.meet_date >= now
WITH o,members,g,topics,title,member_count,mu,l
ORDER BY mu.meet_date

ORDER BY before a Collect and With

1 Answers