Sub joins on a datatable

gforster

I'm going to do my best to be clear...

In my Main Table (VOLUMES), I have an Mjoin for a VOL_CUSTSVCS table (this is a link table to VOLUMES and CUSTSVCS showing what CUSTSVCS are on which VOLUME. Cool, that works perfectly.

Now, there is another link table CUSTSVCS_APPS which shows which APPS are related to which CUSTSVC.

So, every APP is related to a CUSTSVC. Every CUSTSVC is on a VOLUME.

On that VOLUMES table, I can easily show (in both Editor and Datatables views) what CUSTSVC is on the VOLUME. What kind of Join do I need to do to also show a column on that table of all of the APPS that would be on said volume?

rf1234

Not sure whether you can do this with an Mjoin because you would need more than two links to link from volumes to customer services and then on to apps.

Mjoin simply returns an array. You can also write your own SQL statement to fetch the data and return it to the front end.

This could be the SQL:

SELECT apps.name 
   FROM apps
     LEFT JOIN custsvcs_apps     ON  custsvcs_apps.apps_id = apps.id
     LEFT JOIN custsvcs         ON  custsvcs_apps.custsvcs_id = custsvcs.id
     LEFT JOIN vol_custsvcs    ON  vol_custsvcs.custsvcs_id = custsvcs.id
     LEFT JOIN vol             ON  vol_custsvcs.vol_id = vol.id
WHERE vol.id = :yourVolId
     AND apps.name IS NOT NULL

you select the array as an alias of vol.id because you need that to select the apps.

Field::inst( 'vol.id AS appsArray' )->set( false )
                ->getFormatter( function($val, $data, $opts) {
                    return getFormatterAppsArray($val);                       
                }),

In that function you select the array with your own database handler and return it. That's it!