php - Optimising neo4j cypher query for retrieving a social news feed -

up to have tried query slow scans nodes. capable of want retrieve

match (u:users{user_id:140}),(p:posts),(pu:users{user_id:p.created_by}) optional match  (p)-[:post_media]->(f) optional match (p)-[:comment]->(c)<-[:comment]-(u3) (p)-[:created_by]->(u) or (p:public , (u)-[:follow]->(pu) )or  (p:private , (p)-[:shared_with]->(u)) return {user_id:pu.user_id,firstname:pu.firstname,lastname:pu.lastname,profile_photo:pu.profile_photo,username:pu.username} pu,p,collect({user_id:u3.user_id,profile_photo:u3.profile_photo,text:c.text}) comment,collect(f) file order p.post_id desc  limit 25 

before query tried query fast can't retrieve complete news feed capable of retrieving posts followings not users's himself , not others users private posts may shared user retrieving newsfeed.

    match (u:users{user_id:140})-[:follow]->(pu)<-[:created_by]-(p:posts)     optional match  (p)-[:post_media]->(f)     optional match (p)-[:comment]->(c)<-[:comment]-(u3) p:public          return     {user_id:pu.user_id,firstname:pu.firstname,    lastname:pu.lastname,profile_photo:pu.profile_photo,username:pu.username} pu,p,    collect({user_id:u3.user_id,profile_photo:u3.profile_photo,text:c.text}) comment,    collect(f) file order p.post_id desc  limit 25 

note:- short of modifying in clause this:-

where p:public or (p)-[:shared_with]->(u) // problem how should include posts of users himself retrieving news feed . 

firstly should make sure have index on user_id property user label. this:

create index on :users(user_id) 

(incidentally it's common practice use singular nouns labels)

but should using neo4j relationships rather matching post's created_by column. allow neo4j traverse relationship (which fast) compared checking index (which still fast, not ideal, think)

but have problem, think, fact variables in where should put in where clause directly after (optional) match in question. example (p)-[:created_by]->(u) condition refers variables defined in first match, below optional match , where apply optional match. should able fix putting with * in between, though you'd want benchmark performance.

here query of changes (not need setup created relationship separately first):

match   (u:users {user_id:140}),   (p:posts)<-[:created]-(pu:users)   (p)-[:created_by]->(u) or   (p:public , (u)-[:follow]->(pu)) or   (p:private , (p)-[:shared_with]->(u)) optional match (p)-[:post_media]->(f) optional match (p)-[:comment]->(c)<-[:comment]-(u3) return   {user_id:pu.user_id,     firstname:pu.firstname,     lastname:pu.lastname,     profile_photo:pu.profile_photo,     username:pu.username} pu,   p,   collect({user_id:u3.user_id,            profile_photo:u3.profile_photo,            text:c.text}) comment,   collect(f) file order p.post_id desc limit 25 

edit: actually, looking @ this, of variables in where defined in first match, should able move there. edited query reflect that.

edit2: try using optional match which, think, have neo4j traversals first. where think it's getting possible results , filtering, wouldn't efficient.

profile  match   (u:users {user_id:140}),   (p:posts)<-[:created]-(pu:users) optional match (p)-[created_by:created_by]->(u), (u)-[follow:follow]->(pu), (p)-[shared_with:shared_with]->(u) created_by not null or (p:public , follow not null) or (p:private , shared_with not null) optional match (p)-[:post_media]->(f) optional match (p)-[:comment]->(c)<-[:comment]-(u3) return   {user_id:pu.user_id,     firstname:pu.firstname,     lastname:pu.lastname,     profile_photo:pu.profile_photo,     username:pu.username} pu,   p,   collect({user_id:u3.user_id,            profile_photo:u3.profile_photo,            text:c.text}) comment,   collect(f) file order p.post_id desc limit 25 

you might want experiment indexed property indicate private rather using labels.