UNION and sort? (also, property queries where no relationship exists)

[aross]

Hello,

I'm new to neo4j and love it so far, but I'm having a little trouble with more complicated cypher queries. Currently I have a node relationship like:

(user:User)-[:MEMBERSHIP]->(:Group)<-[:MEMBERSHIP]-(group_member:User)

I'd like to:

1. Find all User nodes where there is NO relationship between the "user" Node and "group_member" Nodes (ie, they are not in any of the same groups), and also filter by certain properties of the "group_member" Nodes. (I'm having a hard time figuring this one out)
2. Find all User nodes where there is a specific relationship between the "user" Node and "group_member" Nodes, and also filter by certain properties of the "group_member" Nodes. (I have this subquery working, so I don't need help here).
3. UNION 1 & 2 and then execute a fairly complicated sort and limit.

Of course if there is a way to query 1 & 2 in the same "MATCH/WHERE" that would be preferred but I'm not seeing a way to do this.

Any pointers in the right direction would be awesome!

[Joel]

1. would something like this work? Strategy: find user1 node, and the list of all users to check against, then find all users who you can't reach user1 through any group node?

MATCH (user1:User {name:"bob"}),(user2:User)
WHERE NOT (user1)-[:MEMBERSHIP]->(g:Group)<-[:MEMBERSHIP]-(user2)
AND g.name='filter me in' // group property restriction
return user2.name

Do you have a small test set you could share? (create statements for test set would help)

[aross]

Thank you for this Joel! I will play with it and let you know how it goes.

[aross]

Yes, this code is helpful! Thank you.

So right now these two queries are working:

MATCH (user1:User {user_id:166}),(user2:User)
WHERE (NOT (user1)-[:MEMBERSHIP]->(:Group)<-[:MEMBERSHIP]-(user2)
AND distance(user1.location, user2.location) < 20000)
RETURN user2 AS user

MATCH (user1:User {user_id:166})-[:MEMBERSHIP]->(:Group)<-[user2_rel:MEMBERSHIP]-(user2:User)
WHERE user2_rel.status='left' AND distance(user1.location, user2.location) < 20000
RETURN user2 AS user

Basically, the first returns all users that are not in any groups with user1 that are within 20 km of user1. The second returns all users that have left a group that they were in with user1 and are within 20 km.

I'd like to combine both sets, sort the union by certain properties of user2, and them limit to a specific number. I know that using UNION is slow and also you have very limited options on the UNION'ed set. So it seems like the best thing would be to combine both into one statement if it can be done.

Thanks again for your help! Getting closer...

[Joel]

It is best to not spend time optimizing until it is working and even then, only if required. You should be able to put a UNION between those two, to concatenate the results.

simple example below, here I'm renaming column names so I can union the results

RETURN 'apoc.version' as lib_name, apoc.version() as version
UNION
RETURN 'gds.version' as lib_name,  gds.version() as version

Note: If you are neo4j 4.x you can also do this

CALL {
RETURN 'apoc.version' as lib_name, apoc.version() as version
UNION
RETURN 'gds.version' as lib_name,  gds.version() as version
}
return lib_name, version
order by lib_name desc

[aross]

So, I now am able to construct a full query that works. The part of the query that deals with existing relationships is performing well. However the part that is pulling no relationships is performing terribly.

Here is that query:

MATCH (current_user:GraphUser {user_id:166}),(potential_user:GraphUser)
  WITH current_user AS current_user, potential_user AS potential_user,
  	distance(current_user.location,potential_user.location) AS distance
 WHERE (NOT (current_user)-[:GRAPH_MEMBERSHIP]->()<-[:GRAPH_MEMBERSHIP]-(potential_user))
 AND distance < 20000
  RETURN potential_user, distance
  ORDER BY distance ASC
  LIMIT 10

There are about 100K GraphUser nodes, so not even that many.

Here is the profile output:

I don't understand why the filtering needs to do millions of db hits for a set of under 100k rows, but i think it has something to do with the Cartesian product. But at over a second, this is performing much worse than the statement in SQL.

Thanks for the help so far, maybe I'm missing something obvious.

[clem]

Checkout:

Best practices for queries that can take hours to complete Cypher

Another link on query optimizations: I wasn't clearly your question on time out... But if you set the timeout to 0, it won't timeout:

One tip I learned from the video, is you can move up the LIMIT command so that the query will end earlier (which is different from SQL.). I haven't quite got the hang of it myself, so I'm not sure exactly how to transform your query.

[Joel]

Regarding optimizations to the existing query

Try doing the filtering in two steps, I'd first try filtering for distance < 20000, then WITH those results apply the WHERE NOT

If/how this helps optimize depends on the data content, but what we're trying to do is quickly narrow the cartesian product list down to something much smaller as fast as possible, so you might also try applying the WHERE NOT first, and then WITH those results calculate the distance of the remaining results applying the distance<20000 filter second. My gut tells me to try the simple distance check first, but you never know for sure until you try it, I really enjoy the surprises..