Using a link table for self-referencing Mjoin

rf1234

I tried to build a query according to this example (PHP server script): https://editor.datatables.net/examples/advanced/joinArray.html
In the example "users" are being linked to "permission" via the link table "user_permission"

I wanted to join my table "contract" to itself via my link table "derivative_has_underlying" which has two columns:
"derivative_has_underlying.derivative_contract_id" and "derivative_has_underlying.underlying_contract_id". They both link with "contract.id" of the contract table.

Pretty simple I thought, but I couldn't get it running because aliasing didn't really work in the Mjoin method. I tried everything but always got SQL syntax errors which I couldn't really debug because the debug option didn't show me the SQL generated! All I had where the mysql error messages which aren't helpful if you can't see the SQL.

Finally I gave up and just created a view from the contract table and called it "underlying". Then it worked straight away. And the debug option works also - now, when I don't need it

@ Allan: why does the debug option not work in cases like this? Can I do something about it? Is it not possible to link a table with itself using Mjoin?

This is the code that worked using the view "underlying" instead of an aliased contract 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'])
                            .' / '.renderType($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', 'Z', '<' );
                    });
                } )
            ),
        Field::inst( 'serial' )->set( false ),
        Field::inst( 'instrument' )->set( false ),    
        Field::inst( 'type' )->set( false ),
        Field::inst( 'number' )->set( false )
    )
)

... and for the sake of completeness the DDL of the view:

CREATE VIEW `underlying` AS
    SELECT 
        `id`,
        `govdept_id`,
        `creditor_id`,
        `gov_manual_creditor_id`,
        `instrument`,
        `type`,
        `derivative`,
        `serial`,
        `number`
    FROM
        `contract`
    WHERE
        `instrument` < 'Z';

allan

@ Allan: why does the debug option not work in cases like this?

The debug SQL is output as part of the returned JSON. If there is an exception before that point which isn't caught, its not going to get as far as generating the JSON, which is sounds like is the case here.

A VIEW sounds like a good way of doing what you need here.

How were you trying to alias the table? I'm not sure that's something I've tried with an Mjoin to be honest.

Allan