Subquery / Raw SQL query in Editor

loerez

Hi everyone,

I'm working on the same problem stated in this thread:
https://datatables.net/forums/discussion/22633/include-sub-selects-in-table-view-mode-not-the-editor-view

I'd like to add a field to an editable customer table that shows the number of purchased items per customer (read-only data from a different table).
Allan's sample php code from this thread works fine (see below), but once you edit something, the number of items isn't displayed in the edited row and the following warning pops up:

DataTables warning: table id=customer - Requested unknown parameter 'numberOfItems' for row 605, column 3

I assume that's because the else-part of the code is being executed and the if-part is skipped. Obviously there is no field "numberOfItems" in table "customer". The number shouldn't be edited anyway.

Any ideas how to get rid of the warning and display the table properly after the update-button is clicked?

include( "lib/DataTables.php" );

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate;

if ( ! isset( $_POST['action'] ) ) {
   $rawquery = "SELECT c.*, 
                      ( SELECT count(*) FROM purchases p
                        WHERE p.fk_customerid = c.id) AS numberOfItems
                FROM customer c";
   $data = $db->sql( $rawquery )->fetchAll();
   echo json_encode( array(
      'data' => $data
   ) );

}else{

   //... editor stuff

    Editor::inst( $db, 'customer', 'id' )
        ->fields(
            Field::inst( 'id' ),
            Field::inst( 'lastname' ),
            Field::inst( 'firstname' ),
            Field::inst( 'country' ),
            Field::inst( 'address' ),
            ...
        )
        ->process( $_POST )
        ->json();
}   

(function($){

$(document).ready(function() {
    var editor = new $.fn.dataTable.Editor( {
          ...
        fields: [
               ...
            {
                "label": "numberOfItems",
                "name": "numberOfItems",
                "type": "readonly"
            },
               ...
        ]
    } );

    var table = $('#customer').DataTable( {
        ajax: 'php/table.customer.php',
        columns: [
               ...      
            {
                "data": "numberOfItems"
            },
               ...
        ]
    } );
}(jQuery));

allan

Can you just do an Mjoin and use the length of the array for each row?

I think the problem you are having is that the data fetch is running a different query from when the edit is complete and it then runs its own SELECT to get the data to return, which in this case would be a subset.

Allan

loerez

Thanks a lot, the Mjoin did the job!

For those who encounter a similar problem, here's my solution:

    Editor::inst( $db, 'customer', 'id' )
        ->fields(
            Field::inst( 'id' ),
            Field::inst( 'firstname' ),
            Field::inst( 'lastname' ),
            ...
        )   
        ->join(
            Mjoin::inst( 'purchases' )
                ->link( 'customer.id', 'purchases.fk_customer_id' )
                ->field(
                    Field::inst( 'purchase_id' )            
                )
        )
        ->process( $_POST )
        ->json();

    var table = $('#customer').DataTable( {
        ajax: 'php/table.customer.php',
        columns: [

            { "data": "id" },
            { "data": "firsttname" },
            { "data": "lastname" },
            },
            {
                "data": "purchases",
                "render": function ( data, type, full, meta ) {
                  return data.length;
                }               
            },
            ...
    } );

Gives you just the fields from table.customer + the number of linked purchases