Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines
I mean Admin panel The case is pretty simple 2 tables with many to many relation via 3rd table like student, courses and junction table student-courses
if I edit or create a record in the student table I want to choose 1or more courses - actually it means new records in the junction table but it should work from the student table resource ... etc
In this case, you need to create a column "Courses" inside Student resource which has the Select2 multiple selection as input. This column is only available in Update and Create screen. In this Select2 input will be provided list of courses inside dataSource(). When the UpdateScreen/CreateScreen of student open, The Select2 will allow you to select one or more courses. When click Save, list of course id will be sent to server in format of array. Admin panel will help to convey those data to methods called updateRecord($conditions, $data)
and createRecord($data)
depend on screens.
These methods can be overwritten inside Student resource. So what you do inside that updateRecord() and createRecord() is to create/update student, and insert/update the list of course to the StudentCourse (linked table).
Hi Eugene, the code will be something like this:
You need first to create a CourseField, a virtual field to mange the list of courses in student table:
<?php
use demo\AdminAutoMaker;
use koolreport\dashboard\Field;
class CourseField extends Field
{
protected $oriValue = null; // Use this value to avoid multiple query to database
protected function onCreated()
{
$this->valueType("array");
}
public function orginalValue()
{
if($this->oriValue === null)
{
// Make query to database to get list of couses of particular student
$arrayStudentCourseIds = AdminAutoMaker::table("student_course")
->select("course_id")
->where("student_id",$this->row["student_id"])
->run()
->pluck("course_id");
//Save to oriValue
$this->oriValue = $arrayStudentCourseIds;
}
return $this->oriValue;
}
public function defaultFormatValue($value, $row = null)
{
// To show list of student courses in array formats
return json_encode($value);
}
}
Now in the Student resource, you do this:
<?php
use koolreport\dashboard\admin\Resource;
use demo\AdminAutoMaker;
use koolreport\dashboard\fields\ID;
use koolreport\dashboard\fields\Text;
use koolreport\dashboard\inputs\Select2;
class Student extends Resource
{
protected function onCreated()
{
$this->manageTable("students")->inSource(AdminAutoMaker::class);
}
protected function fields()
{
return [
ID::create("student_id"),
Text::create("student_name"),
Text::create("student_email"),
Text::create("student_phone"),
Text::create("student_address"),
CourseField::create("courses")
->inputWidget(
Select2::create()
->dataSource(function(){
return AdminAutoMaker::table("courses")
->select("course_id","course_name");
})
->multiple(true)
->value("course_id")
->text("course_name")
)
->showOnAll(false)
->showOnCreate(true)
->showOnUpdate(true)
];
}
public function createRecord($data)
{
$couses = null;
if(isset($data["courses"])) {
$courses = $data["courses"];
unset($data["courses"]);
}
$result = parent::createRecord($data);
if($result === false) {
return false;
}
//Now we will insert student courses into student_course table
if(is_array($courses)) {
foreach($courses as $course_id) {
AdminAutoMaker::table("student_course")->insert([
"student_id"=>$data["student_id"],
"course_id"=>$course_id
])->run();
}
}
return true;
}
public function updateRecord($ids,$data)
{
$couses = null;
if(isset($data["courses"])) {
$courses = $data["courses"];
unset($data["courses"]);
}
$result = parent::updateRecord($ids,$data);
if($result === false) {
return false;
}
//Now we delete all records in student_course table
AdminAutoMaker::table("student_course")
->where("student_id",$data["student_id"])
->delete()
->run();
//And then insert again
if(is_array($courses)) {
foreach($courses as $course_id) {
AdminAutoMaker::table("student_course")->insert([
"student_id"=>$data["student_id"],
"course_id"=>$course_id
])->run();
}
}
return true;
}
}
Hope that helps.
Thank you, but I met with some issues
CourseField::create("courses")
- table Students does not have any column courses
because the relations is organized via 3rd table student_courses - it generate sql error Column not found: 1054 Unknown column 'students.courses' in 'field list'
the following generate error that method text is not exist
->value("course_id")
->text("course_name")
I changed it like this but I cannot be sureis it correct or not coz control does not appear
->fields(function () {
return [
Number::create('course_id'),
Text::create('course_name')
];
})
$this->autoColName = false;
in CourseField and don't use any clolumn name in CourseField::create()
I get the error that Call undefined showOnCreate() method
....If I delete all that showOn methods maximum what I got is the column CourseField at the list screen - just array value and absolutely nothing in the Create and Update screens.
I really do not know where to go now.
I am thinking of a solution that's possible work for Create New record. You can create an your own action and add to Action Group Box. In action, there is a form in which you can add your own inputs. Let take example for Student resource, you can add inputs for "name", "age" ... and extra multiple select2 box for "courses".
After user click submit form, you can get user's inputs in the handle() method through parameters called $form. You get values from form and insert into database by your own including the update to "student_course" table.
How do you think of this solution.
Eugene, I think KoolReport's previous answer could work if we build a "courses" data column for the Student table by using both sql for joining tables and some PHP processes to merge rows of the same student with different courses into one row.
Then you could display the "courses" column, edit it with Select2 and save it into database with custom createRecord and updateRecord methods.
Another solution is to use a separate Resource and AdminTable to manage the StudentsCourses table itself with multiple rows of student id, student name (RelationalLink), course id, course name (RelationalLink).
Sebastian, thank you so much.
Could you please make for me a bit more clear this idea: "we build a "courses" data column for the Student table by using both sql for joining tables and some PHP processes to merge rows of the same student with different courses into one row."
Regarding the second solution with a separate Resource and AdminTable to manage the StudentsCourses, I have already thought about it like about the last choice option. Sure it will work but I think you agree that this solution is very far from user friendly approach. I hope I can realize something where the connection table is hidden from the user using your first idea.
Let KoolReport help you to make great reports. It's free & open-source released under MIT license.
Download KoolReport View demo