mjoin, can't add child record
mjoin, can't add child record
montoyam
Posts: 568Questions: 136Answers: 5
using the advice of the .net mjoin documentation I switched from a direct link to using a link table.
public class EmployeePoolController : ApiController
{
[Route("api/EmployeePool")]
[HttpGet]
[HttpPost]
public IHttpActionResult EmployeePool()
{
var request = HttpContext.Current.Request;
var settings = Properties.Settings.Default;
using (var db = new Database(settings.DbType, settings.DbConnection))
{
var response = new Editor(db, "EmployeePool", "EmployeePoolID")
.Model<EmployeePoolModel>("EmployeePool")
.Field(new Field("EmployeePool.DepartmentID")
.Validator(Validation.NotEmpty())
.Validator(Validation.Numeric())
.Options(new Options()
.Table("Departments")
.Value("DepartmentID")
.Label("DepartmentName")
)
)
.Field(new Field("EmployeePool.JobClassificationID")
.Validator(Validation.NotEmpty())
.Validator(Validation.Numeric())
.Options(new Options()
.Table("JobClassifications")
.Value("JobClassificationID")
.Label("JobClassification")
)
)
.Field(new Field("EmployeePool.RecordAdded")
.Set(false)
)
.Field(new Field("Departments.DepartmentName"))
.Field(new Field("JobClassifications.JobClassification"))
.LeftJoin("Departments", "Departments.DepartmentID", "=", "EmployeePool.DepartmentID")
.LeftJoin("JobClassifications", "JobClassifications.JobClassificationID", "=", "EmployeePool.JobClassificationID")
.MJoin( new MJoin("EmployeeSkills")
.Link("EmployeePool.EmployeePoolID", "EmployeePoolSkillLink.EmployeePoolID")
.Link("EmployeeSkills.EmployeeSkillID", "EmployeePoolSkillLink.EmployeeSkillID")
.Model<EmployeeSkillModel>()
)
.Process(request)
.Data();
return Json(response);
}
}
}
public class EmployeeSkillsController : ApiController
{
[Route("api/EmployeeSkills")]
[HttpGet]
[HttpPost]
public IHttpActionResult EmployeeSkills()
{
var request = HttpContext.Current.Request;
var settings = Properties.Settings.Default;
using (var db = new Database(settings.DbType, settings.DbConnection))
{
var response = new Editor(db, "EmployeeSkills", "EmployeeSkillID")
.Model<EmployeeSkillModel>("EmployeeSkills")
.Model<EmployeePoolModel>("EmployeePool")
.Field(new Field("EmployeeSkills.RecordAdded")
.Set(false)
)
.Field(new Field("EmployeePoolSkillLink.EmployeePoolID")
.Validator(Validation.NotEmpty())
.Validator(Validation.Numeric())
.Options(new Options()
.Table("EmployeePool")
.Value("EmployeePoolID")
.Label("EmployeeName")
)
)
.LeftJoin("EmployeePoolSkillLink", "EmployeePoolSkillLink.EmployeeSkillID", "=", "EmployeeSkills.EmployeeSkillID")
.LeftJoin("EmployeePool", "EmployeePoolSkillLink.EmployeePoolID", "=", "EmployeePool.EmployeePoolID")
.Where("EmployeePoolSkillLink.EmployeePoolID", request.Form["EmployeePoolIDFilter"])
// tried: .Where("EmployeePool.EmployeePoolID", request.Form["EmployeePoolIDFilter"])
.Process(request)
.Data();
return Json(response);
}
}
}
I also am using parent/child editor like this: https://datatables.net/blog/2019-01-11
function createChild(row) {
var rowData = row.data();
// This is the table we'll convert into a DataTable
var table = $('<table class="display" id="skillTable" />');
// Display it the child row
row.child(table).show();
//console.log(rowData);
// Editor definition for the child table
var SkillsEditor = new $.fn.dataTable.Editor({
ajax: {
url: "api/EmployeeSkills",
data: function (d) {
d["EmployeePoolIDFilter"] = rowData.EmployeePool.EmployeePoolID;
console.log(rowData.EmployeePool.EmployeePoolID); //this is returning the correct employeeID when I click the save button in the editor
}
},
table: table,
fields: [
{ label: "Skill:", name: "EmployeeSkills.Skill" },
{
label: "Employee:",
name: "EmployeePoolSkillLink.EmployeePoolID",
type: "select",
placeholder: "Select an Employee",
def: rowData.EmployeePool.EmployeePoolID
},
{
label: "Added By",
name: "EmployeeSkills.EnteredBy",
def: function () {
return userNameCookie;
}
, type: "readonly"
},
{
label: "Record Added",
name: "EmployeeSkills.RecordAdded",
type: "readonly",
def: function () {
var d = new Date();
return d;
}
}
]
});
// Child row DataTable configuration, always passes the parent row's id to server
var SkillsTable = table.DataTable({
dom: "Bt",
pageLength: 5,
ajax: {
url: "api/EmployeeSkills",
type: "post",
data: function (d) {
d["EmployeePoolIDFilter"] = rowData.EmployeePool.EmployeePoolID;
}
},
columns: [
{ title: "Skill", data: "EmployeeSkills.Skill" },
{
data: null,
className: "center",
defaultContent: '<a href="" class="editor_edit">Edit</a> / <a href="" class="editor_remove">Delete</a>'
}
],
select: true,
buttons: [
{ extend: "create", editor: SkillsEditor },
]
});
When I attempt to add a child record I see that a record is created in EmployeeSkills, but not the link table, EmployeePoolSkills
I am assuming that the single api: api/EmployeeSkills should be creating both the child and the link records?
This question has an accepted answers - jump to answer
This discussion has been closed.
Answers
Yes, it should work that way. But for the link table to be filled I think you would need to use an options instance as well like in this example:
https://editor.datatables.net/examples/advanced/joinArray.html
I noticed that behavior as well this morning - and got rid of the mJoin for that particular situation.
You are right - the example in the blog post doesn't quite line up with the schema you are using - it doesn't use a link / junction table.
You can actually have Editor insert into a left joined table (which your link table would be, relative to the child table, in this case) by including the joined table information (this is the code that does that).
The key is to make sure that you include the link table field in the submitted data (i.e. as a regular form field).
Allan
@rt1234, i can't use an options list in this case because the left join (Skills) is a free typing text box.
@allan, I don't understand how to include the link table in the editor. will it be a hidden field?
Sure, I probably had a different issue anyway but found it too cumbersome to figure it out using MJoin. I find MJoin a bit fragile and I've had lots of trouble with it ...
Sometimes it isn't more work to just insert the link table manually. I have "DELETE CASCADE" implemented across the board, so I only need to worry about the INSERT in case of a link table.
This would replace the MJoin in my special case. I need the event handler anyway to write the log ... not more lines of code than the MJoin I would say.
@allan, I don't understand how to include the link table in the editor. will it be a hidden field?
The link table is not included in the Editor. You do not have its fields (which are just keys) on the client side.
The Mjoin returns an array of values that you can see in your Editor form and in the Data Table, but not the link table itself.
Just take a look at this example please: https://editor.datatables.net/examples/advanced/joinArray.html
The "Permissions" column contains the results of the Mjoin and you can see them in the Editor form, too.
3 tables: users => user_permission => permission
As I mentioned above when you gave that link before, I can not use options like this because my child records are free typing text.
So in my master/child controller I am using MJoin, which seems to work perfectly to display the child records. Now, for the child editor, I am using a different controller that starts with the child record, 'EmployeeSkills', and left joins to the link table and the 'parent' table because these will be a 1:1 relationship, not a 1:many. Am I to use mjoin in the child controller as well??
I do see that when I try to add the link table model to the controller i get an error (the multi-part identifier could not be bound). This is just when I add the model (line 16 below). When I comment this line out, I dont get an error, the child record is created, but the link record is not created. So I'm guessing that is a clue that this child controller is not correct??
this controller appears to work correctly for creating the master record and for displaying the master/child records correctly. But, it only adds the master record, not the child and link records.
and here is the js i am trying to add the child record:
"As I mentioned above when you gave that link before, I can not use options like this because my child records are free typing text."
I have the same: the options can be freely defined! BUT: If you want to assign one of the freely defined options to another entity through a link table you MUST be able to select it. How else would you be able to assign them? So the process of assigning a freely defined option to another entity inevitably requires a selection, too.
You might have a different use case?! Maybe you could post parts of your data model to help understand this?!
Here is an example:
The labels in the table ctr_label are freely defined and reside in the field "label_text". To assign one of the freely defined labels to a "ctr" you must select one. No other way ...
This is the "post-it" example if you will. write a "post-it": to really use it you will assign it so something, e.g. the bottom of your computer's screen
That's what my example is about.
Yup - forget what I said about the hidden field, - that's just going to complicate things.
@rf1234 is correct - use a
postCreateevent, almost exactly has he has it, to insert the information into the link table. Since you are POSTing the host row's id already, I think all you need to do is add:Allan
Doh - sorry - you are using C#:
Apologies for any dozy syntax errors - don't have a C# compiler with me atm.
Regards,
Allan
Sorry, I had to switch focus to another part of this application, but I am back to this now.
thank you guys. the code worked perfectly.