But first, a primer on symmetric and asymmetric friendships and their differences.
Friendship relationships describe a model in which users in a relational database form relationships between other users.
Generally these fall into two categories:
An asymmetric friendship relation is best exemplified by Twitter where users form the following relationships with each other:
- Followers - users that follow you but you don’t follow them back
- Following - users that you follow but they don’t follow you back
- Friends - users that you follow and they follow you back
- None - neither follows the other
A symmetric friendship relation is best illustrated by Facebook where specific visibility permissions are applicable only when a two users are friends - i.e. they follow each other.
The following relationships exist between facebook users:
- Sent friend requests - friend requests you have sent but have not been approved yet
- Received friend requests - a user requested to be your friend but you have not yet approved it
- Friends - two users have “friended”, and approved, each other
- None - no relationship exists between two users
Facebook provides security/privacy settings in which certain content is only accessible to “Friends” only hence the need to model and distinguish that relationship specifically.
For Soapee I needed to model symmetric relationships as I required to identify friends in order to limit recipe visibility. My implementation follows:
Symmetric Friendship Table Structure
This example uses PostgreSQL with Bookshelf.js but the same concepts apply if using MySQL, for example.
Friendship Bookshelf.js Models
There are few ways in which symmetric and asymmetric relationships can be modelled in an RDBMS. Generally this involves a
users table which associates to itself via a
In other words, the
users table forms a many-to-many relationship to itself via the
I’ve extracted relevant Friendship and User models and relation definitions from Soapee’s API server - the full models can be viewed here.
User model, the pendingIncomingFriendRequests and pendingOutgoingFriendRequests relations are provided for illustrative purposes. The key relationship is friends.
Using the above model, a friends relationship exists only when the
Friendship model contains two rows: one entry for each of the two users in the relationship.
The two rows are populated by a
User first making a request. Once the request is approved, a second row is inserted. Conversely, a friendship can be broken by
either party by deleting the relevant friends row.
Retrieving Friend’s Recipes
The above function, given a userId, will retrieve a specific user and will also eager-load the specific user’s friends and their recipes. Note that recipes are attached to friends but a simple lodash reduce, already built into Bookshelf.js, is able to collect all recipes from the returned friends collection.
The key relation here is friends,
which will return any relation defined on the
User model, but that belong to friends. If, for example, we wished to return all our friend’s comments:
Soapee’s User model defines additional relations (such as status update for example) which can all be eager loaded through friends.
Respecting Friend’s Recipes Privacy Settings
I’ll finish with an example that I hope illustrates how flexible Bookshelf.js is in both defining and querying relationships.
friends.recipes example has a minor issue in that it returns all our friend’s recipes, even if these were marked as private.
We should only be returning recipes with visibility set to either public or friend.
getUserFriendsWithRecipes function can be modified as follows:
Note, in the above
recipes.visibility 0 is private, 1 is public and 2 is friends only.
Bookshelf’s fetch model method takes a
withRelated option which allows specifying relations to lazy load.
This option can specify either a model name or a model name and function key value pair; the latter variant can be supplied a function in which
the underlying knex.js query can be further queried.
I hope I’ve shown that Bookshelf.js makes it easy (and IMHO fun) to define and query model relationships. As an additional resource, please feel free to review Soapee’s model implementations and how these models are queried.
The opinions expressed here represent my own and may or may not have any basis in reality or truth. These opinions are completely my own and not those of my friends, colleagues, acquaintances, pets, employers, etc...