neo4j - Search list with mutual count (2nd try)

Question

I have created fresh dataset to explain my desired result. and here is the link

Or you can trigger this command using cypher.

create 
(_6  {UserName:"dhansukh", UserProfileID:'1000', EMailID:'[email protected]'}),
(_5  {UserName:"dhruv", UserProfileID:'516', EMailID:'[email protected]'}),
(_4  {UserName:"dharmistha", UserProfileID:'5262', EMailID:'[email protected]'}),
(_3  {UserName:"dinesh", UserProfileID:'995', EMailID:'[email protected]'}),
(_2  {UserName:"dharmesh", UserProfileID:'502', EMailID:'[email protected]'}),
(_1  {UserName:"manish", UserProfileID:'1', EMailID:'[email protected]'}),
_1-[:friends {ApprovalStatus: 1} ]->_2,
_1-[:friends {ApprovalStatus: 1} ]->_3,
_1-[:friends {ApprovalStatus: 2} ]->_5,
_2-[:friends {ApprovalStatus: 1} ]->_3,
_2-[:friends {ApprovalStatus: 1} ]->_5,
_3-[:friends {ApprovalStatus: 1} ]->_4

Now I am trying following query, but it is not given me my expected result.

START me=node:node_auto_index(UserProfileID = '1'), other=node(*) 
MATCH pMutualFriends=me-[r?:friends]-mf-[r1:friends]-other 
WHERE other.UserName =~ '(?i)dh.*' AND other.UserProfileID <> 1 
RETURN other.UserProfileID, other.UserName, r.ApprovalStatus, COUNT(pMutualFriends) AS mutualCount

In the above result set, I have get duplicate records, (due to ApprovalStatus), If I remove ? from relationship, it just shows linked node only, but I want all nodes started with 'dh'. node 6 also missing, don't know why? mutual count also showing wrong in some case. Only that node should be consider in mutual count which is has ApprovalStatus = 1. like login node (eg. node 1) and search nodes both have relationship's property ApprovalStatus = 1.

EDIT : My expected result set :

UserProfileID  UserName     ApprovalStatus  MutualCount 
-------------  --------     --------------  ----------- 
502            dharmesh     1               2           (node 3 & 5 )
516            dhruv        2               1           (node 2)
5262           dharmistha   null            1           (node 3) 
1000           dhansukh     null            0               

EDIT : I am updating image for clear understanding.

I am suffering from this issue last 20-25 days, and not getting proper solution, I don't know where is the problem. I have already post this problem many times on stackoverflow. here is the link, and this and this and many more.

Answer 1

As I said in my comment, trying to query for connected and disconnected nodes at the same time doesn't seem to be a good idea.

If you want only connected nodes, try the following query :

START me=node:node_auto_index(UserName = 'manish') 
MATCH me-[:friends]-mf-[:friends]-other, me-[r?]-other 
WHERE other.UserName! =~ '(?i)dh.*' 
RETURN DISTINCT ID(other), r.ApprovalStatus AS status, count(mf) AS mutual, ID(me) 
ORDER BY mutual DESC , status ASC

Please note that I had to add another pattern in the match clause, because your approval status is between (me) and (other), and not between (me) and (mutual friend), which is what you were doing!

This will return the first 3 lines of your expected answer and ignores dhansukh.