Whilst developing Soapee I received a feature request that Recipes should either be defined as public, private or only visible to friends - by default recipes were public.

Follows is a post on how I modeled Friendships using Bookshelf.js with PostgreSQL in Soapee’s Express based API Server.

But first, a primer on symmetric and asymmetric friendships and their differences.

Friendship Relationships

Friendship relationships describe a model in which users in a relational database form relationships between other users.

Generally these fall into two categories:

Asymmetric Relationships

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

Symmetric Relationships

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.

Frienships table DDL
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE public.friendships (
id SERIAL,
user_id INTEGER NOT NULL,
friend_id INTEGER NOT NULL,
created_at TIMESTAMP WITH TIME ZONE,
updated_at TIMESTAMP WITH TIME ZONE,
CONSTRAINT friendships_pkey PRIMARY KEY(id),
CONSTRAINT friendships_prevent_duplicate_requests UNIQUE(user_id, friend_id)
)
WITH (oids = false);
CREATE INDEX friendships_friend_id_index ON public.friendships
USING btree (friend_id);

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 friendships table. In other words, the users table forms a many-to-many relationship to itself via the friendships table.

Bookshelf.js provides the belongsToMany method which defines a Join Model with which the through method can be used to query users via an association.

I’ve extracted relevant Friendship and User models and relation definitions from Soapee’s API server - the full models can be viewed here.

Friendship

Friendship Bookshelf Model Excerpt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
import bookshelf from 'db/bookshelf';
import { User } from 'models/user';
export let Friendship = bookshelf.Model.extend( {
tableName: 'friendships',
user() {
return this.belongsTo( User );
},
friend() {
return this.belongsTo( User, 'friend_id' );
}
} );

User

User Bookshelf Model Excerpt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
import bookshelf from 'db/bookshelf';
import { Friendship } from 'models/friendship';
import { Recipe } from 'models/recipe';
export let User = bookshelf.Model.extend( {
tableName: 'users',
recipes() {
return this.hasMany( Recipe );
},
/**
* Symmetric friendship relationships
*/
friends() {
return this
.belongsToMany( User )
.through( Friendship, 'friend_id', 'user_id' )
.query( qb => {
qb.whereRaw(
`exists(
select 1
from friendships f
where f.friend_id = friendships.user_id
and f.user_id = friendships.friend_id )`
);
} );
},
/**
* Other people requesting to be friends with me but I
* have yet to approve their friend requests
*/
pendingIncomingFriendRequests() {
return this
.belongsToMany( User )
.through( Friendship, 'friend_id', 'user_id' )
.query( qb => {
qb.whereRaw(
`not exists(
select 1
from friendships f
where f.friend_id = friendships.user_id
and f.user_id = friendships.friend_id )`
);
} );
},
/**
* Me requesting to be friends with other people and
* they have yet to approve the friendship request
*/
pendingOutgoingFriendRequests() {
return this
.belongsToMany( User )
.through( Friendship, 'user_id', 'friend_id' )
.query( qb => {
qb.whereRaw(
`not exists(
select 1
from friendships f
where f.friend_id = friendships.user_id
and f.user_id = friendships.friend_id )`
);
} );
}
} );

In the 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

retrieving friends' recipes function extract
1
2
3
4
5
6
7
8
9
10
11
function getUserFriendsWithRecipes() {
return User
.forge( {
id: this.userId
} )
.fetch( {
withRelated: [
'friends.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:

retrieving friends' recipes
1
2
3
4
5
6
7
8
9
10
11
function getUserFriendsWithRecipes() {
return User
.forge( {
id: this.userId
} )
.fetch( {
withRelated: [
'friends.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.

The 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.

The getUserFriendsWithRecipes function can be modified as follows:

retrieving friends' recipes
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
function getUserFriendsWithRecipes() {
return User
.forge( {
id: this.userId
} )
.fetch( {
withRelated: [
{
'friends.recipes': qb => {
qb.where( 'recipes.visibility', '>', 0 );
}
}
]
} );
}}

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.

Conclusion

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...

The information in this article is provided “AS IS” with no warranties and is unlicensed to the Public Domain. The source code for this website is on GitHub.