Different AJAX queries for DataTable and Editor

Loren Maxwell

I have some complicated data that I'd like to show in addition to the data that's available to edit.

In this specific case, I'd like to show a table that acts as a scoreboard where the game data is editable, but I'd like to also include the region of each team, which requires a left join on two fields: the season and the team name (or the season and opponent_name). Eventually I'd like to include some calculated values (such as each team's current W-L-T record, ranking in the polls, etc.). In short, I'm looking to include data that the joins in Editor can't currently accommodate, so I'm using an SQL statement to retrieve the data.

Here's the PHP page for the DataTables ajax:

<?php

// DataTables PHP library
include "../../Datatables/Editor/php/DataTables.php";

use
 DataTables\Database,
 DataTables\Database\Query,
 DataTables\Database\Result;

// Query
$sql = "

    SELECT g.*, tra.region AS team_region, ora.region AS opponent_region,

       /* Add DataTables identifier */
      CONCAT('row_', g.id) AS DT_RowId,
       /* Add back in the fields used for joins */
       g.season_name AS season_name, g.team_name AS team_name, g.opponent_name AS opponent_name
    
    FROM
    
        sa_game g
    
    LEFT JOIN
        sa_region_affiliations tra
        on tra.season_name = g.season_name AND tra.team_name = g.team_name
    
    LEFT JOIN
        sa_region_affiliations ora
        on ora.season_name = g.season_name AND ora.team_name = g.opponent_name
    
    WHERE g.season_name=". $db->quote( $_GET['season'] ) ."

";
$records = $db->sql( $sql )->fetchAll();

$data['data'] = array_merge( $records );

echo json_encode( $data );

Notice in the SQL I'm returning the DataTables row identifier:

      /* Add Datables identifier */
      CONCAT('row_', g.id) AS DT_RowId,

And I've noticed that any fields that are used in a join are not returned, so I've added them back in this way:

      /* Add back in the fields used for joins */
      g.season_name AS season_name, g.team_name AS team_name, g.opponent_name AS opponent_name

Now the DataTable is good (or at least from the perspective that everything shows as I would hope), and I can edit an existing or create a new row, but Editor uses a different ajax call to submit the data.

Here's an abbreviated version of the PHP page for the submit:

<?php

// DataTables PHP library
include "../../Datatables/Editor/php/DataTables.php";

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

// Build our Editor instance and process the data
Editor::inst( $db, 'sa_game' )
    ->fields(
        Field::inst( 'season_name' ),
        Field::inst( 'team_name' ),
        Field::inst( 'opponent_name' ),
        Field::inst( 'scored' ),
        Field::inst( 'allowed' )
    )
    ->where( 'season_name', $_POST['season'] )

    ->process( $_POST )
    ->json();

Of course when I submit I don't have the team_region or opponent_region anymore, so I get the following error:

DataTables warning: table id=scoreboard - Requested unknown parameter 'team_region' for row XXX, column Y.
For more information about this error, please see http://datatables.net/tn/4

I've read this post:
https://datatables.net/forums/discussion/39437

There it states to use initEdit() and val() to set the additional values, so I've added the following into my editor, creatively named "scoreboard_editor":

.on( 'initEdit', function ( e, node, data, items, type ) {
    scoreboard_editor.set( {
        'team_region': data.team_region,
        'opponent_region': data.opponent_region
    } )
} )

If I do console.log(data.team_region) I see the correct region, but when I hit submit I get the same error as before.

So a few questions on this approach in general:
1) Is they way I'm retrieving the data the best way? Do I have to actually create the DataTables row identifier and add back in the fields used for the joins?
2) What is correct way to "forward" the additional data through initEdit() and val()?
3) Is there a way to requery the DataTable such that if the team or the opponent were to be changed then their proper regions would be retrieved?

allan

Editor 1.8 is going to have a new option in the PHP server-side libraries which is going to help resolve this sort of thing. There is a new Editor->readTable() method (commit). Using readTable() will tell Editor to read from a different table from what it is writing to - so what you would do is create a VIEW with your more complex read SQL and point readTable at it. Then whenever Editor does a read (including after an update or create action) it will read from that more complex view.

That feature is in the PHP libraries which are available on Github if you want to try it just now. That feature will work just fine with 1.7.4 on the client-side since it doesn't have any client-side dependencies.

Allan

Loren Maxwell

Thanks, Allan.

I've updated my Editor.php file from Github, but what do I pass into the readTable function? The SQL for the query I'd like to run?

Would this work:

<?php
 
// DataTables PHP library
include "../../Datatables/Editor/php/DataTables.php";
 
// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;


// Query for the view
$sql = "
 
    SELECT g.*, tra.region AS team_region, ora.region AS opponent_region,
 
    FROM
     
        sa_game g
     
    LEFT JOIN
        sa_region_affiliations tra
        on tra.season_name = g.season_name AND tra.team_name = g.team_name
     
    LEFT JOIN
        sa_region_affiliations ora
        on ora.season_name = g.season_name AND ora.team_name = g.opponent_name
     
    WHERE g.season_name=". $db->quote( $_GET['season'] ) ."
 
";


// Build our Editor instance and process the data
Editor::inst( $db, 'sa_game' )
    ->fields(
        Field::inst( 'season_name' ),
        Field::inst( 'team_name' ),
        Field::inst( 'opponent_name' ),
        Field::inst( 'scored' ),
        Field::inst( 'allowed' )
    )
    ->where( 'season_name', $_POST['season'] )
    // Get the view
    ->readTable($sql)
 
    ->process( $_POST )
    ->json();

allan

No - its just the table name that should be read from. So you need to create a VIEW with your more complex SQL. Then pass in the name of that view to the readTable method.

You should try to have the column names either match for the read and write VIEW and TABLE, or use ->set( false ) / ->get( false ) for the get / set table fields respectively. It does mean longer code if you do it that way, but it is more concise that the old way of doing this sort of thing (injecting a custom SQL select).

Allan

Loren Maxwell

Thanks, Allan.

I'm going to have to play around with it, so I might be back with additional questions.

So far I've not created any views in my database, so I'll be learning a couple of new things at once!

Loren Maxwell

Allan, I'm researching MySQL views, but one drawback I'm noticing is that I can't (or maybe just can't figure out how) to pass parameters to a view.

I know that's not a DataTables question proper, but it would impact how useful the new readTable() function would be.

And would put me at wondering if you include include a ->query() function almost as easily with Editor like I was thinking of above.

allan

It depends on what you want to do with the parameter. You can use a where condition with a bound parameter for example.

The problem with using a query() method is that it would then effectively bypass any get formatters and get values as well as be difficult to parse into known JSON for Editor (rather than just dumping the view out).

Allan