[Server-side code] Using columns from more than one table in a Editor Field's select list

TooManyTables

I have a working Datable/Editor instance, designed to allow the assigning of people (Custodians) to rooms. The form contains a Field that currently shows only a (not very human-readable) Door Code. I would like to give it a more useful label consisting of information from two related tables - Rooms and Buildings.

I had the idea of passing in both tables as an array to Options->table(), and using Options->label() and Options->render() as outlined below to allow me to access fields from both tables:

Field::inst( 'Custodians.doorCode' )
 ->options( Options::inst()
 ->table( ['Room', 'Building'] )
  ->value( 'Room.doorCode' )
  ->label( ['Room.doorCode', 'Building.buildingShortName', 'Room.roomNumber'])
   //Special render here to return doorCode and a human-readable room in the label E.g. 'M-456'
  ->render( function ( $row ) {
     return $row['Room.doorCode'].' ('.$row['Building.buildingShortName'].'-' . $row['Room.roomNumber'] . ')';
  } )
                     
  )

This works, but too well - it gives me all possible combinations of rooms and buildings in the dropdown box, rather than just the ones that actually exist.

So I get (mock data):

A120123 (A-123)
A120123 (B-123)
A120123 (C-123)
B126456 (A-456)
B126456 (B-456)
B126456 (C-456)
C129789 (A-789)
C129789 (B-789)
C129789 (C-789)

Instead of:

A120123 (A-123)
B126456 (B-456)
C129789 (C-789)

I've tried using a ->leftjoin() (which gave an error stating that that isn't a method for Options) and a ->where:

  ->where( function ($q) {
      $q->where( 'Room.buildingID', 'Building.buildingID', '=', true );
  })

But that ends up returning nothing. Is there some way of limiting this?

I'm afraid I'm not able to give a link to the page in question - it's on an internal network, not accessible without proper login credentials and being physically on-site.

Server-side PHP Code:

Editor::inst($db, 'Custodians', array('Custodians.userID', 'Custodians.doorCode') )
        ->debug(true)
        ->field(
            Field::inst( 'Custodians.userID' )
                ->options( Options::inst()
                    ->table( 'Users' )
                    ->value( 'Users.userID' )
                    ->label('Users.userFullName')
                    ->where(function($q) {
                        $q->where( 'Users.userID', '(SELECT DISTINCT userID FROM IndependentUserRole)', 'IN', false);
                        }
                    )
                )
                ->validator( 'Validate::notEmpty' )
                ->validator( 'Validate::dbValues', array('field' => 'Users.userID')),
            Field::inst( 'Custodians.doorCode' )
                ->options( Options::inst()
                    ->table( ['Room', 'Building'] )
                    ->value( 'Room.doorCode' )
                    ->label( ['Room.doorCode', 'Building.buildingShortName', 'Room.roomNumber'])
                    ->where( function ($q) {
                        $q->where( 'Room.buildingID', 'Building.buildingID', '=', true );
                    })
                    //Special render here to return doorCode and a human-readable room in the label E.g. 'M-456'
                    ->render( function ( $row ) {
                        return $row['Room.doorCode'].' ('.$row['Building.buildingShortName'].'-' . $row['Room.roomNumber'] . ')';
                      } )                    
                )
                ->validator( 'Validate::notEmpty' )
                ->validator( 'Validate::dbValues', array('field' => 'Room.doorCode')),
    ->leftJoin( 'Users', 'Custodians.userID', '=', 'Users.userID' )
    ->leftJoin( 'IndependentUserRole', 'Custodians.userID', '=', 'IndependentUserRole.userID' )
    ->leftJoin( 'Room', 'Custodians.doorCode', '=', 'Room.doorCode' )                        
    ->leftJoin( 'Building', 'Room.buildingID', '=', 'Building.buildingID' )                        
    ->process($_POST)
    ->json();

jQuery, contained in document.ready:

var editor2 = new $.fn.dataTable.Editor( {
            ajax: {
                url: equipPath + '/Controllers/UpdateCustodians.php'
            },
            table: '#custodians-table',
            fields: [ {
                    label: 'User:',
                    name: 'Custodians.userID',
                    type: 'select',
                    placeholder: ''
                }, {
                    label: 'Door Code:',
                    name: 'Custodians.doorCode',
                    type: 'select',
                    placeholder: ''
                }
            ]
        } );

        var table2 = $('#custodians-table').DataTable( {
            ajax: {
                url: equipPath + '/Controllers/UpdateCustodians.php',
                type: 'POST'
            },
            columns: [
                { data: 'Users.userFullName'},
                { data: 'Custodians.doorCode' },
                { data: 'Building.campusID' },
                { data: 'Building.buildingName' },
                { data: 'Room.roomNumber' }
            ],
            select: true,
            pageLength: 100,
            buttons: [
                { extend: 'create', editor: editor2 },
                { extend: 'edit',   editor: editor2 },
                { extend: 'remove', editor: editor2 },
            ]
        } );

allan

Hi,

I think this is one case where the Options class isn't going to cut it I'm afraid. If you need information from two tables (i.e. a join) the Options class won't work for you as it doesn't support that ability (yet - good idea though!).

Instead, what I think you'll need to do is use the ability to provide your own function to get the data from the database.

You could combine that with the database methods to execute the exact query you need - e.g.:

options( function () use ($db) {
  $res = $db->raw( 'SELECT ... FROM ... JOIN ... ON ...' )->fetchAll();

  return ... // array of arrays with 'label' and 'value' parameters
}

Regards,
Allan

TooManyTables

Interesting, I'll give that a go. Worst-case scenario, boss says not to worry about it if it's not trivial (it's a nice-to-have, after all) and I've learnt something new.

Thanks for providing such a great piece of software, and such prompt and tireless support!

laurentP13

Thanks Allan for the guidance,

I believe the correct syntax is
$res = $db->raw()->exec("SELECT ... FROM ... WHERE ..." )->fetchAll();
(note the exec).

You want your sql query to return two columns named value (primary key for your target table) and label (what the selector would display). Use aliases to have SQL prepare this for you.

Complete example:

Say I have these tables: student, city, class, enrollment.

I'm going to enroll students in some class. I want the selector to display the city name in brackets as it makes identification easier (e.g. Smith, Sarah (Melbourne)).

There we go:

Editor::inst( $db, 'enrollment', 'id' )
    ->fields(
        Field::inst( 'enrollment.id' )->set(false),
        Field::inst( 'enrollment.fk_student' )
            ->options(
                function () use ($db) {
                    $sql =
                        "SELECT " .
                        "student.id as value, " .
                        "concat(student.name, ' (', city.name, ')') as label " .
                        "FROM `student`, `city` " .
                        "WHERE student.fk_city = city.id";
                    $res = $db->raw()->exec($sql)->fetchAll();
                    return($res);
                }
            )
            ->validator( Validate::dbValues() ),
        Field::inst( 'enrollment.fk_class')
            ->options(
                ->table( 'class' )
                ->value( 'id' )
                ->label( 'name' )
            )
            ->validator( Validate::dbValues() ),
    )
    ->process( $_POST )
    ->json();

Laurent