Orchard and M:M Data Relations

Topics: Writing modules
Jul 6, 2012 at 11:22 AM
Edited Jul 8, 2012 at 9:52 AM

I have a question regarding the naming conventions in Orchard relating to M:M joins and self joins. I noticed from the documentation and on-line samples that there are some naming conventions in place.

I haven't tried this code, so bear with me, I'm just trying to get my head around a few of the concepts relating to the data layer before I get started. Will Orchard allow many to many joins with both sides of the joining table, being the same table?

The following is an example of what I mean.......A person object can have many relationships to another person object and vice versa, for example, a person can have many peers, a person can have many managers, and one of the managers may also be a peer to that person. 

 

public class PersonRecord : ContentPartRecord {
        public PersonRecord() {
            PersonRelations = new List<PersonRelationRecord>();
        }
        public virtual IList<PersonRelationRecord> PersonRelations { get; set; }
    }

public class PersonRelationRecord {
        public virtual int Id { get; set; }
        public virtual PersonRecord Person {get; set;}
        public virtual PersonRecord Relative {get; set;}
        public virtual RelationTypeRecord RelativeType {get; set;}
}

public class RelationTypeRecord {
        public virtual int      Id { get; set; }
        public virtual string   Name { get; set; }
        public virtual string   Description { get; set; }
}

The schema I would assume would look something like the following:

 SchemaBuilder.CreateTable("PersonRecord", t => t
                .ContentPartRecord()
                ....
                );

SchemaBuilder.CreateTable("RelationTypeRecord", t => t
                .Column("Id", c => c.PrimaryKey().Identity())
                .Column("Name", c => c.WithLength(50))
                .Column("Description", c => c.WithLength(255))
                );

            SchemaBuilder.CreateTable("PersonRelationRecord", t=> t
                 .Column("Id", c => c.PrimaryKey().Identity())
                 .Column("PersonRecord_Id", c => c.NotNull())
                 .Column("PersonRecord_RelativeId", c => c.NotNull())
                 .Column("RelativeTypeRecord_Id", c => c.NotNull())
                 );

As you can see from the SchemaBuilder for the PersonRelationRecord, I have proposed the naming convention of tablename and id for the Person Id, but that naming convention would not work for the RelativeId which is also a link back to the PersonRecord.

a) How would you correctly link back the RelativeId to the person table?

b) Must the naming convention be used?

c) If we can bypass the naming convention is there some data annotation that can be used to indicate the mapping, or do you simply set foreign keys using SchemaBuilder.CreateForeignKey() and nHibernate picks up mappings from that.

d) Is there an alternative ?

 

Thanks

Coordinator
Jul 11, 2012 at 3:00 PM

Please read http://docs.orchardproject.net/Documentation/Creating-1-n-and-n-n-relations

Jul 11, 2012 at 5:43 PM

Thanks for the response,  and as I said I'm trying to understand the way data models should be created using Orchard.

So taking the M:M rewards example in the docs as a guide, am I correct in thinking the following would be a suitable approach: -

1) Do not have a List of PersonRelations in the Person Content Type as indicated in my example

2) Instead, create a Relations part record which contains a collection of PersonRelationRecords

3) Create a Relations content part which proxies the PersonRelations property to the record using a filter to the person on the PersonRelationRecord.

4) Attach the Relations content part to the Person content type, so creating the relationship (obviously migration also used in background)

5) Code a handler and service for the Relations part in a similar manner as the rewards example, for data retrieval / storage passing, injecting the person as filter

6) For the purpose of adding / removing relationships, a simple panel, some Jquery, and a person lookup service could be utilized along with a suitable view model and display view.

Thanks

Coordinator
Jul 11, 2012 at 6:08 PM

That sounds about right.

Jul 12, 2012 at 5:20 AM
Edited Jul 12, 2012 at 7:53 AM

With regards to the naming conventions then..... based on a comment you made in another discussion, as long as the migration code in the association table includes the table column as the property name and _Id (for example, Person_Id and Relative_Id), then Orchard, and nHibernate should be able to associate the records appropriately even though they link back to the same column on the same table.

Thanks again.