Joins with multiple related tables

TomBajzek · June 2018

Can I have two separate tables joined to my main table? I have a trouble ticket app where a ticket may have zero or more notes related to it, and one vehicle related to it. I've built 3 apps that have the 1-to-many notes relationship, and they work just fine. When I tried to add the 1-to-1 vehicle relationship in the new app, I found that the editor panel open up empty (no contents or labels) for either a create or an edit, for either the tickets or the notes tables. (The relationship with the vehicles table is read-only - just to allow a vehicle to be selected.) I have verified that the vehicle data gets to the DataTable, as it is displayed correctly there. The problem is just with the tickets editor and the notes editor.

If it's necessary to look at code, I'll make arrangements for you to see it privately.

Thanks,
Tom

rf1234 · June 2018

"The relationship with the vehicles table is read-only - just to allow a vehicle to be selected"
What you need is to be able to assign one vehicle to a ticket. That is done with an options instance and you would save the vehicle id as a foreign key in your ticket table. Alternatively you could use a link table if you don't want to have the foreign key in your ticket table. A left join would only be needed to retrieve a vehicle name or something else to display in the select field at the front end.

https://editor.datatables.net/manual/php/joins#Options

Here is an example with two options instances:
https://editor.datatables.net/examples/advanced/joinLinkTable.html

One options instance is used to populate the foreign key field "site" in the parent table "users". The other instance is used to populate the link table "user_dept" between "users" and "dept".

I attached a graph of the data model to make it easier to understand the example.

TomBajzek · June 2018

I've built several apps that use selections from a related table, and they work just fine. I've also built apps that edit in a related table, along the lines of this blog post: https://datatables.net/blog/2016-03-25, and those have worked as expected.

I misspoke slightly in my question, in that the ticket needs not only to allow a vehicle to be selected, but also to render data from the vehicles table that allows users to identify the vehicle. Those requirements are in addition to supporting editing in the related notes table (wherein a ticket may have zero or more related notes).

My question about tables joined to a given table arises from my frustration at being unable to see how I've done this incorrectly. My descriptive field identifying the vehicle from the joined table displays correctly, so I believe that has been done correctly. The empty editor panel that results is the thing that is troubling me that I haven't been able to solve. I had a similar type of problem a couple of months ago trying to implement my client's request to have two editors open at once, when I inquired and found out that this is not currently supported. I asked this question so I would not repeat that experience again, in case this is not possible.

Now I'll have to find a way to extract a simple example from this.

Thanks for your patience

rf1234 · June 2018

"I misspoke slightly in my question, in that the ticket needs not only to allow a vehicle to be selected, but also to render data from the vehicles table that allows users to identify the vehicle. Those requirements are in addition to supporting editing in the related notes table (wherein a ticket may have zero or more related notes)."

There might be an issue with your JavaScript?! It is no problem to join multiple tables to provide data to render the options instance in Javascript. Here is an example from my own coding. This options instance renders the data from two tables and it INNER JOINs them (via the WHERE clause because Editor doesn't allow explicit INNER JOINs). In addtion I LEFT JOIN these tables outside the options instance in order to have those fields available for rendering inside the options instance. Sounds weird but it works fine:

PHP:

if ( ! isset($_POST['user']) || ! is_numeric($_POST['user']) ) {
    echo json_encode( [ "data" => [] ] );
} else {
    if ($lang === 'de') {     
        $msg[0] = 'Feld darf nicht leer sein.';
    } else {
        $msg[0] = 'Field may not be empty.';
    }
    Editor::inst( $db, 'govdept_has_user' )
    ->field(
        Field::inst( 'govdept_has_user.user_id' )->set(Field::SET_CREATE),
        Field::inst( 'govdept_has_user.govdept_id' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) )
            ->options( Options::inst()
                ->table('govdept, gov')
                ->value('govdept.id')
                ->label( array('gov.name', 'govdept.name') )
                ->render( function ( $row ) {               
                    return $row['gov.name'].' / '.$row['govdept.name']; 
                } )
                ->order( 'gov.name asc' )
                //where clause MUST be a closure function in Options!!!
                ->where( function($q) {
                        $q ->where('govdept.gov_id', 'gov.id', '=', false); //join
                        if (isset($_SESSION['masterDataId']) ) {
                            $q ->where( 'govdept.id', 
                                        '( SELECT DISTINCT govdept_id FROM govdept_has_user     
                                           WHERE user_id = :id AND role IN ("Administrator", "Principal" )
                                           ORDER BY govdept_id ASC  
                                           )', 'IN', false);
                            $q->bind( ':id', $_SESSION['masterDataId'] );
                        }
                    } )
            ),
        Field::inst( 'govdept_has_user.role' )->validator( 'Validate::notEmpty', array('message' => $msg[0]) ),
        Field::inst( 'govdept.name' ),
        Field::inst( 'gov.name' ),
        Field::inst( 'gov.regional_8' ),
        Field::inst( 'gov.regional_12' )
    )
    ->leftJoin( 'user', 'user.id', '=', 'govdept_has_user.user_id' )
    ->leftJoin( 'govdept', 'govdept.id', '=', 'govdept_has_user.govdept_id' )
    ->leftJoin( 'gov', 'gov.id', '=', 'govdept.gov_id' )
    ->where( function ( $q ) {
        $q  ->where( 'govdept_has_user.user_id', $_POST['user'] );
        $q  ->where( 'user.type', 'G' );
        if (isset($_SESSION['masterDataId']) ) {
            $q ->where( 'govdept_has_user.govdept_id', 
                        '( SELECT DISTINCT govdept_id FROM govdept_has_user     
                           WHERE user_id = :id AND role IN ("Administrator", "Principal" ) 
                           ORDER BY govdept_id ASC  
                           )', 'IN', false);
            $q->bind( ':id', $_SESSION['masterDataId'] );
        }
    } )
    ->on('preCreate', function ( $editor, $values ) {
        // no acces to $id inside the function - need to access global array again
        $userUser = filter_var($_POST['user']);
        $editor            
            ->field('govdept_has_user.user_id')
            ->setValue($userUser);
    })
    ->on( 'postCreate', function ( $editor, $id, $values, $row ) {            
        logChange( $editor->db(), 'create', $id, $row, 'govdept_has_user' );
        //set these session variable to be able to send the pwLink
        $_SESSION['sendPwLinkId'] = filter_var($_POST['user']);
    } )
    ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), 'edit', $id, $row, 'govdept_has_user' );
    } )
    ->on( 'postRemove', function ( $editor, $id, $values ) {
        logChange( $editor->db(), 'delete', $id, $values, 'govdept_has_user' );
    } )
    ->process($_POST)
    ->json();
}

JavaScript: The department name is rendered at the front end as a combination of two fields of two different tables: $row['gov.name'].' / '.$row['govdept.name'];
BUT: the field name in JavaScript is the foreign key that you want to populate by selecting a department.

var userGovDeptEditor = new $.fn.dataTable.Editor( {
    ajax: {
        url: 'actions.php?action=tblUserGovDept',
        data: function ( d ) {
            var selected = userTable.row( {selected: true} ); 
            if (selected.any()) {
                d.user = selected.data().user.id;
            }
        }
    },
    table: "#tblUserGovDept",
    fields: [ {
            label: lang === 'de' ? 'Name der Abteilung:' : 'Department Name:',
            name:  "govdept_has_user.govdept_id",
            type: "selectize", 
            opts: {
                create: false,
                maxItems: 1,
                maxOptions: 15,
                openOnFocus: false,
                allowEmptyOption: false,
                placeholder: lang === 'de' ? 'Bitte Abteilung auswählen' :
                                             'Please select a Department'
            }
        }, {
            label: lang === 'de' ? 'Rolle:' : 'Role:',
            name:  "govdept_has_user.role",
            type:  "selectize",
            options: userRoleOptions,
            opts: {
                create: false,
                maxItems: 1,
                allowEmptyOption: false,
                placeholder: lang === 'de' ? 'Rolle für diese Abteilung auswählen' :
                                             'Select Role for this Department'
            }
        }
    ]        
} );

TomBajzek · June 2018

I tried your approach, rendering the vehicle identification in PHP, as this really simplifies the Javascript, where I've been unable to resolve this problem. (My original code had rendered this correctly in Javascript, but it made ugly looking code.) I'd thought I had this worked out, but now I get a "Warning - Invalid JSON" diagnostic. The JSON looked OK to me, so I ran it through JSONlint, and it was pronounced as valid. I'm not sure what to do with this.

I also noticed that I'm getting no data for my fields from the vehicles table, and I don't see what that is happening. I ran the debugger for this, and the code for the debug file is UKUYEF. My code for the tickets table editor is here:

```
Editor::inst( $db, 'tickets', 'id' )
// ->debug(true)
->fields(
Field::inst( 'tickets.id' )->set(false),
Field::inst( 'tickets.created' )
->set(Field::SET_CREATE)
->setValue(date("Y-m-d H:i:s"))
->getFormatter( function( $val, $data, $opts) {
return date("Y-m-d H:i:s", strtotime( $val) );
} ),
Field::inst( 'tickets.updated' )
->setValue(date("Y-m-d H:i:s"))
->getFormatter( function( $val, $data, $opts) {
return date("Y-m-d H:i:s", strtotime( $val) );
} )
->setFormatter( function( $val, $data, $opts) {
return date("Y-m-d H:i:s", strtotime($val));
} ),
Field::inst( 'vehicles.bareTag' ),
Field::inst( 'vehicles.color' ),
Field::inst( 'vehicles.modelYear' ),
Field::inst( 'vehicles.maker' ),
Field::inst( 'vehicles.model' ),
Field::inst( 'vehicles.type' ),
Field::inst( 'tickets.vehicle_id' )
->options( Options::inst()
->table( 'tickets,vehicles' )
->value( 'vehicles.id' )
->label( array('bareTag','color','modelYear','maker','model','type' ) )
->render( function($row) {
return $row['vehicles.bareTag'] . ' - ' .
$row['vehicles.color'] . ' ' .
$row['vehicles.modelYear'] . ' ' .
$row['vehicles.maker'] . ' ' .
$row['vehicles.model'] . ' ' .
$row['vehicles.type'];
} )
->order('vehicles.bareTag asc')
->where( function($q) {
$q ->where( 'vehicles.id','tickets.vehicle_id','=',false );
} )
),
Field::inst( 'tickets.status' )
->validator( Validate::notEmpty() ),
Field::inst( 'tickets.originator' )
->validator( Validate::notEmpty() ),
Field::inst( 'tickets.responder' )
->options(Options::inst()
->table('responders')
->value('responder')
->label('responder')
->where( function($q) {
$q->where('active','yes','=');
} )
->render( function ($row) {
return $row['responder'];
} )
),
Field::inst( 'tickets.category' )
->options(Options::inst()
->table('categories')
->value('category')
->label('category')
->render( function ($row) {
return $row['category'];
} )
)
// Field::inst( 'vehicles.id' )
// ->options( Options::inst()
// ->table('vehicles')
// ->value('id')
// ->label('bareTag' . ' ' . 'color' . ' ' . 'modelYear' . ' ' . 'maker' . ' ' . 'model' . ' ' 'type'),
// ->label('bareTag')
// )
// ),
)
->where( function($q) {
global $vehicleId;
error_log('t.a.p2: vehicleId= ' . $vehicleId);
if ($vehicleId != '') {
$q ->where('tickets.vehicle_id',$vehicleId,'=') ; // Fetch this vehicle's tickets
}
} )
->on('postCreate', function($editor,$id,$values,$row) { // Notify techs of new ticket
error_log('f.t.p: Created: id= ' . $id . ', catR= ' . $row['category'] . ', descR= ' . $row['description'] . ', catV= ' . $values['category'] . ', descV= ' . $values['description']);
notify($id,$values['category'],$values['description'],$values['email']);
} )
->where( function ($q) {
global $vehRole,$maxAge,$theUser;
if ($vehRole != 'tech' and $vehRole != 'admin') {
$q ->where('tickets.originator',$theUser,'='); // Restrict non-(tech,admin) to own tickets
}
if ($maxAge != 'all') { // Exclude old closed tickets
$q ->where('tickets.status','closed','!=')
->or_where(function( $r) use ($maxAge) {
$r->where('tickets.status','closed','=');
$r->where('tickets.updated','date_sub(now(),interval ' . $maxAge . ')','>',false);
} );
}
} )
->leftJoin( 'vehicles','vehicles.id','=','tickets.vehicle_id' )
->process( $_POST )
->json();
``

I think I can straighten out my Javascript once I get this PHP to work correctly.

Thanks,
Tom

rf1234 · June 2018

Hard to read this without markdown, Tom.

There are a couple of problems here I guess:

Field::inst( 'tickets.vehicle_id' )
        ->options( Options::inst()
            ->table( 'tickets,vehicles' )
            ->value( 'vehicles.id' )
            ->label( array('bareTag','color','modelYear','maker','model','type' ) )
            ->render( function($row) {
                return $row['vehicles.bareTag'] . ' - ' .
                $row['vehicles.color'] . ' ' .
                $row['vehicles.modelYear'] . ' ' .
                $row['vehicles.maker'] . ' ' .
                $row['vehicles.model'] . ' ' .
                $row['vehicles.type'];
            } )
        ->order('vehicles.bareTag asc')
        ->where( function($q) {
            $q ->where( 'vehicles.id','tickets.vehicle_id','=',false );
        } )

You don't need the INNER JOIN because you are just retrieving the label from one single table. The same table that you get the foreign key from: "vehicles". In my case I got the label from two tables different from my primary table.

Secondly your label array field names aren't corresponding with what you select in your LEFTJOIN.

This could work - I didn't check your other options instances though:
I am pretty sure you don't need the tablename as a prefix on the fields selected with the options instance if you only have one table. This should work:

......
->options( Options::inst()
            ->table( 'vehicles' )
            ->value( 'id' )

If not prefix it whith "vehicles".

So this is my suggestion for this options instance:

.....
Field::inst( 'tickets.vehicle_id' )
        ->options( Options::inst()
            ->table( 'vehicles' )
            ->value( 'id' )
            ->label( array('vehicles.bareTag','vehicles.color','vehicles.modelYear','vehicles.maker','vehicles.model','vehicles.type' ) )
            ->render( function($row) {
                return $row['vehicles.bareTag'] . ' - ' .
                $row['vehicles.color'] . ' ' .
                $row['vehicles.modelYear'] . ' ' .
                $row['vehicles.maker'] . ' ' .
                $row['vehicles.model'] . ' ' .
                $row['vehicles.type'];
            } )
            ->order('vehicles.bareTag asc')

allan · June 2018

Hi Tom,

The reply from the server for table #tickets has an error at the start of it which is making it invalid JSON:

< br / > < b > Notice < /b>:  Undefined index: vehicles.bareTag in <b>/var / www / vhosts / bajzek.com / asccintranet.bajzek.com / FleetTracker / php / table.tickets.php < /b> on line <b>123</b > < br / > < br / > < b > Notice < /b>:  
...

Allan

TomBajzek · June 2018

Hi rf1234,

Your answer about the join and the render in PHP helped me understand how to do this correctly. (It turns out that I needed to use ->value( 'vehicles.id' ) to make it work.) I think that cleared up what I was misunderstanding about those things, and I thank you for that. However, I still have the original unresolved issue:

My editor panel is still empty when I click New or Edit, which was the original reason I asked a question here. I was hoping that getting the rendering of the options fixed might make this go away, but it did not. So, I still need to find a way to deal with that one.

Thanks again for your help,
Tom

rf1234 · June 2018

If the json error is fixed now the next step would be to look at your JavaScript. If you can post that I can take a look.

TomBajzek · June 2018

rf1234,

Thanks for the offer, but Allan responded to a tech support purchase I made, and fixed that issue. It turns out that the names were inconsistent in the editor fields, which I had not noticed on my own. That problem is now behind me, and I'm dealing with another issue. I appreciate your help, as that part of the program is now working correctly.

Thanks again,
Tom

Joins with multiple related tables

Joins with multiple related tables

Answers

Howdy, Stranger!

Categories

DataTables

Joins with multiple related tables

Joins with multiple related tables

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables