Several joins with conditions.

svendber

Hi

I've got the following code.

$editor = Editor::inst( $db, 'crapper_customer_intranet', 'kundenr')
    ->fields(
        Field::inst('crapper_customer_intranet.kundenr'),
        Field::inst('crapper_customer_intranet.navn'),
        Field::inst('crapper_customer_intranet.cvrnr')

    )
    ->join(
        Mjoin::inst('crapper_vulnerability', array('toolid', 'plugin_id'))
            ->link('crapper_customer_intranet.kundenr', 'crapper_vulnerability.kundenr' )
            ->order('plugin_id')
            ->field(
                Field::inst('ip'),
                Field::inst('plugin_id'),
                Field::inst('start'),
                Field::inst('port'),
                Field::inst('protocol'),
                Field::inst('cvss_base_score'),
                Field::inst('service_name'),
                Field::inst('cve'),
                Field::inst('plugin_name'),
                Field::inst('description'),
                Field::inst('solution'),
                Field::inst('os'),
                Field::inst('id')

            )
    )

    ->join(
        Mjoin::inst('crapper_snippet_test',  array('scan_vendor', 'vendor_id'))
            ->link('crapper_customer_intranet.kundenr', 'crapper_vulnerability.kundenr' )
            ->link('crapper_snippet_test.vendor_id', 'crapper_vulnerability.plugin_id')
            ->order('vendor_id')

            ->fields(
                Field::inst('scan_vendor'),
                Field::inst('vendor_id'),
                Field::inst('vuln_title'),
                Field::inst('type'),
                Field::inst('area'),
                Field::inst('base_cvss'),
                Field::inst('vuln_descr'),
                Field::inst('snip_lang_title'),
                Field::inst('lang_description')
            )
    );

It works fine and all, but I need to change it up a bit. The second join for the table 'crapper_snippet_test' need to be altered so it only links when the following is also correct:
crapper_snippet_test.scan_vendor = crapper_vulnerability.toolid

how can I do that?
I've tried reading up on https://editor.datatables.net/manual/php/joins#Server-side and other pages on Forum, but I haven't been able to find the answer for it.

Have a great day!

allan

Hi,

I'm sorry, a compound join like that is not something that the Mjoin class currently supports. You can apply a where condition to the selection, but that would only work against given data, not against another column.

I'm trying to think if it would be possible with a VIEW, but I don't think so since the join for Mjoin is effectively done in PHP. The only thing I can think of would be to use a VIEW that would do the Mjoin for you (a Common Table Expression for example) - but that would make updating very difficult.

Sorry I don't have a solution for this one in the provided libraries.

Allan