Is it possible to create a self referencing many to many relationship?

Topics: Writing modules
Sep 13, 2014 at 5:08 PM
I am just starting out with creating a module. This module is for the services that our companies' branches provide. Each of these services can be related to any number of other services. Therefore, I was thinking a self referencing many to many relationship would work. However, after looking into this for a while I am thinking this is not possible in Orchard 1.8 and want to make sure I am not missing something.

Here is the code I have that pertains to this issue:
Models
ServicePartRecord:
 public class ServicePartRecord : ContentPartRecord
    {
        public ServicePartRecord()
        {
            RelatedServices = new List<RelatedServicesRecord>();
        }

        public IList<RelatedServicesRecord> RelatedServices { get; set; }
    }
ServicePart:
 public class ServicePart : ContentPart<ServicePartRecord>
    {
        public IEnumerable<RelatedServicesRecord> RelatedServices
        {
            get { return Record.RelatedServices.Select(r => r.RelatedService) }
        }
    }
RelatedServiceRecord:
public class RelatedServicesRecord
    {
        public virtual int Id { get; set; }
        public virtual ServicePartRecord Service { get; set; }
        public virtual ServicePartRecord RelatedService { get; set; }
    }
Migration
SchemaBuilder.CreateTable("ServicePartRecord", table =>
                table.ContentPartRecord()

SchemaBuilder.CreateTable("RelatedServicesRecord", table =>
                table
                    .Column<int>("Id", col => col.PrimaryKey().Identity())
                    .Column<int>("Service_Id")
                    .Column<int>("RelatedService_Id"));

            SchemaBuilder.CreateForeignKey(
                "FK_Related_Service", "RelatedServicesRecord", new[] { "RelatedService_Id" },
                "ServicePartRecord", new[] { "Id" }
            );

            SchemaBuilder.CreateForeignKey(
                "FK_Service", "RelatedServicesRecord", new[] { "Service_Id" },
                "ServicePartRecord", new[] { "Id" }
            );
The database tables get created correctly with the foreign keys, the issue is that Orchard looks for two columns named ServicePartRecord_id in the RelatedServicesRecord table because that is the type of Service and RelatedService. Obviously I cannot have two columns with the same name in the RelatedServicesRecord table, so I am wondering if there is a way to tell Orchard what column to key on. I know that with nHibernate you can specify the key column with KeyColumn("some_id"), but does Orchard support this? Is there a different/better way to do this?

Thanks!
Developer
Sep 14, 2014 at 5:51 PM
I don't know whether this should work OOTB or not but a workaround that comes to my mind could be to add a second record (with only an Id) that you use only for building such relationships.

Otherwise I guess this is something default in NHibernate so most possibly you can also change it: look at ISessionConfigurationEvents. By implementing this interface you can write an event handler that can change part of NHibernate's configuration.
Sep 15, 2014 at 1:03 PM
Thanks Piedone! I will look into ISessionConfigurationEvents.
Sep 15, 2014 at 5:04 PM
I tried something like this:
    public class PersistenceConfiguration : ISessionConfigurationEvents
    {

        public void Created(FluentConfiguration cfg, AutoPersistenceModel defaultModel)
        {
            defaultModel.Override<ServicePartRecord>(mapping => mapping.HasManyToMany(x => x.RelatedServices).Inverse().Table("ModuleName_RelatedServicesRecord").ParentKeyColumn("Service_Id").ChildKeyColumn("RelatedService_Id"));
        }

        public void Prepared(FluentConfiguration cfg) { }
        public void ComputingHash(Hash hash) { }
        public void Building(NHibernate.Cfg.Configuration cfg) { }
        public void Finished(NHibernate.Cfg.Configuration cfg) { }
    }
I now get an error: No row with the given identifier exists[ModuleName.Models.RelatedServicesRecord#19]
I do have an item with ID in the RelatedServicesRecordTable so it is not finding it for some reason. I don't have any NHibernate experience, so I will probably just move on and use a workaround at this point.
Feb 21, 2015 at 5:44 PM
Hi PacmanDave,
Did you find any solution?
I have the exact same scenario and couldn't find any solution yet.

I would appreciate it if you share your solution. (in case you found one)

Thanks,
Alex
Feb 21, 2015 at 6:02 PM
Edited Feb 21, 2015 at 6:03 PM
We ended up not really needing this functionality in this way. What worked well enough for us is have a related service content picker field that was configured to only show service content types and allow multiple. By doing it this way, in your view you have easy access to the related content items.

This isn't necessarily the best solution for what we were trying to accomplish as you have to go to both service content items and choose each other, but at the same time it allows us to possibly not show a service on one page but show it on the other.
Feb 21, 2015 at 6:41 PM
Thanks for quick response.
But unfortunately in my situation I must have self referencing many to many relationship. I continue searching, hopefully I can find a solution. Or perhaps a genius in orchard could help me with this ;-)

Thanks
Feb 22, 2015 at 6:08 AM
@PacmanDave, @AlexLearner

After a quick look, in the ServicePartRecord class for the RelatedServices List, didn't you forget the keyword "virtual"? Maybe your 2nd solution could work

Not sure about the need of a [Cascading attribute] for the List, and / or in your mapping, the need of something like e.g: .Cascade.SaveUpdate()

Orchard supports .Column<int>("Some_Id"), see in the Orchar.Roles migration file. In the RolesPermissionsRecord table definition, a "Role_Id" is used for the RoleRecord, strange there is also a "RoleRecord_Id" ?

Best
Feb 25, 2015 at 3:18 AM
Edited Feb 25, 2015 at 3:21 AM
I've got something working, need to be validated, to add some null checking...

There is a ServicePart implemented in a standard way with normal Driver and Handler (not listed). This part is only used to have the benefits of the content manager (create, edit, display) when attached to a content type, and has a ServiceRecord property to provide a link to a ServiceRecord table. This is the ServiceRecord table that is the real self referencing services table
public class ServicePart : ContentPart<ServicePartRecord> {
    public string Name {
        get { return Record.Name; }
        set { Record.Name = value; }
    }
    public ServiceRecord Service {
        get { return Record.ServiceRecord; }
        set { Record.ServiceRecord = value; }
    }
}
public class ServicePartRecord : ContentPartRecord {
    public virtual string Name { get; set; }
    public virtual ServiceRecord ServiceRecord { get; set; }
}

public class ServiceRecord {
    public ServiceRecord()
    {
        ParentServices = new List<ServiceRecord>();
        ChildServices = new List<ServiceRecord>();
    }
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<ServiceRecord> ParentServices { get; set; }
    public virtual IList<ServiceRecord> ChildServices { get; set; }
}
In Migration file
        SchemaBuilder.CreateTable("ServicePartRecord",
            table => table
                .ContentPartRecord()
                .Column<string>("Name")
                .Column<int>("ServiceRecord_Id")
        );

        ContentDefinitionManager.AlterPartDefinition("ServicePart", cfg => cfg
            .Attachable()
            .WithDescription("Adds a Service Part.")
            );

        SchemaBuilder.CreateTable("ServiceRecord", table =>
            table
                .Column<int>("Id", col => col.PrimaryKey().Identity())
                .Column<string>("Name")
                .Column<int>("ParentService_Id")
                .Column<int>("ChildService_Id")
         );
Mapping
public class PersistenceConfiguration : ISessionConfigurationEvents {
    public void Created(FluentConfiguration cfg, AutoPersistenceModel defaultModel) {
        defaultModel.Override<ServiceRecord>(mapping => {
            mapping.HasManyToMany(m => m.ParentServices)
                .Table("ModuleName_ServiceRecord")
                .ParentKeyColumn("ChildService_Id")
                .ChildKeyColumn("ParentService_Id")
                .Inverse();

            mapping.HasManyToMany(x => x.ChildServices)
                .ParentKeyColumn("ParentService_Id")
                .ChildKeyColumn("ChildService_Id");
        });
    }
I've done all my tests in a ServicePart alternate view (Service.cshtml). Because my ServicePart driver only update the Name property, I've done the ServiceRecord property updating for all my instances in this alternate view, but you can do it in your driver / handler
var contentItem = (ContentItem)Model.ContentItem;
var servicePart = contentItem.As<ServicePart>();
var _serviceRepository = WorkContext.Resolve <IRepository<ServiceRecord>>();
var service = new ServiceRecord() { Name = servicePart.Name };
_serviceRepository.Create(service);
servicePart.Service = service;

Then, you can access a ServicePart / ServiceRecord directly through the ServicePart if present in the context (e.g in a view Model), or by using the content manager. As said, the ServicePart is only used to have an attachable part and linked to a service, so you can directly use a ServiceRecord repository service if you want
Here, some exemples with no null checking for brevety
var contentItem = (ContentItem)Model.ContentItem;
var servicePart = contentItem.As<ServicePart>();

var servicePart = _contentManager.Query<ServicePart, ServicePartRecord>()
    .Where(x => x.Name == "Service X").Slice(0, 1).FirstOrDefault();

var serviceRecord = _serviceRepository.Fetch(record => record.Name == "Service X").FirstOrDefault();
Then, I can add a child service like that
servicePart1.Service.ChildServices.Add(servicePart2.Service);
Or, if you work only with a ServiceRecord repository
serviceRecord1.ChildServices.Add(ServiceRecord2);

So, what happens? The ServiceRecord table has 3 columns: "Name", "ParentService_Id", "ChildService_Id". When I've created the first instances through the ServicePart, only the "Name" column have had some values. Then, when I've added the "Service2" in the child services of "Service1" as above, a new row has been created with the "ChildService_Id" = "Service2 id" and, also the "ParentService_Id" = "Service1 id". So, you only have to add child services, parents are automaticaly updated. Adding a service in a parent collection has no persistent effect, a parent is indirectly added when you add a child. Notice that for these new rows, the "Name" column has no values, this because there are only used for the Parents / Children associations

So, you only have to write in the child services collections, but you can iterate for reading onto child and parent collections, it works


Best
Marked as answer by PacmanDave on 2/25/2015 at 5:17 AM
Feb 25, 2015 at 1:16 PM
@jtkech

Wow, thank you for this! This should help a lot of people with this issue as there were a few unanswered threads on this issue when I first tried to solve it! You rock!
Mar 12, 2015 at 2:03 PM
@jtkech,

Why would you need the "ServiceRecord_Id" column in ServicePartRecord table?
Mar 13, 2015 at 3:57 AM
The ServiceRecord property of type "ServiceRecord" isn't persisted in the ServicePartRecord table, but in the ServiceRecord table, a one to many relation is used. Here, the "ServiceRecord_Id" column of the 1st table is a foreign key and the "Id" column is the primary key of the 2nd table

This can be auto mapped by using a column of the same type as the targeted primary key (on the many side of the relation), and with a name that starts and ends with the related properties, separated by an underscore

Best