Display Only Unused/Available Options in Editor Select

gforster

If I have a Field in the Editor wherein I am using something like

 Field::inst( 'VOLUMES.server_id' )
             ->options( Options::inst()
                 ->table( 'SERVERS' )
                 ->value( 'id' )
                 ->label( 'name' )
             )
             ->validator( 'Validate::dbValues' )
     ->leftJoin( 'SERVERS', 'VOLUMES.server_id', '=', 'SERVERS.id' )

And in the javascript, using this in the fields:

              label: "Hostname",
              name: "VOLUMES.server_id",
              type: "select",
              placeholder: "Select the Hostname"

and the values of VOLUMES.server_id (and SERVERS.name for that matter) must be unique per mysql restrictions, is it possible to only display unused options? That way, when selecting from the dropdown, you are only presented with what is available.

allan

That's a cracking question - I don't think I've come across this one before.

In SQL terms we can use WHERE ... NOT IN ... to do this with a sub-select to get the items that we want to filter out.

To do that you can use the where method for the Options instance like this:

->where( function ($q) {
  $q->( 'id', '(SELECT server_id FROM volumes)', 'NOT IN', false );
} )

That will select all server ids currently in the volumes table and then reverse filter them.

More information about how to do some of these more interesting things with filters are available here.

Regards,
Allan