HasAndBelongsToMany

» Home » ActiveRecord » Documentation » ActiveRecord RC1 Documentation » User's Guide » Relations » HasAndBelongsToMany

A many-to-many relation can be mapped using the HasAndBelongsToMany. As usual it requires an association table.

Consider the following table script:


CREATE TABLE Posts 
(
    [id] [int] IDENTITY (1, 1) NOT NULL,
    [title] [varchar] (50) NULL,
    [contents] [text] NULL
) ON [PRIMARY]

CREATE TABLE Categories
(
    [id] [int] IDENTITY (1, 1) NOT NULL,
    [title] [varchar] (50) NULL,
) ON [PRIMARY]

CREATE TABLE PostCategory
(
    [postid] [int] NOT NULL,
    [categoryid] [int] NOT NULL
) ON [PRIMARY]

The relation is that a post can have many categories and thus a category can be related to many posts.


using Castle.ActiveRecord;

[ActiveRecord("posts")]
public class Post : ActiveRecordBase
{
    private int id;
    private string title;
    private string contents;
    private Blog blog;
    private IList categories = new ArrayList();
    
    [PrimaryKey]
    private int Id
    {
        get { return id; }
        set { id = value; }
    }

    [Property]
    public string Title
    {
        get { return title; }
        set { title = value; }
    }

    [Property(ColumnType="StringClob")]
    public string Contents
    {
        get { return contents; }
        set { contents = value; }
    }
    
    [BelongsTo("blogid")]
    public Blog OwnerBlog
    {
        get { return blog; }
        set { blog = value; }
    }
    
    [HasAndBelongsToMany(typeof(Category), 
        Table="PostCategory", ColumnKey="postid", ColumnRef="categoryid")]
    public IList Categories
    {
        get { return categories; }
        set { categories = value; }
    }
}

The other side of the relation can be mapped identically. The only change is the inversion of ColumnKey and ColumnRef. It is wise to choose one side of the relation as the owner. The other side, the non-writable, need to use Inverse=true.

In the example above it would be semantically correct to have the Post class controlling the relation. The other side Category can optionally have a list of Posts, and will use Inverse=true.


using Castle.ActiveRecord;

[ActiveRecord("categories")]
public class Category : ActiveRecordBase
{
    private int id;
    private string title;
    private IList posts = new ArrayList();
    
    [PrimaryKey]
    private int Id
    {
        get { return id; }
        set { id = value; }
    }

    [Property]
    public string Title
    {
        get { return title; }
        set { title = value; }
    }

    [HasAndBelongsToMany(typeof(Post), 
        Table="PostCategory", ColumnKey="categoryid", ColumnRef="postid", Inverse=true)]
    public IList Posts
    {
        get { return posts; }
        set { posts = value; }
    }
}

Quick Note

We cannot stress enough how important it is to define a proper Cascade behavior for your relations in a real world application.

More information on the attribute can be found at Attributes article.

Attributes on the association table

More than often the association table in a many-to-many relation is used to hold association attributes. In the example used so far, the PostCategory could have some columns to hold some arbitrary data.

It is desirable then to map this relation correctly to a class that represents the association table. So create a PostCategoryActiveRecord type. Now comes the trick part: ActiveRecord classes must have primary keys. So you have two options. Either you add a surrogate key to your association table or you use composite key.

The current support for composite keys does not support relations as the keys, although this is supported by NHibernate. Nevertheless this is on the Roapmap and should be implemented by the next version.

Association table with surrogate key

On this approach we introduce a primary key in a table where, semantically, the key could be the the two foreign keys.


CREATE TABLE PostCategory
(
    [id] [int] IDENTITY (1, 1) NOT NULL,
    [postid] [int] NOT NULL,
    [categoryid] [int] NOT NULL,
    [arbitraryvalue] [int] NULL
) ON [PRIMARY]

Now it is just a matter of implementing the class as you normally would.


using Castle.ActiveRecord;
using NHibernate.Expression;

[ActiveRecord]
public class PostCategory : ActiveRecordBase
{
    private int id;
    private Post post;
    private Category category;
    private int arbitraryvalue;
    
    [PrimaryKey]
    private int Id
    {
        get { return id; }
        set { id = value; }
    }

    [BelongsTo("postid")]
    public Post Post
    {
        get { return post; }
        set { post = value; }
    }

    [BelongsTo("categoryid")]
    public Category Category
    {
        get { return category; }
        set { category = value; }
    }
    
    [Property]
    public int ArbitraryValue
    {
        get { return arbitraryvalue; }
        set { arbitraryvalue = value; }
    }
    
    public static PostCategory[] FindByPost(Post post)
    {
        return FindAll(typeof(PostCategory), Expression.Eq("Post", post));
    }

    public static PostCategory[] FindByCategory(Category category)
    {
        return FindAll(typeof(PostCategory), Expression.Eq("Category", category));
    }
}

As you see we introduced find methods to allow the retrival of instances based on an specific post or category.

Using a composite key

The composite key approach does not require the introduction of a surrogate key, but requires more work and can be used with relations (yet). As you can see the post and category are represent by their ids instead of Post and Category instance. This is highly undesirable for object oriented domain models.


using Castle.ActiveRecord;
using NHibernate.Expression;

[Serializable]
public class PostCategoryKey
{
    private int postid;
    private int categoryid;
    
    [KeyProperty]
    public int PostId
    {
        get { return postid; }
        set { postid = value; }
    }

    [KeyProperty]
    public int CategoryId
    {
        get { return categoryid; }
        set { categoryid = value; }
    }
    
    public override int GetHashCode()
    {
        return postid ^ categoryid;
    }

    public override bool Equals(object obj)
    {
        if (this == obj)
        {
            return true;
        }
        PostCategoryKey key = obj as PostCategoryKey;
        if (key == null)
        {
            return false;
        }
        if (postid != key.postid || categoryid != key.categoryid)
        {
            return false;
        }
        return true;
    }
}

[ActiveRecord]
public class PostCategory : ActiveRecordBase
{
    private PostCategoryKey id;
    private int arbitraryvalue;
    
    [CompositeKey]
    public PostCategoryKey Id
    {
        get { return id; }
        set { id = value; }
    }
    
    [Property]
    public int ArbitraryValue
    {
        get { return arbitraryvalue; }
        set { arbitraryvalue = value; }
    }
    
    public static PostCategory[] FindByPost(Post post)
    {
        return FindAll(typeof(PostCategory), 
            Expression.Eq("PostCategory_postid", post.Id));
    }

    public static PostCategory[] FindByCategory(Category category)
    {
        return FindAll(typeof(PostCategory), 
            Expression.Eq("PostCategory_categoryid", category.Id));
    }
}

Home

Castle Stronghold

HasAndBelongsToMany

Attributes on the association table

Association table with surrogate key

Using a composite key