Raw subQuery and editor with sum

72pixels

I am having the same issue as this post https://datatables.net/forums/discussion/36355/subquery-raw-sql-query-in-editor. However, I am trying to get the sum of a column in the linked table rather than just a count.

I am using the raw query to generate the datatables table and I only want to edit the status and start_date fields in the primary table so I have excluded all of the other fields in the form. When I attempt to edit, I get a warning message

"DataTables warning: table id=projects - Requested unknown parameter 'client_address' for row 3, column 2. For more information about this error, please see http://datatables.net/tn/4"

The edit produces blank row but if I refresh, the updated data is there. I have tried the Mjoin, Mjoin & Left Join, and countless other scenarios and none seem to be working. What am I missing?

projects.php

if ( ! isset( $_POST['action'] ) ) {
   $rawquery = "SELECT projects._id, invoiceID, client_fname, client_lname, client_address, status, start_date, invoiceTotal, 
            (Select sum(amount) from expenses where projects._id=expenses.projectID) as expenses,
            (Select sum(amount) from labor where projects._id=labor.projectID) as labor,
            (Select (invoiceTotal - 
                ifnull(
                (Select sum(amount) from expenses where projects._id=expenses.projectID) +  (Select sum(amount) from labor where projects._id=labor.projectID)
                ,0) 
            )
            ) profit, 
            FROM projects 
            inner join invoices on projects.invoiceID = invoices._id
            group by projects._id";
   $data = $db->sql( $rawquery )->fetchAll();
   echo json_encode( array(
      'data' => $data
   ) );
 
}else{
    
    
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'projects', '_id' )
    ->fields(
        Field::inst( '_id' )
            ->set( false ),
        Field::inst( 'invoiceID' )
            ->set( false ),
        Field::inst( 'start_date' )
            ->validator( Validate::dateFormat( 'Y-m-d' ) )
            ->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
            ->setFormatter( Format::dateFormatToSql('Y-m-d' ) ),
        Field::inst( 'status' )
    )
    ->process( $_POST )
    ->json();
}

javascript:

peditor = new $.fn.dataTable.Editor( {
        ajax: "data/projects.php",
        table: "#projects",
        idSrc:  "_id",
        fields: [ {
                label: "Start Date:",
                name: "start_date",
                type: "datetime"
            }, {
                label: "Status:",
                name: "status",
                type: "select", 
                options: [ "pending", "in-progress", "completed", "cancelled" ], 
                def: "pending"
            }
        ]
    } );


var table = $('#projects').DataTable( {
        dom: "Bfrtip",
        ajax: "data/projects.php",
        columns: [
            { data: "invoiceID" },
            { data: null, render: function ( data, type, row ) {
                // Combine the first and last names into a single table field and add a link
                return '<a href="viewProject.php?id='+data._id+'">'+data.client_fname+' '+data.client_lname+'</a>'    ;
            } },
            { data: "client_address"},
            { data: "status" },
            { data: "start_date" },
            { data: "invoiceTotal",render: $.fn.dataTable.render.number( ',', '.', 2, '$' ) },
            { data: "expenses" ,render: $.fn.dataTable.render.number( ',', '.', 2, '$' ) },
            { data: "labor",render: $.fn.dataTable.render.number( ',', '.', 2, '$' )  },
            { data: "profit",render: $.fn.dataTable.render.number( ',', '.', 2, '$' )  }
        ],
        select: true,
        buttons: [
            { extend: "edit",   editor: peditor }
        ]
    } );

HTML:

<table id="projects" class="display m_datatable" style="width:100%">
    <thead>
        <tr>
            <th>Invoice</th>
            <th>Name</th>
            <th>Address</th>
            <th>Status</th>
            <th>Start Date</th>
            <th>Invoice Total</th>
            <th>Expenses</th>
            <th>Labor</th>
            <th>Profit</th>
              </tr>
    </thead>
    <tfoot>
        <tr>
            <th>Invoice</th>
            <th>Name</th>
            <th>Address</th>
            <th>Status</th>
            <th>Start Date</th>
            <th>Invoice Total</th>
            <th>Expenses</th>
            <th>Labor</th>
            <th>Profit</th>
              </tr>
    </tfoot>
</table>

allan

Thanks for the code. So the problem here is that when you submit the edit request, the Editor PHP libraries will then read back the data from the configuration given to it (in this case just those three fields) - the result is that the JSON returned to the client-side will not have the full data set required (as seen by the error message you are getting).

What you would need to do for this case is to listen for the postGet method in PHP and then query the database with your custom SQL to get the full data and have Editor return that - e.g.

    ...
    ->on( 'postGet', function ( $editor, &$data, $id ) {
      // query database to get data for row $id
      // ...
      $data[ 'field1' ] = $row['field1']; // etc (or use `array_merge`).
    } );
    ->process( $_POST )
    ->json();

postGet will execute once for every row read, so continue your current check to see if $_POST['action'] is submitted or not, otherwise it will hit the database with a lot of queries!

I'd suggest putting your custom query into a function where you can optionally add where filter for the $id from the postGet function.

Allan