Search for Formatted / Derived Value on Server Side Search ?

sscotti

I'm just getting started with using Data tables using the composer packages. I've used the Laravel ones, but now also trying these. I'm using server side processing for display purposes, and I'll probably want to try using the Editor later on. Anyways, wondering if there is a way to search for a formatted or "derived" value, or if one can perform JOINS or other more complex DB operations in the server side script.

As an example, I have this in my PHP script:

        $table = 'referring_physician';
        // Table's primary key
        $primaryKey = 'id';

        $columns = array(
        array( 'db' => 'id', 'dt' => "id" ),
        array( 'db' => 'identifier',  'dt' => 'identifier' ),
        array(
            'db'        => 'lname',
            'dt'        => 'name',
            'formatter' => function( $d, $row ) {
                return $row['lname'] . ', ' . $row['fname'] ;
            }
        ),
        array( 'db' => 'mobile_phone',     'dt' => 'mobile_phone' ),
        array( 'db' => 'email',     'dt' => 'email' ),


        array(
            'db'        => 'specialty',
            'dt'        => 'specialty',
            'formatter' => function( $d, $row ) {
                $nicename = DatabaseFactory::SelectByQuery('SELECT specialty from specialties WHERE cmscode = ?', [$d])->fetch(PDO::FETCH_OBJ);
                $nicename = ($nicename !== false)?$nicename->specialty:"NOT SET";
                return $nicename;
            }
        ),

 . . . . . .

The specialty is actually just an id in the referring_physician table, and it is cross-referenced to the displayable text version corresponding to the id in that other table, "specialities". I'd like to make that searchable by the formatted name and not by the idea. That is just a simple example. I have more complex queries that I'd actually like to sort of use as a virtual table for display and editing purposes. Just wondering if that is possible and how to do that.

The "Editor" apparently does support LEFT JOINS, which might be good enough for most of my purposes when it comes to actually editing a table.

Thanks.

allan

Using the demo SSP class, no, I'm afraid it does not support joins. Editor's PHP libraries however do and you can use them without an Editor license as they are open source.

The other option is to use a VIEW and then just read from that.

Allan