Displaying custom record count, using server side processing

ldd954

I have a table and one column displays the status of a record
Booked or Available.

I am using server side processing and want to display how many records of the total are booked status and how many are available status.

I have successfully gotten server side filters to work well. I can narrow down my database and only see what I want, but of the full set returned of the filtered data, I would like to be able to display how many are booked and how many are still available.

Is this possible?

(Note, I have gotten this to work like this
"
var available = table.rows().eq( 0 ).filter( function (rowIdx) {
return table.cell( rowIdx, 3 ).data() == 'Available' ? true : false;
} );
document.getElementById("availableallotments").innerHTML = table.rows(available).count();
"
But it only counts the records that are dispalyed on screen, not what is on subsequent pages as I am limiting 50 records to be displayed at a time.

Thank you for your assistance.

ldd954

I should have Summarized.
With server size processing on and paging, I want to perform counts on the full row set returned. Counting how many rows meet each criteria and then display that amount to the user.

kthorngren

You will need to do the calculations server side and pass the data back in a different object, for example:

{
  "draw": 1,
  "recordsTotal": 57,
  "recordsFiltered": 57,
  "data": [ ... ],
  "booked_status": ???
}

Using xhr you can use the json parameter to access the additional data objects you return and display them how you wish.

Kevin

ldd954

Thank you Kevin,
I appreciate the feedback and direction.
Is there a help document that would push me in the right direction regarding how to put a different object into the json returned from the server?

allan

It depends on what you are using on the server-side. Is it a script you've written, a library we publish or is it from somewhere else?

Allan

ldd954

Thanks for the follow up Allan,
I am using PHP server side,

use
  DataTables\Editor,
  DataTables\Editor\Field,
  DataTables\Editor\Format,
  DataTables\Editor\Mjoin,
  DataTables\Editor\Options,
  DataTables\Editor\Upload,
  DataTables\Editor\Validate,
  DataTables\Editor\ValidateOptions

Here is a sample of what I am using to display that view:

public function allotment_view($post)
    {
        // Build our Editor instance and process the data coming from _POST
        // Use the Editor database class
        Editor::inst( $this->editorDb, 'allotments', 'allotments_id' )
          ->fields(
            Field::inst( 'allotments.owner' ),
            Field::inst( 'allotments.resortinfo_id' )
              ->options( Options::inst()
                ->table( 'resortinfo' )
                ->value( 'resortinfo_id' )
                ->label( 'resort_name' )
              )
              ->validator( Validate::dbValues() ),
              Field::inst( 'resortinfo.resort_name' ),
            Field::inst( 'allotments.alot_checkin' )
              ->validator( Validate::dateFormat( 'D, j M y' ) )
              ->getFormatter( Format::dateSqlToFormat( 'D, j M y' ) )
              ->setFormatter( Format::dateFormatToSql( 'D, j M y' ) ),
              Field::inst( 'allotments.alot_checkout' )
              ->validator( Validate::dateFormat( 'D, j M y' ) )
              ->getFormatter( Format::dateSqlToFormat( 'D, j M y' ) )
              ->setFormatter( Format::dateFormatToSql( 'D, j M y' ) ),
            Field::inst( 'allotments.nights' ), 
            Field::inst( 'allotments.unitinfo_id' )
              ->options( Options::inst()
                ->table( 'unitinfo' )
                ->value( 'unitinfo_id' )
                ->label( ['resort', 'unit_name'] )
              )
              ->validator( Validate::dbValues() ),
              Field::inst( 'unitinfo.unit_name' ),
              Field::inst( 'allotments.resnum' ),
              Field::inst( 'allotments.unitaccount' ),
              Field::inst( 'allotments.dailyrate' ),
              Field::inst( 'allotments.listings' ),
              Field::inst( 'allotments.status' )
                ->options( Options::inst()
                ->table( 'allotment_status' )
                ->value( 'status' )
                ->label( 'status' )
              )
              ->validator( Validate::dbValues() ),
              Field::inst( 'allotments.suppliercost' ),
               Field::inst( 'allotments.tax_included_sup_payment' ),
               Field::inst( 'allotments.paymentconfirmed' ),
               Field::inst( 'allotments.paidwith' ),
              Field::inst( 'allotments.notes' ),
              Field::inst( 'allotments.user_add' )->set( Field::SET_CREATE ),

            
          )
          ->where( function ( $q ) {
                if($_POST['resortname'] != 'null'){
                $q->where( 'resortinfo.resort_name', '%'.$_POST['resortname'].'%', 'LIKE');
                }
                if(!empty($_POST['after'])){
                $q->where( 'allotments.alot_checkin', date('Y-m-d',strtotime($_POST['after'])), '>=');
                }
                if(!empty($_POST['before'])){
                $q->and_where( 'allotments.alot_checkin', date('Y-m-d',strtotime($_POST['before'])), '<=');
                }
                if($_POST['status'] != 'null'){
                $q->and_where( 'allotments.status', $_POST['status'], '=');
                }
            } )
          ->leftJoin( 'unitinfo', 'unitinfo.unitinfo_id', '=', 'allotments.unitinfo_id' )
          ->leftJoin( 'resortinfo', 'resortinfo.resortinfo_id', '=', 'allotments.resortinfo_id' )
          //When creating a new record we need to know who!
          ->on( 'preCreate', function ( $editor, $values ) {
              $editor
                  ->field( 'allotments.user_add' )
                  ->setValue( $this->ion_auth->user()->row()->id );
            } )
          
          ->debug( true )
          ->process( $_POST )
          ->json();  
    }//End Allotment_View

Thank you Kevin and Allan for your help so far and being here to assist.

allan

Super - thank you.

Rather than using the ->json() method, call ->data(). The json method is basically just the same as:

$data = $editor->data();

echo json_encode($data);

e.g. you might use:

$data = Editor::inst( ... )
  ...
  ->process($_POST)
  ->data();

echo json_encode($data);

So just before that last line you can add extra information onto the $data object:

$data['myCounts'] = [...];

Allan

ldd954

Amazing,
Thank you Allan, I greatly appreciate your help and this wonderful tool.

Cheers.

ldd954

Just wanting to update this post with how I ended up performing this for anyone that may look for an answer similar to this.

My Modal providing the JSON data back and forth
Used $data to hold the editor

$data = Editor::inst( $this->editorDb, 'allotments', 'allotments_id' )
          ->fields(
            Field::inst( 'allotments.owner' ),
            Field::inst( 'allotments.resortinfo_id' )
....
->process( $_POST )
          ->data();

Then set some variables to hold my counts (which get them from another function

$data['Available'] = $this->getallotmentcount( $this->editorDb, 'allotments', 'Available', $post );
$data['Booked'] = $this->getallotmentcount( $this->editorDb, 'allotments', 'Booked', $post );
$data['All'] = $this->getallotmentcount( $this->editorDb, 'allotments', '', $post );

echo json_encode($data);

My count function just runs some SQL statements to pull the data how I want and then passing it back.

Thanks Allan and Kevin for your help!

colin

Excellent, thanks for posting back,

Colin