JOIN an MJOIN

rpmccormick

I asked this long ago, and the answer was no way, but I thought I would check again and see if there has been any update.

I have a lot of tables that link 2 other tables. For example, I have "Clients", I have "Hotels" and I have a lookup table called "ClientHotels". I do this because each hotel can have multiple clients, and each client can have multiple hotels.

It every Hotel belonged to one Client, I could MJOIN a list of Hotels inside the details pane of the Clients table. Is there any way to accomplish the same thing... list of Hotels under the Clients table, and list of Clients under Hotels table... using the lookup table "ClientHotels" to link their IDs?

I suppose I could make a VIEW that joins one, and another view that joins the other, then each table could MJOIN the view... is that the only way to do it?

rf1234

"Is there any way to accomplish the same thing... list of Hotels under the Clients table, and list of Clients under Hotels table... using the lookup table "ClientHotels" to link their IDs?"

Sure that is what an Mjoin is made for! But maybe I don't understand the question?!

Here is an example:
This table "ctr_installation" has multiple public authorities, principals and departments. And these are n:m relationships, not 1:n relationships in the relational data model.

Here is an excerpt of the data model (MySqlWorkbench):

I marked the Mjoin Link Tables in yellow. Looks kind of greenish now ...

This is what it looks like in the data table. The Mjoin cols are marked in yellow. The advantage of the MJoin is that you can edit the link tables with Editor.

And you can also have it vice versa: Showing the installations a department belongs to as opposed to showing the departments of an installation:

This is the php code with multiple MJoins:

Editor::inst( $db, 'ctr_installation' )
->field(
    Field::inst( 'ctr_installation.id' )->set( false ),
    ...............................
->join(
Mjoin::inst( 'gov' )
    ->link( 'ctr_installation.id', 'ctr_installation_has_gov.ctr_installation_id' )
    ->link( 'gov.id', 'ctr_installation_has_gov.gov_id' )
    ->order( 'gov.name asc' )
    ->fields(
        Field::inst( 'id' )->set( false )                
            ->options( Options::inst()
                ->table('gov')
                ->value('id')
                ->label( array('name', 'regional_12') )
                ->render( function ( $row ) {   
                    return $row['name'] . ' ('.$row['regional_12'].')';
                } )
                ->order( 'name asc' )
                ->where( function($q) {
                    //$q ->where('gov.is_client_ctr', 1 );
                } )
            ),
        Field::inst( 'name' )->set( false ),
        Field::inst( 'regional_12' )->set( false )
    )
)
->join(
Mjoin::inst( 'user' )
    ->link( 'ctr_installation.id', 'ctr_installation_has_principal.ctr_installation_id' )
    ->link( 'user.id', 'ctr_installation_has_principal.user_id' )
    ->order( 'user.lastname asc' )
    ->fields(
        Field::inst( 'id' )->set( false )                
            ->options( Options::inst()
                ->table('user')
                ->value('id')
                ->label( array('acad', 'lastname', 'firstname') )
                ->render( function ( $row ) {   
                    if ($row['acad'] > '') {
                        return $row['lastname'] . ', ' . $row['acad'] . ' '. $row['firstname'];        
                    } else {
                        return $row['lastname'] . ', ' . $row['firstname'];      
                    }
                } )
                ->order( 'lastname, firstname asc' )
                ->where( function($q) { 
                    //users must be able to choose themselves as principals if they are already principals!
                    //$q  ->where('user.id',  $_SESSION['id'], '!=' ); 
                    if ( $_SESSION['type'] !== "L" ||
                         ( $_SESSION['role'] !== "Administrator" &&
                           $_SESSION['role'] !== "Principal"        ) ) { 
                        $q  ->where( function ( $r ) { 
                            $installationId = 0;
                            if ( isset($_SESSION['ctr_installation_id'] ) ) {
                                $installationId = $_SESSION['ctr_installation_id'];
                            }
                            //all users that belong to the gov(s) of the respective ctr_installation for which the user is a principal
                            $r  ->where( 'user.id',  
                                '( SELECT DISTINCT a.user_id 
                                    FROM gov_has_user a
                              INNER JOIN ctr_installation_has_gov b         ON a.gov_id = b.gov_id    
                              INNER JOIN ctr_installation c                 ON b.ctr_installation_id = c.id
                              INNER JOIN ctr_installation_has_principal d   ON c.id = d.ctr_installation_id                      
                                   WHERE d.ctr_installation_id = :instId 
                                   ORDER BY a.user_id ASC
                                   )', 'IN', false);
                            $r  ->or_where( 'user.updater_id', $_SESSION['id'], '=' );
                            $r  ->bind( ':instId', $installationId );
                        } );
                    }
                } )
            ),
        Field::inst( 'acad' )->set( false ),
        Field::inst( 'lastname' )->set( false ),
        Field::inst( 'firstname' )->set( false ),
        Field::inst( 'id' )->set( false )           
    )
)
->join(
Mjoin::inst( 'ctr_govdept' )
    ->link( 'ctr_installation.id', 'ctr_govdept_has_ctr_installation.ctr_installation_id' )
    ->link( 'ctr_govdept.id', 'ctr_govdept_has_ctr_installation.ctr_govdept_id' )
    ->order( 'ctr_govdept.dept_name asc' )
    ->fields(
        Field::inst( 'dept_name' )->set( false )
    )
)
->where( function ( $q ) {
    if ( isset($_SESSION['govCredUserId']) ) {
        $q  ->where( function ( $r ) { 
            //all installations that have departments for which the user is an administrator or principal
            $r  ->where( 'ctr_installation.id',  
                '( SELECT DISTINCT a.ctr_installation_id
                    FROM ctr_govdept_has_ctr_installation a
              INNER JOIN ctr_govdept b                   ON a.ctr_govdept_id = b.id    
              INNER JOIN ctr_govdept_has_user_complete c ON b.id = c.ctr_govdept_id     
                   WHERE c.user_id = :id
                     AND c.role IN ("Administrator", "Principal" )
                   )', 'IN', false);
            $r  ->bind( ':id', $_SESSION['govCredUserId'] );
        } );
    }
} )                        
.............................
->process($_POST)            
->json();

And this is the php code with the installations a department can belong to (opposite case using the same link table "ctr_govdept_has_ctr_installation":

Editor::inst( $db, 'ctr_govdept' )
->field(
    Field::inst( 'ctr_govdept.id' )->set( false ),
    ......................
->leftJoin( 'ctr_govdept_has_ctr_installation', 'ctr_govdept.id', '=', 'ctr_govdept_has_ctr_installation.ctr_govdept_id')
->join(
Mjoin::inst( 'ctr_installation' )
    ->link( 'ctr_govdept.id', 'ctr_govdept_has_ctr_installation.ctr_govdept_id' )
    ->link( 'ctr_installation.id', 'ctr_govdept_has_ctr_installation.ctr_installation_id' )
    ->order( 'ctr_installation.inst_name asc' )
    ->fields(
        Field::inst( 'id' )->set( false )                
            ->options( Options::inst()
                ->table('ctr_installation')
                ->value('id')                    
                ->label( 'inst_name' )
                ->order( 'inst_name asc' )
            ),
        Field::inst( 'inst_name' )->set( false )
    )
)
->where( function ( $q ) {        
    $q  ->where( 'ctr_govdept_has_ctr_installation.ctr_installation_id', $_POST['ctr_installation_id'] );
} )
................
->process($_POST)            
->json();

if you don't need to edit the relationship in your respective use case you do not have to use the mJoin because it is rather limited. You can't return arrays using really complex queries with it. What you can do is to imitate an Mjoin using your own SQL. Like this for example:

Editor::inst( $db, 'ctr_govdept' )
->field(
    Field::inst( 'ctr_govdept.id' )->set( false ),    
    Field::inst( 'ctr_govdept.id AS gov' )->set( false )   //return same format as an MJoin             
        ->getFormatter( function($val, $data, $opts) {
            return getFormatterGovArray($val);
        }),
    ..............................
)
->where( function ( $q ) {
    ....................
} )
............
} )
->process($_POST)
->json();

function getFormatterGovArray($ctrGovdeptId) {
    global $dbh;
    
    $dbh->query('SELECT DISTINCT a.name AS govName, a.regional_12 AS govRegional12
                   FROM gov a
             INNER JOIN ctr_installation_has_gov b          ON a.id = b.gov_id
             INNER JOIN ctr_installation c                  ON b.ctr_installation_id = c.id
             INNER JOIN ctr_govdept_has_ctr_installation d  ON c.id = d.ctr_installation_id
                  WHERE d.ctr_govdept_id = :ctrGovdeptId
               ORDER BY 1 ASC');
    $dbh->bind(':ctrGovdeptId', $ctrGovdeptId); 
    
    return $dbh->resultsetAssoc();     
}

The function "getFormatterGovArray" returns the same format as an Mjoin would do and you can use the result at the front end like it was a real Mjoin. As you can see you would be unable to achieve the same result with a real Mjoin due to its limiations.

Hope that helps! Good luck!

rpmccormick

Wow, that is awesome. Thanks. Amazing work.

If you don't mind posting a little more... how do you get the Selection of Principals (selectize?) box to send data to Editor? ...what does its php-api file look like (you can't use the one with joins for yout editor right)? ...and I would like to see your code that populates its options too? I do have a way to do both, but I'm guessing your way might teach me a thing or two

Thanks again.

rf1234

"how do you get the Selection of Principals (selectize?) box to send data to Editor?"

These are the Editor field definitions on the client side for the two Mjoins "Public Authority Selection" and "Selection of Principals". That's all you need. This communicates directly with the two PHP-Mjoins above.

}, {
    label: lang === 'de' ? 'Behördenauswahl:' : 'Public Authority selection:',
    name: "gov[].id", //render gov_name (regional_12)
    type: "selectize",
    opts: {
        create: false,
        maxItems: null,
        openOnFocus: true,
        allowEmptyOption: false,
        placeholder: lang === 'de' ? 'Bitte wählen Sie eine oder mehrere Behörde(n)' : 'Please select one or more Public Authority(ies)',
    }
}, {
    label: lang === 'de' ? 'Auswahl von Hauptansprechpartnern:' : 'Selection of Principals:',
    name: "user[].id", //render firstname, lastname
    fieldInfo: ctrInstallationPage ? ( lang === 'de' ? '<span class="text-danger"> \n\
        Bitte achten Sie darauf, sich nicht selbst \n\
        als Hauptansprechpartner zu löschen!</span>' : 
        '<span class="text-danger"> Please make sure you \n\
        do not delete yourself as principal!</span>' ) : '',
    type: "selectize",
    opts: {
        create: false,
        maxItems: null,
        openOnFocus: true,
        allowEmptyOption: false,
        placeholder: lang === 'de' ? 'Bitte einen oder mehrere Hauptansprechpartner wählen' : 'Please select one or more Principals',
    }
}

"and I would like to see your code that populates its options too?"

You've seen it already: It is the two options instances INSIDE the Mjoins.The Mjoins on "gov" and "user" both have options instances that populate the selectize fields on the front end.

The third Mjoin on "ctr_govdept" doesn't have an options instance because it is only used to display the departments not to select them with Editor.

So two of the three "Mjoin-arrays" from PHP (or whatever you want to call them) are used in Editor - and hence have options instances that are used by Selectize (or select or select2 or whatever you want to use). One of the three is not used in Editor - and has no options instance.

All three of them are displayed in the data table like this. Yes, you can do the rendering more elegantly but it works:

{   data: "gov",
    render: function ( data, type, row ) {
        var ix=0;
        var returnString = '';
        while (row.gov[ix]) {
            if (ix !== 0) {
                returnString += '<br>';
            }
            returnString +=
                ( row.gov[ix].name + ' (' +
                  row.gov[ix].regional_12 + ')' );
            ix++;       
        }
        return returnString;
    }
},
{   data: "user",
    render: function ( data, type, row ) {
        var ix=0;
        var returnString = '';
        while (row.user[ix]) {
            if (ix !== 0) {
                returnString += '<br>';
            }
            if (row.user[ix].acad > '') {
                returnString += 
                ( row.user[ix].lastname + ', ' + row.user[ix].acad + ' ' + row.user[ix].firstname );        
            } else {
                returnString +=
                ( row.user[ix].lastname + ', ' + row.user[ix].firstname ); 
            }
            ix++;       
        }
        return returnString;
    }
},
//        {   data: "ctr_govdept", render: "[,<br>].dept_name" },
{   data: "ctr_govdept",
    render: function ( data, type, row ) {
        var ix=0;
        var returnString = '';
        while (row.ctr_govdept[ix]) {
            if (ix !== 0) {
                returnString += ',<br>';
            }
            returnString += row.ctr_govdept[ix].dept_name;
            ix++;       
        }
        if (returnString <= '') {
            return lang === 'de' ? '<p class="text-danger">Bitte mindestens \n\
                   eine Abteilung anlegen.</p>' : '<p class="text-danger">\n\
                   Please create at least one department.</p>';
        }
        return returnString;
    }
},

If you only need to display data and can't or don't want to use an Mjoin you can use a function like "getFormatterGovArray" that returns the same format as an Mjoin would do. The rendering for this on the front end side can be very simple like this:

{   data: "gov", render: "[,<br>].govName"  },
{   data: "gov", render: "[,<br>].govRegional12"  }

The trick in PHP is to alias an existing MySQL-table field and use this "container" to get the pseudo-Mjoin-Array from PHP to the front end:

Field::inst( 'ctr_govdept.id AS gov' )->set( false )   //return same format as an MJoin            
    ->getFormatter( function($val, $data, $opts) {
        return getFormatterGovArray($val);
    }),

I am using "ctr_govdept.id" and alias it to "gov". This also makes it very easy to pass this id into the gefFormatter that creates the "Mjoin-array". It is the parameter $val that Editor reads from the database.

allan

That's a heck of a solution! Thanks for posting all those details @rf1234.

The other option is to use a VIEW as @rpmccormick originally noted. There is no leftJoin option inside the Mjoin instance at this time.

Allan

rf1234

@allan, yes views are really useful for this! Here is a more complex example from the world of "financial weapons of mass destruction" (Warren Buffet). Let me call them "derivatives" which is shorter ...

Many German municipalities still have this stuff on their books. Their use is forbidden these days when it comes to mere speculation. According to the law you must assign an "underlying" contract that is "hedged" by the derivative.

In terms of my data model both, the derivative, e.g. an interest rate swap and the underlying, e.g. a variable rate loan financing a school for example, are contracts. (The benefit of the swap could be that it will eliminate the interest rate change risk when done properly. But of course you can ask the question: Why would you finance a school with a variable rate loan in the first place?)

So what the Mjoin needs to do is to assign one ore more contracts (the underlying(s)) to the derivative. Even more complicated: One underlying can be hedged by multiple derivatives and one derivative can hedge multiple underlyings (n:m relationship).

This only works with a view that selects the potential underlyings from the contract table. Here is an excerpt from my data model that shows this. The foreign keys in the link table "derivative_has_underlying" are the contract id of the derivative and the contract id of the underlying. Hence the link table links the contract table with itself.

And this is the Mjoin that does the job:

//single derivative contracts need to have underlying contracts assigned to them
//without an assignment of an underlying they may not become approved.
//the array of the underlyings is displayed in the data table
->join(
Mjoin::inst( 'underlying' )
    ->link( 'contract.id', 'derivative_has_underlying.derivative_contract_id' )
    ->link( 'underlying.id', 'derivative_has_underlying.underlying_contract_id' )
    ->order( 'serial, instrument, type asc' )
    ->fields(
        Field::inst( 'id' )->set( false )
            ->options( Options::inst()
                ->table( 'underlying' )
                ->value( 'id' )
                ->label( array('serial', 'instrument', 'type', 'number') )
            //render serial, instrument, type, number
                ->render( function ( $row ) {               
                    return '# '.$row['serial']
                            .' / '.renderInstrument($row['instrument'])
                            .' / '.renderTypeDerivative($row['type'])
                            .' ('.$row['number'].')';
                } )
                ->order( 'serial, instrument, type asc' )
                //where clause MUST be a closure function in Options!!!
                ->where( function($q) {
                    $q ->where( function($r) {
                        $r ->where('govdept_id', $_SESSION['govdept_id'] );
//                                $r ->where('instrument', 'X', '<' );
    //currently we only allow loans to be assigned as underlyings to derivatives
                        $r ->where('instrument', 'W', '<' );
                    });
                } )
            ),
        Field::inst( 'serial' )->set( false ),
        Field::inst( 'instrument' )->set( false ),    
        Field::inst( 'type' )->set( false ),
        Field::inst( 'number' )->set( false )
    )
)

This is the front end code for it:

}, {
    label: lang === 'de' ? 
            'Wählen Sie einen oder mehrere Verträge über Basisgeschäfte:' : 
            'Select one or more underlying contracts:',
    name:  "underlying[].id", //render serial, instrument, type, number
    type: "selectize", 
    opts: {
        create: false,
        maxItems: null,
        openOnFocus: true,
        allowEmptyOption: true,
        placeholder: lang === 'de' ? 
            'Bitte Grundgeschäft auswählen' : 'Please select underlying'
        }
}, {

and yes: you can only assign an underlying to a derivative not to "normal" contracts. This is being resolved with "dependent":

.dependent( 'contract.derivative', function ( val, data, callback ) {
    var that = contractEditor;
    if ( that.val('contract.instrument') === 'Z' ||
         that.val('contract.derivative') >= 'A'       ) { //single derivatives need to be assigned
        that.show( [ 'underlying[].id' ] );
    } else { //non-derivatives cannot be assigned
        that.hide( [ 'underlying[].id' ] );
    }
})