Displaying custom record count, using server side processing

Displaying custom record count, using server side processing

ldd954ldd954 Posts: 6Questions: 1Answers: 0

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.

This question has an accepted answers - jump to answer

Answers

  • ldd954ldd954 Posts: 6Questions: 1Answers: 0

    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.

  • kthorngrenkthorngren Posts: 14,093Questions: 25Answers: 3,325

    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

  • ldd954ldd954 Posts: 6Questions: 1Answers: 0

    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?

  • allanallan Posts: 54,886Questions: 1Answers: 8,603 Site admin

    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

  • ldd954ldd954 Posts: 6Questions: 1Answers: 0

    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.

  • allanallan Posts: 54,886Questions: 1Answers: 8,603 Site admin
    Answer ✓

    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

  • ldd954ldd954 Posts: 6Questions: 1Answers: 0

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

    Cheers.

  • ldd954ldd954 Posts: 6Questions: 1Answers: 0

    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!

  • colincolin Posts: 12,890Questions: 0Answers: 2,198

    Excellent, thanks for posting back,

    Colin

Sign In or Register to comment.