How to MJoin on self referencing table?

Blackline1

Hello,

I'm having trouble trying to use MJoin (https://editor.datatables.net/manual/net/mjoin) to pull in items from a self-referencing table. I have no problems using MJoin to pull in items from other tables, but I'm having trouble finding the documentation to pull in child objects from the same table.

I have a database called 'Customers' that holds both 'Customers' and 'ChildCustomers'. A 'ChildCustomer' is a Customer that has Customer.ParentCustomerId == Customer.CustomerId. I don't know how to properly form the .MJoin() to pull in 'ChildCustomers'.

My code is as follows: (Note: I am using .NET libraries)

EditorModel:

namespace MyProject.ViewModels.DatatablesEditor
{
    public class CustomerListDTO : EditorModel
    {
        public class Customers : EditorModel
        {
            public int CustomerId { get; set; }

            public string CompanyName { get; set; }

            public string ClientCode { get; set; }

            public int? ParentCustomerId { get; set; }

            public string TypeId { get; set; }

        }
    }

    public class Contacts : EditorModel
    {
        public int ContactId { get; set; }

        public int CustomerId { get; set; }

        public string ApplicationUserId { get; set; }

        public bool IsPrimary { get; set; }
    }

    public class ChildCustomers : EditorModel
    {
        public string CustomerId { get; set; }

        public string ParentCustomerId { get; set; }
    }
}

Controller

public ActionResult CustomersTable()
        {
            var formData = HttpContext.Request.Form;

            using (var db2 = new DataTables.Database("sqlserver", ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
            {
                var response = new Editor(db2, "Customers", "CustomerId")
                    .Model<CustomerListDTO>()
                    .MJoin(
                        new MJoin("Contacts")
                            .Model<Contacts>()
                            .Where(q => q.Where("Contacts.IsPrimary", true, "="))
                            .Link("Customers.CustomerId", "Contacts.CustomerId")
                    )
                    .Process(formData)
                    .Data();
                return Json(response, JsonRequestBehavior.AllowGet);
            }
        }

Can anyone help me form the proper .MJoin() statement to accomplish this? I know I'll probably have to alias the newly joined Customer object as 'ChildCustomer' somehow.

Also, as a side question: when the user types in the ID of a ChildCustomer into the datatables search box, is it possible to modify the controller to search for and return it's parent customer?

Thanks in advance!

allan

What is really needed here is the ability to add a left join from the Mjoin query. That query is what is getting the list of contacts and then it needs to get the extra information from the customers table.

I'm sorry to say that this is not something that Editor's .NET library currently can do - its on the feature list and I hope to add it in for 1.7. I'll try to take a look later today at what would be involved there as I haven't really scoped that out yet.

Am I correct in saying that you currently get the customer ids from the Mjoin, you just aren't getting their information such as name?

Thanks,
Allan

Blackline1

Hi Allen,

Thanks for the reply. Maybe I should clarify: the .MJoin() on 'Contacts' is irrelevant to my problem with ChildCustomers - perhaps I should have omitted that part from the example code.

Per row in the Customers table, I need to pull in all the other customers in that same table that have a 'ParentCustomerId' equal to that row's primary key CustomerId. In my C# code provided I did not include my attempt to do that. I imagine it would be something like this:

.MJoin(
            new MJoin("Customers")
                .Model<ChildCustomers>()
                .Name("ChildCustomers")
                .Link("Customers.CustomerId", "ChildCustomers.ParentCustomerId")
            )

Here' is a quick example of the table structure:

CustomerId | Name     |  ParentCustomerId | Address,etc. |
1          | AcmeCorp |  NULL             | 123 Main Street
2          | ABC Corp |  1                | 456 Test Ave.
3          | Foo Corp |  1                | 789 Bar Rd.

The end result is that I want to be able to display all the ID's of a customer's children in the grid and also be able to search by them.

With LINQ+EF6, I can generally accomplish this by using .Include(x=>x.ChildCustomers) to populate a property: public ICollection<Customer> ChildCustomers {get;set;}

It may also be worth noting that I'm planning on separating the parents and children into separate views. Let me know if I can clarify anything else. Thanks!

allan

Thanks! I think I understand now - sorry I didn't quite get it initially!

This is the C# code for this demo, which I think is basically want you are looking for:

                var response = new Editor(db, "users")
                    .Model<JoinSelfModel>()
                    .Field(new Field("users.manager")
                        .Options(new Options()
                            .Table("users")
                            .Value("id")
                            .Label(new[] { "first_name", "last_name" })
                        )
                    )
                    .LeftJoin("users as manager", "users.manager", "=", "manager.id")
                    .Process(request)
                    .Data();

Note that this is specifically a one-to-one join (left join) rather than a one-to-many (Mjoin).

Allan

Blackline1

Allen,

That example would work for building the Children view to link together who it's parent was.

I don't believe that approach would work for the Parents view, however. Referring to your example, how would you approach it if you were conversely building a list of those managers, then listing all their staff in a column?

Thanks

allan

Okay - I didn't understand before, but I've got it this time! Although I don't yet have a simple answer for you. Let me get back to you - I'm certain there is a way to do that, but I can't remember it off the top of my head. And it turns out there isn't, I'll add one.

Allan

Blackline1

Hey allan,

Any progress at all? I have about a week or so until the deadline on this. I could definitely get it done using a different implementation but the Editor API is just so nice that I would prefer to continue using it as a standard in the project.

Thanks!!

allan

Currently working on it. The PHP libraries have a bit of a workaround which can be used to do this, but not the .NET ones. I'm working on a proper solution for it at the moment and will let you know when I figure it out (tying myself in knots at the moment!).

Allan