Venturing into DataMapper + many-to-many self-referential graphs

With Merb v1.0 almost upon us I did a little research into it and it's opinionated choice for DataMapper.

After some initial hurdles I've come to be taken by DataMapper. It's nice, easy and fast to work with. The toughest thing to model in DataMapper though was a many-to-many self-referential graph. Others have attempted to solve this in various ways, but all seemed lacking to me. The documentation and specs are also still lacking somewhat at the moment, though good efforts are underway to improve this.

So the thing I wanted to model was a user that can friend many other users. On the user model I wanted an easy way to get to the friends of the user, and to get the users that have friended the user, all via datamapper associations.

Below is a solution that I think models this well in the database, and provides nice interfaces in a bi-directional fashion, providing datamapper collections to work with.

If this weren't a self-referential many-to-many relationship you don't need to be specific about the intermediate model. For example, you would simply do this:

class Article
  include DataMapper::Resource

  has n, :categories, :through => Resource
end

class Category
  include DataMapper::Resource

  has n, :articles, :through => Resource
end

The :through => Resource bit tells DataMapper that you're referring to another model through the anonymous Resource model. This will basically define a class ArticleCategory and a matching table articles_categories

However, due to the self-referential nature that we're looking at, we need to be specific about the intermediate model. Here are the models:

class User
  include DataMapper::Resource

  property :id, Serial
  property :name, String, :nullable => false

  has n, :friended_users
  has n, :friends, :through => :friended_users, :class_name => "User",
                   :child_key => [:user_id]
  has n, :friended_by, :through => :friended_users, :class_name => "User",
                       :remote_name => :user, :child_key => [:friend_id]
end

class FriendedUser
  include DataMapper::Resource

  property :user_id, Integer, :key => true
  property :friend_id, Integer, :key => true

  belongs_to :user, :child_key => [:user_id]
  belongs_to :friend, :class_name => "User", :child_key => [:friend_id]
end

Things to note:

• The FriendedUser model defines two integer properties explicitly and makes them the primary key. There is no need for an auto-generated (Serial) ID. Because of the primary key definition, uniqueness of the tuple (:user_id, :friend_id) is guaranteed and you get the indexes.
• The FriendedUser model belongs_to :user, referring to the User class. It maps into the property :user_id.
• The FriendedUser model belongs_to a :friend, referring to the User class. It maps into the property :friend_id.
• The User model has many :friended_users, refering to the FriendedUser class. Note that when you access this collection for a specific user it will join with the :user_id property of the FriendedUser model.
• It then defines the two associations :friends and :friended_by, through :friended_users. It must specify that what we'll expect to be returned are User instances. It also must specify the :child_key in FriendedUser to which this association should be linked. Lastly, for the :friended_by association you must specify the :remote_name, which refers to the belongs_to :user bit in FriendedUser. You don't need to (but can if you want to) specify :remote_name for the :friends association because datamapper will infer that it is referring to the belongs_to :friend bit in FriendedUser based on the association name :friends.

Let's test this:

DataMapper.auto_migrate!

jan = User.create(:name => 'Jan')
piet = User.create(:name => 'Piet')
klaas = User.create(:name => 'Klaas')
bo = User.create(:name => 'Bo')
fu1 = FriendedUser.create(:user => jan, :friend => piet)
fu2 = FriendedUser.create(:user => jan, :friend => klaas)
fu3 = FriendedUser.create(:user => bo, :friend => klaas)

# Output
p "-"*78
p bo.friended_users == [fu3]
p jan.friended_users == [fu1, fu2]
p jan.friends == [piet, klaas]
p klaas.friended_by == [jan, bo]

The resulting output:

~ CREATE TABLE "users" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "name" VARCHAR(50) NOT NULL)
~ CREATE TABLE "friended_users" ("user_id" INTEGER NOT NULL, "friend_id" INTEGER NOT NULL, PRIMARY KEY("user_id", "friend_id"))
~ INSERT INTO "users" ("name") VALUES ('Jan')
~ INSERT INTO "users" ("name") VALUES ('Piet')
~ INSERT INTO "users" ("name") VALUES ('Klaas')
~ INSERT INTO "users" ("name") VALUES ('Bo')
~ INSERT INTO "friended_users" ("user_id", "friend_id") VALUES (1, 2)
~ INSERT INTO "friended_users" ("user_id", "friend_id") VALUES (1, 3)
~ INSERT INTO "friended_users" ("user_id", "friend_id") VALUES (4, 3)
-----------------------------------------"
~ SELECT "user_id", "friend_id" FROM "friended_users" WHERE ("user_id" IN (4)) ORDER BY "user_id", "friend_id"

true

~ SELECT "user_id", "friend_id" FROM "friended_users" WHERE ("user_id" IN (1)) ORDER BY "user_id", "friend_id"

true

~ SELECT "users"."id", "users"."name" FROM "users" INNER JOIN "friended_users" ON ("users"."id" = "friended_users"."friend_id") WHERE ("friended_users"."user_id" = 1) GROUP BY "users"."id", "users"."name" ORDER BY "users"."id"

true

~ SELECT "users"."id", "users"."name" FROM "users" INNER JOIN "friended_users" ON ("users"."id" = "friended_users"."user_id") WHERE ("friended_users"."friend_id" = 3) GROUP BY "users"."id", "users"."name" ORDER BY "users"."id"

true

Lastly, note that instead of creating a friendship by calling FriendedUser.create you can also create a friend like this:

jan.friended_users.create(:friend => piet)