Answers

• allan Posts: 63,498Questions: 1Answers: 10,470 Site admin

  June 4

  Without seeing the script it is impossible to say. Perhaps it is doing a lot of reading of data? Or perhaps the parameters for server-side processing aren't being seen and it thus processes everything? Are you POSTing the DataTables Ajax request?

  If you link to the page in question and show the code that would help address some of the questions I would need answers to.

  Allan
• hzhong Posts: 26Questions: 9Answers: 0

  June 5

  Thank you Allan. Below is the original code. It reports the same error when the URL of server-side script is put directly into the browser as a get request so I believe the problem is with the server-side script itself. The error reports from time to time. It sometimes responds with data successfully.

  <?php
  
  // DataTables PHP library
  include( "Datatables-1.10.18/Editor-PHP-1.7.4/php/DataTables.php" );
  
  $project_id = $_GET['project_id'];
  
  // Alias Editor classes so they are easy to use
  use
  DataTables\Editor,
  DataTables\Editor\Field,
  DataTables\Editor\Format,
  DataTables\Editor\Options,
  DataTables\Editor\Mjoin,
  DataTables\Editor\Join,
  DataTables\Editor\Upload,
  DataTables\Editor\Validate,
  DataTables\Editor\ValidateOptions;
  
  /*
   * Define SQL
   */
  
        $data = Editor::inst( $db, 'daily_inspection', 'di_id' )
            ->field(
                Field::inst( 'daily_inspection.di_id' ),
                Field::inst( 'daily_inspection.project_id' ),
                Field::inst( 'daily_inspection.hour' ),
                Field::inst( 'daily_inspection.inspect_date' ),
                  Field::inst( 'daily_inspection.fdate' ),
          Field::inst( 'daily_inspection.step_id' ),
          Field::inst( 'daily_inspection.app_id' ),
          Field::inst( 'application_type.application' ),
          Field::inst( 'application_step.app_step' ),
                Field::inst( 'daily_inspection.prep_surface_sa' )  ->getFormatter( function ( $val, $data ) {
                  return $val
                      ? $val
                      : '';
      } ),
                Field::inst( 'daily_inspection.prep_surface_st' )  ->getFormatter( function ( $val, $data ) {
                 return $val
                     ? $val
                     : '';
     } ),
  
          Field::inst( 'daily_inspection.tank_id' ),
          Field::inst( 'daily_inspection.notes' ),
          Field::inst( 'daily_inspection.ncrSubmit' ),
          Field::inst( 'daily_inspection.ncr_closed' ),
          Field::inst( 'vessel_tanks.vessel_tank_id' ),
          Field::inst( 'vessel_tanks.tank_number' ),
          Field::inst( 'vessel_tanks.tank_status' ),
          Field::inst( 'projects.projectname' )
  
            )
            ->join(
              Mjoin::inst( 'inspection_document' )
                  ->link( 'daily_inspection.di_id', 'inspection_document.di_id' )
                  ->fields(
                      Field::inst( 'inspection_document_id' ) ->set(false)
                  )
          )
          ->join(
              Mjoin::inst( 'ncr_document' )
                  ->link( 'daily_inspection.di_id', 'ncr_document.ncr_id' )
                  ->fields(
                      Field::inst( 'ncr_document_id' ) ->set(false)
                  )
          )
  
            ->leftJoin( 'projects', 'projects.projectid', '=', 'daily_inspection.project_id' )
        ->leftJoin( 'vessel_tanks', 'vessel_tanks.vessel_tank_id', '=', 'daily_inspection.tank_id' )
        ->leftJoin( 'application_step', 'application_step.step_id', '=', 'daily_inspection.step_id' )
        ->leftJoin( 'application_type', 'application_type.app_id', '=', 'daily_inspection.app_id' )
        ->where( 'daily_inspection.project_id', $project_id )
  
  
            ->process($_POST)
            ->json();
  
  
• allan Posts: 63,498Questions: 1Answers: 10,470 Site admin

  June 5

  And is your DataTables ajax object configured for POST (i.e. type: 'post')?

  Editor-PHP-1.7.4

  Is quite old. Editor 2.3.2 is the current release. There was a change in how the Mjoin was handled when server-side processing is enabled. It might be worth updating your server-side libraries and seeing if that has an impact.

  Allan
• hzhong Posts: 26Questions: 9Answers: 0

  June 6

  Thank you Allan. The ajax uses GET. The serverSide is disabled as the default setting.

  I managed to make the script work by adding where for Mjoin which limits the data read in Mjoin

            ->join(
              Mjoin::inst( 'inspection_document' )
                  ->link( 'daily_inspection.di_id', 'inspection_document.di_id' )
                  ->where( 'daily_inspection.project_id', $project_id )
                  ->fields(
                      Field::inst( 'inspection_document_id' ) ->set(false)
                  )
          )
          ->join(
              Mjoin::inst( 'ncr_document' )
                  ->link( 'daily_inspection.di_id', 'ncr_document.ncr_id' )
                  ->where( 'daily_inspection.project_id', $project_id )
                  ->fields(
                      Field::inst( 'ncr_document_id' ) ->set(false)
                  )
          )
  
            ->leftJoin( 'projects', 'projects.projectid', '=', 'daily_inspection.project_id' )
        ->leftJoin( 'vessel_tanks', 'vessel_tanks.vessel_tank_id', '=', 'daily_inspection.tank_id' )
        ->leftJoin( 'application_step', 'application_step.step_id', '=', 'daily_inspection.step_id' )
        ->leftJoin( 'application_type', 'application_type.app_id', '=', 'daily_inspection.app_id' )
        ->where( 'daily_inspection.project_id', $project_id )
  
  
            ->process($_POST)
            ->json();
  

  Wanted to make sure Editor 2 is backwards compatible with Editor 1.7.4, correct?
• allan Posts: 63,498Questions: 1Answers: 10,470 Site admin

  June 6

  The serverSide is disabled as the default setting.

  Okay - so you aren't using server-side processing then.

  I doubt that the upgrade will make much difference then, since the way you have it setup you are requesting the full amount of data on each request.

  How many rows of data do you have (both in the main table and the two Mjoined tables)? My guess is that you are close to hitting the memory bound, and sometimes there is just enough to trigger it.

  You could either switch to server-side processing mode, or increase the max RAM allowed in PHP.

  Allan
• hzhong Posts: 26Questions: 9Answers: 0

  June 11

  Thank you Allan. The main table has about 57k rows. One of the Mjoined table has 169k rows. Another one has less than 100 rows.

  Is it right to say that when requesting full amount of data in client-side processing, the Mjoin is performed before filtering data, while in server-side processing, the Mjoin is performed after filtering data?
• allan Posts: 63,498Questions: 1Answers: 10,470 Site admin

  June 11

  Kind of... This is the part of the code that is of particular interest.

  What happens is that the main table is queried first, and then if there are less than 1000 rows, Mjoin will do a WHERE IN condition to limit how many records it returns.

  When you are client-side processing, that condition won't be met. So you'll be loading the full 57K rows, plus then the 169K from the Mjoin - that will eat up the PHP memory no question. You might want to look at increasing the PHP limit if you are getting errors about it, but performance is still acceptable.

  With server-side processing enabled, only the first 10 (or however) records will be read from the main table, so the if condition in the Mjoin will be met, and this also speed things up there.

  With that many rows, if you don't yet have server-side processing enabled, you might want to consider enabling it. The downside to server-side processing is that you cannot search or order on the Mjoined data.

  Allan