Mjoin assistance needed (Populating multiple fields with joined table data)

stuartsjb-icr

I'm trying to write an Mjoin direct link in my server-side script to provide Name values from a secondary table (Marker.Name) for foreign key values in the primary table (ModuleCourseworkMarks.MarkerAID, ModuleCourseworkMarks.MarkerBID, ModuleCourseworkMarks.MarkerCID).

I've had success doing this with just one foreign key in the past but am having a bit of trouble understanding how the Mjoin syntax works, even with the documentation!

So far I've got this:

$editor = Editor::inst($db, 'ModuleCourseworkMarks', 'ModuleCourseworkMarkID')
...
                Field::inst('ModuleCourseworkMarks.MarkerAID')
                ->options('Markers', 'ID', 'Name')
                ->validator( 'Validate::dbValues' ), 
                
                Field::inst('ModuleCourseworkMarks.MarkerBID')
                ->options('Markers', 'ID', 'Name')
                ->validator( 'Validate::dbValues' ), 
                
                Field::inst('ModuleCourseworkMarks.MarkerCID')
                ->options('Markers', 'ID', 'Name')
                ->validator( 'Validate::dbValues' )

                Field::inst('Markers.Name')
...
                ->leftJoin('Student', 'Student.StudentID', '=', 'ModuleCourseworkMarks.StudentID')
                ->leftJoin('ModuleHistory', 'ModuleHistory.ModuleID', '=', 'ModuleCourseworkMarks.ModuleID')
                ->leftJoin('CourseWorkStatus', 'CourseWorkStatus.id', '=', 'ModuleCourseworkMarks.CourseworkStatusID')
                ->leftJoin('Modules', 'Modules.ModuleID', '=', 'ModuleCourseworkMarks.ModuleID')

                ->join(
                    Mjoin::inst('Markers')
                    ->set( false )
                    ->link('ModuleCourseworkMarks.ModuleCourseworkMarkID', 'Markers.ID')
                    ->fields(
                            Field::inst('ModuleCourseworkMarks.MarkerAID'),
                            Field::inst('ModuleCourseworkMarks.MarkerBID'),
                            Field::inst('ModuleCourseworkMarks.MarkerCID')
                            )
                );

courseworkMarksTable = $('#CourseworkMarksTable').DataTable({
...
"columns": [
    {data: "Markers.Name", editField: "ModuleCourseworkMarks.MarkerAID", className: "editableInline"},
    {data: "Markers.Name", editField: "ModuleCourseworkMarks.MarkerBID", className: "editableInline"},
    {data: "Markers.Name", editField: "ModuleCourseworkMarks.MarkerCID", className: "editableInline"}
    ]

but it's resulting in my receiving the following SQL error being alerted in the browser:

"The multi-part identifier "Markers.Name" could not be bound."

Unfortunately with the site running on our intranet server and tightly integrated with our internal SQL database I'd be unable to replicate the issue using JSFiddle or Datatables Live.

stuartsjb-icr

I've answered my own question here, and was barking up the wrong tree looking at Mjoin. I've altered my code as follows and this has produced the desired result.

...
Field::inst('MarkerATable.Name'),
Field::inst('MarkerBTable.Name'),
Field::inst('MarkerCTable.Name')
...
->leftJoin('Markers AS MarkerATable', 'MarkerATable.ID', '=', 'ModuleCourseworkMarks.MarkerAID')
->leftJoin('Markers AS MarkerBTable', 'MarkerBTable.ID', '=', 'ModuleCourseworkMarks.MarkerBID')
->leftJoin('Markers AS MarkerCTable', 'MarkerCTable.ID', '=', 'ModuleCourseworkMarks.MarkerCID');

...
{data: "MarkerATable.Name", editField: "ModuleCourseworkMarks.MarkerAID", className: "editableInline"},
{data: "MarkerBTable.Name", editField: "ModuleCourseworkMarks.MarkerBID", className: "editableInline"},
{data: "MarkerCTable.Name", editField: "ModuleCourseworkMarks.MarkerCID", className: "editableInline"}
...

Anyone who's run into the same confusion as me should look here.

allan

A many join insert is not something that Editor currently supports I'm afraid. Its UI on the client-side simply doesn't allow for it. However, it does support left joins (one to one) .

Good to hear you've got it going as you need!

Allan