SearchPane for child table

montoyam

I have a parent/child setup as designed in the blog:
https://datatables.net/blog/2019-01-11

Is it possible to have a search pane at the top (I guess you would say at the parent level) but the searchpanel will have unique values from the child records and will show parent records that have child records for the selected searchpane element

So, using the blog example, lets say there is a SearchPane at the very top that lists all the last names that are found in the users table. Then, when you select a certain last name, it will show all locations that contain users with that last name. So if a Smith worked at both Edinburgh and London, those two site records would be the only ones to show.

I hope this makes sense.

kthorngren

For that particular example the users are part of the original data for the parent. You could create a third column, hidden using columns.visible that contains the users. Depending on the format of this field you could use something like this example to show the names in a SearchPane:
https://datatables.net/extensions/searchpanes/examples/advanced/renderSearchArrays.html

As long as the data you want to search is part of the parent row data you can setup a way to use SearchPanes to search for it. The data needs to be in a column but can be hidden.

Kevin

montoyam

That example is a perfect example of the 1 to many data structure I have. Thank you. This is a .net project. In sql I can create a view and use 'stuff' to return a comma separated list, but I like the nested json that your example uses. Is it possible to create json like that in .net?

montoyam

I'm guessing this is done through the controller using an MJoin??? If so, I have another question posted trying to figure out how to go that route as I am not clear on how to use MJoin correctly.

montoyam

ok. I figured out the syntax for the controllers and models. It works perfectly. Thank you:

    public class EmployeePoolModel
    {
        public int EmployeePoolID { get; set; }
        public int DepartmentID { get; set; }
        public int JobClassificationID { get; set; }
        public string ContactPerson { get; set; }
        public string EmployeeName { get; set; }
        public int IsAvailable { get; set; }
        public string EnteredBy { get; set; }
        public string RecordAdded { get; set; }
    }

    public class EmployeeSkillModel
    {
        public int EmployeeSkillID { get; set; }
        public string Skill { get; set; }
        public string EnteredBy { get; set; }
        public string RecordAdded { get; set; }
    }


    public class PoolSkillModel
    {
        public int EmployeePoolSkillID { get; set; }
        public int EmployeePoolID { get; set; }
        public int EmployeeSkillID { get; set; }
    }

    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);
            }
        }
    }