Limiting the number of matches in Neo4j Cypher

0
votes

I have the following Neo4j scenario (I am using Neo4j 2.0.1):

Nodes:

User
Token

Relationships:

friends_with -> bidirectional relationship between Users
is_authorized_by -> directed relationship between a User and a Token

For each User, I need to get either its Token (if it has a valid one), or a valid friend's Token.

I've written the following Cypher query:

MATCH (user:User) WHERE user.id IN ['123', '456'] 
OPTIONAL MATCH (user)-[:is_authorized_by]->(user_token:Token) 
    WHERE HAS (user_token.access_token) AND timestamp()/1000 < user_token.expiration_timestamp 
OPTIONAL MATCH (user)-[:friends_with*1..3]-(friend:User)-[:is_authorized_by]->(friend_token:Token) 
    WHERE HAS (friend_token.access_token) AND timestamp()/1000 < friend_token.expiration_timestamp  
RETURN user.id as userId, coalesce(user_token.access_token, friend_token.access_token) AS token

Of course, this doesn't work, as the second OPTIONAL MATCH doesn't only match the first friend's Token.

What I would like to do is:

MATCH (user:User) WHERE user.id IN ['123', '456'] 
OPTIONAL MATCH (user)-[:is_authorized_by]->(user_token:Token) 
    WHERE HAS (user_token.access_token) AND timestamp()/1000 < user_token.expiration_timestamp 
OPTIONAL MATCH (user)-[:friends_with*1..3]-(friend:User)-[:is_authorized_by]->(friend_token:Token) 
    WHERE HAS (friend_token.access_token) AND timestamp()/1000 < friend_token.expiration_timestamp  <--- limit this to 1
RETURN user.id as userId, coalesce(user_token.access_token, friend_token.access_token) AS token

How can this be achieved?

Edit 1:

I'm only interested in direct friends' Tokens:

MATCH (user:User) WHERE user.id IN ['123', '456'] 
OPTIONAL MATCH (user)-[:is_authorized_by]->(user_token:Token) 
    WHERE HAS (user_token.access_token) AND timestamp()/1000 < user_token.expiration_timestamp 
OPTIONAL MATCH (user)-[:friends_with]-(friend:User)-[:is_authorized_by]->(friend_token:Token) 
    WHERE HAS (friend_token.access_token) AND timestamp()/1000 < friend_token.expiration_timestamp  
RETURN user.id as userId, coalesce(user_token.access_token, friend_token.access_token) AS token
3
neo4jcypher
Please note that the user.id IN ['123', '456'] is not yet an efficient operation. Only equality so far for index lookups – Michael Hunger
How many rows does this return? Would you want to limit it to one too? OPTIONAL MATCH (user)-[:is_authorized_by]->(user_token:Token) Do you want one token per friend or only one friend-token in total? – Michael Hunger
@MichaelHunger A User always has exactly one Token. I can authorize an User either by using his own Token or the Token of a direct friend. In the end, I want to return one valid Token for each User, preferring the User's own Token. – user3455402

3 Answers

1
votes

To just return any one out of the returned list you can apply an aggregation function like MAX, i.e.

RETURN user.id as userId, MAX (coalesce(user_token.access_token, friend_token.access_token)) AS token

0
votes

Since the coalesce() function already returns at most one access_token, you simply want to return just one row per userId:

RETURN DISTINCT user.id as userId, coalesce(user_token.access_token, friend_token.access_token) AS token
0
votes

How many rows does this return? Would you want to limit it to one too?

OPTIONAL MATCH (user)-[:is_authorized_by]->(user_token:Token)

Do you want one token per friend or only one friend-token in total?

Your tokens also don't seem to be connected at all? So you probably also rather want do something like this? Just guessing, not sure if that will perform as you'd like.

MATCH (user:User {id:'123'})
WITH user, head(filter(token in 
          extract(p in (user)-[:is_authorized_by]->(:Token) | last(nodes(p))) 
          WHERE token.access_token AND timestamp()/1000 < token.expiration_timestamp)) as user_token
OPTIONAL MATCH (user)-[:friends_with*1..3]-(friend:User)
WITH user, user_token, 
     head(filter(token in 
          extract(p in (friend)-[:is_authorized_by]->(:Token) | last(nodes(p))) 
          WHERE token.access_token AND timestamp()/1000 < token.expiration_timestamp)) as friend_token
RETURN user.id as userId, coalesce(user_token.access_token, friend_token.access_token) AS token