Join with table rename

marianidiego

Link to test case: https://editor.datatables.net/examples/advanced/joinArray
Description:
It would be interesting to be able to change the name of the linked table. Like: "Mjoin::inst( 'permission as permission' )" .
This might be useful in some circumstances....

rf1234

... and it is already possible!

Just create a view in you database and use it.

Example from my own coding:
- I created a view on "contract" which I call "underlying". Then I created a link table that links derivative contracts with their "underlying" contract. Meaning a link table between "contract" and itself.

Now I can create an Mjoin that allows me to select underlyings for derivative contracts. Both are in the contract table of course. ("Naked" derivatives are illegal for my clients. They must have and assign an "underlying" contract they want to hedge.)

This is the Mjoin:

//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 )
    )
)

marianidiego

Not exactly.

I already use views in sql...

But exactly, these each one has a different name. I have different views, to extract data for different circumstances. If I can rename the table to datatables, I can use the same code each time.

If I declare a

Editor::inst( $db, 'product_special_clients as products', 'products_id' )

this will work without any problems.

But if I use a

$a->join(
       Mjoin::inst("products_description_special_clients as products_description")
    ->link( 'products_description.products_id', 'products.products_id' )

This will not work.

rf1234

ok, then define those different table names as (global) variables and get them into the code that way. Shouldn't be any problem.

if ( <thatCondition> ) {
    $mjoinTable = "products_description_special_clients";
} else {
    $mjoinTable = "products_description";
}

$a->join(
       Mjoin::inst('  .  $mjoinTable  . ')
    ->link(' . $mjoinTable . '.products_id', 'products.products_id' )

If you have trouble having the right table name available in your code use a $_SESSION variable for example that you can set elsewhere depending on your context. That way your code becomes very flexible.

Here is a WHERE clause that is different for each context depending on how certain session variable are set. For certain contexts the WHERE clause isn't applicable at all for example, see first "if" condition. Hence I can use the same Editor instance for client users with restricted rights and for internal admin users with unlimited rights. I also use different table names in the SQL depending on the situation.

->where( function ( $q ) {
    if ( isset($_SESSION['govCredUserId']) ) { //not the admin page
        $q  ->where( function ( $r ) { 
            if ( isset($_SESSION['subRegAnyPage']) ) {
                $userCompleteView = 'sub_govdept_has_user_complete';
            } else {
                $userCompleteView = 'ctr_govdept_has_user_complete';
            }    
            //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 ' . $userCompleteView . ' c     ON b.id = c.ctr_govdept_id  
              INNER JOIN ctr_installation d              ON a.ctr_installation_id = d.id
                   WHERE c.user_id = :id
                     AND c.role     IN ("Administrator", "Principal" )
                     AND d.is_subsidy_register = :isSubReg
                   )', 'IN', false);
            $r  ->bind( ':id', $_SESSION['govCredUserId'] );
            $r  ->bind( ':isSubReg', isset($_SESSION['subRegAnyPage']) ? 1 : 0 );
        } );
    }
} )

allan

Mjoin::inst("products_description_special_clients as products_description")

I need to look at what will be involved in supporting that directly. I can see that it might be useful. Thanks for the suggestion.

Regards,
Allan

marianidiego

allan

@marianidiego - I've just been looking at this, and I'm wondering if you could tell me a little more about what sort of use cases you had in mind here?

There are a few goals that I can think of:

• A self referencing join - when permissions refers to itself for example. That is actually possible already using the Mjoin->aliasParentTable() option which can be used to give the parent table an alias.
• Changing the name of the field in JSON / HTTP. That can be done with Mjoin->name().
• Simplifying the SQL / link statements. No option for this one at the moment.

Is there anything I've missed?

Allan