Summary I have created a many-to-many network graph relationship (social network). A Person can have many Friends. A Friend can be a friend to many Persons. Full object model (ERD) is given at the bottom of the question.
Friendship in this model can either be mutual (commonly known as symmetric or undirectional edges which i represent by blue double arrows) or non-mutual (commonly known as asymmetric or directional linkage/edges which i represent by black single arrows).
I would like help crafting the apex/triggers needed to maintain a numerical field ( numberOfNthDirectOrMutualPersonConnections__c) on each node (Person__c) that counts the number of symmetric or asymmetric edges connected to the Person__c through any degree of separation obtained by traversing from the Person__c through the entire network only along symmetric or asymmetric edges. I guarantee there will always be less than 50,000 Person__c records in my system.
Person__c.numberOfNthDirectOrMutualPersonConnections__c (numeric) field needs to count (to the nth degree) all the unique Friend__r.Person__c records reachable by Person__c through symmetric (blue double arrows) or asymmetric (black single arrow) edges/associations/links (this means that the numeric count needs to include friends (of nth degree in the network) reachable via direct (black arrow) friendship or mutual friendship (blue arrow).
Example & Expectation:
- Suppose that
Person__cTom has 2
Person__crecords (Bill and Jane) in Tom's
Person__cBill has no direct friends (no black arrow pointing to others in other words no Person__c s in Bill's
FriendsList__c), and no mutual friends (no blue arrow)
Person__cJane has 2
Person__crecords (Tom and Lisa) in Jane's
FriendsList__cand both edges are mutual (blue arrow means symmetric linkage).
Person__cLisa has 2 friends (Jane and Kim) in Lisa's
FriendsList__c. Jane is a mutual friend and Kim is just a friend (black arrow means asymmetric direct linkage and points to the friend)
- Kim has no friends, meaning no
Person__cchild records associated to Kim's
My expectation is that "4" is returned in Person__c Tom's numberOfNthDirectOrMutualPersonConnections__c field.
Similarly, numberOfNthDirectOrMutualPersonConnections__c would equal 0 for Bill. Similarly, numberOfNthDirectOrMutualPersonConnections__c would equal 4 for Jane. Similarly, numberOfNthDirectOrMutualPersonConnections__c would equal 4 for Lisa. Similarly, numberOfNthDirectOrMutualPersonConnections__c would equal 0 for Kim.
High-level Attempt, Limits, Considerations
- Query all associations
SELECT Id,FriendId,Friend__r.FriendsList__c,Friend__r.FriendsList__r.Person__c FROM Person__c LIMIT 50000
- Iterate the query to create a "network structure" (Person__c as nodes, mapped to a map of all of their edges (fromPersonId, ToPersonId).
It might look like
- Maybe implement into Triggers (or Batch Apex) a "depth search" or "breadth search" counting algorithm to execute on
myNetworkStructurethat calculates the number that I'm seeking.
- Putting queries inside loops is not an appropriate solution because the SOQL governor limits will throw exceptions too soon for this project.
- For this project, there will never be more than 50,000 Person__c records in the system
- I'm assuming with under 50,000 records this can be done via synchronous logic. I have not worked with graphical network algorithms enough to justify having to use batch apex for this problem.
- In thinking about the Triggers needed to maintain truth/accuracy in all
Person__c.numberOfNthDirectOrMutualPersonConnections__crecord values the solution might call for:
- Trigger on Friend__c (after delete, before insert), //to handle when friends are added or deleted. We must recalculate the numberOfNthDirectOrMutualPersonConnections__c for some if not all Person__c in the database to sustain the new truth
- Trigger on Friend__c (before update) // to handle when a Friend__r.Person__c lookup changes then we must recalculate the numberOfNthDirectOrMutualPersonConnections__c for some if not all Person__c in the database to sustain the new truth
- Trigger on Person__c (after delete) //to handle when a Person__c is deleted because this will reduce friendships in the network therefore some if not all Person__c in the database would need to have
Person__c.numberOfNthDirectOrMutualPersonConnections__cDML updated to sustain the new truth
ERD and Page Layout Details: FriendsList__c is the junction object that enables the many to many relationship. FriendsList__c is the master side in the master-detail relationship with Person__c. Person__c is the detail side. Every detail-side Person__c has 1 master FriendsList__c. Using the FriendsList__c standard page, we click New Friend to add a new Friend__c to the Friend__c related list on FriendsList__c. (Note that per my requirement the Friend__c edit page layout requires Lookup association to Person__c)