Wordpress serverside search and sorting not working

Wordpress serverside search and sorting not working

mjm14mjm14 Posts: 2Questions: 0Answers: 0

This is my first post here and my first attempt at doing serverside and ajax with wordpress and datatables so keep that in mind. :)

At the moment search and sorting by a column does not work. When I search or click on a column header I see a new draw being triggered in the network tab in Chrome with "200 success" but nothing changes. I am kinda stumped as to what else I need to do.

Also the page length is not being honored with the default 10. It is showing all entries on the first page.

I really appreciate any assistance, and hopefully I have provided enough detail here.

Here is part of the json array I am getting from my php function which I think is fine:

{
    "data": [
        {
            "company_id": "22289",
            "created_by": "9508",
            "date_created": "2020-09-21 17:52:26",
            "date_updated": "2020-09-21 17:52:26",
            "entry_id": "1974",
            "po_status": false
        },
        {
            "company_id": "21057",
            "created_by": "9282",
            "date_created": "2020-09-17 19:47:21",
            "date_updated": "2020-09-17 19:47:21",
            "entry_id": "1947",
            "po_status": false
        }
    ],
    "draw": "1",
    "recordsFiltered": 2,
    "recordsTotal": 2
}

Here is the php code that gives me that array:

<?php
/**
 * AJAX Datatables GF Entries
 *
 * @package
 */

// Exit if accessed directly.
defined( 'ABSPATH' ) || exit;

if ( ! class_exists( 'Ajax_Datatables_GF_Entries' ) ) {

    /**
     * Ajax Buttons class.
     */
    class Ajax_Datatables_GF_Entries {

        /**
         * Constructor.
         *
         * @access public
         * @return void
         */
        public function __construct() {

            add_action( 'wp_enqueue_scripts', array( $this, 'datatables_gf_entries' ) );
            add_action( 'wp_ajax_datatables_gf_entries_action', array( $this, 'datatables_gf_entries_action' ) );
            add_action( 'wp_ajax_nopriv_datatables_gf_entries_action', array( $this, 'datatables_gf_entries_action' ) );

        }

        /**
         * AJAX Datatables GF Entries function.
         *
         * @access public
         */
        public function datatables_gf_entries() {

            wp_enqueue_script( 'datatables_gf_entries_action', get_stylesheet_directory_uri() . '/assets/js/ajax_datatables_gf_entries-min.js', array( 'jquery' ), '1.0.2', true );
            wp_localize_script(
                'datatables_gf_entries_action',
                'datatablesGFEntries',
                array(
                    'url'   => admin_url( 'admin-ajax.php?action=datatables_gf_entries_action' ),
                    'error' => __(
                        'Please try again',
                        'datatables-gf-entries'
                    ),
                )
            );

        }


        /**
         * AJAX Datatables GF Entries action.
         *
         * @access public
         */
        public function datatables_gf_entries_action() {

            $gf_entries = GFAPI::get_entries( 42 );

            $data = array();

            if ( ! empty( $gf_entries ) ) {

                $total_data = count( $gf_entries );

                foreach ( $gf_entries as $entry ) {

                    $form_id  = $entry['form_id'] ?? '';
                    $entry_id = $entry['id'] ?? '';

                    $po_status = gform_get_meta( $entry_id, 'po_status' ) ?? 'Not Processed';

                    $gf_user              = get_userdata( $entry['created_by'] );
                    $gf_user_display_name = $gf_user->display_name;

                    $created_by_company_id = get_user_meta( $entry['created_by'], 'company_id', true ) ?? '';

                    $companies_obj      = new Companies();
                    $created_by_company = $companies_obj->get_company( $created_by_company_id );

                    $data[] = array(
                        'entry_id'        => $entry['id'] ?? '',
                        // 'form_id'         => $entry['form_id'] ?? '',
                        'date_created'    => $entry['date_created'] ?? '',
                        'date_updated'    => $entry['date_updated'] ?? '',
                        // 'ip'              => $entry['ip'] ?? '',
                        // 'source_url'      => $entry['source_url'] ?? '',
                        // 'user_agent'      => $entry['user_agent'] ?? '',
                        'created_by'      => $entry['created_by'] ?? '',
                        // 'created_by_name' => $gf_user_display_name ?? '',
                        'company_id'      => $created_by_company_id ?? '',
                        // 'company_name'    => $created_by_company->name ?? '',
                        // 'is_starred'      => $entry['is_starred'] ?? '',
                        // 'is_read'         => $entry['is_read'] ?? '',
                        // 'source_url'      => $entry['source_url'] ?? '',
                        // 'user_agent'      => $entry['user_agent'] ?? '',
                        // 'resume_url'      => $entry['resume_url'] ?? '',
                        // 'project_name'    => $entry[1] ?? '',
                        'po_status'       => $po_status ?? 'Not Processed',
                    );


                }
            }

            $json_data = array(
                "draw"            => ( isset( $_POST[ 'draw' ] ) ? $_POST[ 'draw' ] : 0 ),
                'recordsTotal'    => intval( $total_data ),
                'recordsFiltered' => intval( $total_data ),
                'data'            => $data,

            );

                echo wp_json_encode( $json_data );

                wp_die();

        }
    }
    new Ajax_Datatables_GF_Entries();
}

and lastly here is the js file I have:

jQuery(document).ready(function($) {

    var editor;
    jQuery(document).ready(function() {
    editor = new jQuery.fn.dataTable.Editor( {
        table: "#gf-entries",
        fields: [ {
                label: "ID",
                name: "entry_id"
            }, {
                label: "Status",
                name: "po_status"
            }, {
                label: "Submitted By",
                name: "created_by"
            }, {
                label: "Company",
                name: "company_id"
            }, {
                label: "Created",
                name: "date_created"
            }
        ]
    } );
});

jQuery(function(){
    var ajaxurl = datatablesGFEntries.url;
    var table = jQuery("#gf-entries").DataTable({
        ajax:{
            url: ajaxurl,
            type: 'POST',
          },
        serverSide: true, 
        select: {
            style: 'os',
            items: 'cell'
        },
        buttons: [
            { extend: "create", editor: editor },
            { extend: "edit",   editor: editor },
            { extend: "remove", editor: editor }
        ],
        processing: true,
        columns: [
            {
                label: "ID",
                data: "entry_id",
                mRender: function(data, type, full){
                    if( type == 'sort' || type == 'undefined' || type == 'type' ){
                        return data;
                    }
                    if( data ) {
                        return '<a href="/forms/entries/' + data + '">' + data + '</a>';
                    } else {
                        return '-';
                    }
                }
            },
            {
                label: "Status",
                data: "po_status",
                mRender: function(data, type, full){
                    if( type == 'sort' || type == 'undefined' || type == 'type' ){
                        return data;
                    }
                    if( data === 'Ordered' ) {
                        return data;
                    } else {
                        return 'Not Processed';
                    }
                }
            }, 
            {
                label: "Submitted By",
                data: "created_by",
                mRender: function(data, type, row){
                    if( type == 'sort' || type == 'undefined' || type == 'type' ){
                        return data;
                    }
                    if( data ) {
                        return '<a href="/users/' + data + '">' + row.created_by_name + '</a>';
                    } else {
                        return '-';
                    }
                }
            }, 
            {
                label: "Company",
                data: "company_id",
                mRender: function(data, type, row){
                    if( type == 'sort' || type == 'undefined' || type == 'type' ){
                        return data;
                    }
                    if( data ) {
                        return '<a href="/companies/' + data + '">' + row.company_name + '</a>';
                    } else {
                        return '-';
                    }
                }
            }, 
            {
                label: "Created",
                data: "date_created"
            }
        ],
        pageLength: 10,
        select: true,
        dom: 'f' + "<'table-responsive't>" + "<'row align-items-center bottom'<'col-sm-5'il><'col-sm-7'p>>",
        columnDefs: [
            { width: 200, targets: '_all' },
        ],
        fixedColumns: true,
        download: [[ 0, "desc" ]],
        language: {
            search: '<i class="fas fa-search"></i>',
            searchPlaceholder: 'Search...',
            info: '_START_ to _END_ of _TOTAL_',
            infoEmpty: "",
            infoFiltered: "",
            zeroRecords: "<strong>No requests could be found.</strong>",
            lengthMenu: '_MENU_',
            oPaginate: {
                sNext: 'Next<i class="far fa-arrow-alt-circle-right ml-2"></i>',
                sPrevious: '<i class="far fa-arrow-alt-circle-left mr-2"></i></i>Prev',
            }
        },
    });
});
});

and finally the html:

<div class="table-responsive">

<table id="gf-entries" class="table">
        <thead>
            <tr>
                <th>ID</th>
                <th>Status</th>
                <th>Submitted By</th>
                <th>Company</th>
                <th>Created</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>ID</th>
                <th>Status</th>
                <th>Submitted By</th>
                <th>Company</th>
                <th>Created</th>
            </tr>
        </tfoot>
    </table>
</div>

I would greatly appreciate any assistance. Thanks so much!!

Replies

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    That all looks good. I'm a bit puzzled as you say you're getting all records back from the server, but that response:

        "draw": "1",
        "recordsFiltered": 2,
        "recordsTotal": 2
    

    is showing there are only two records total, so something is definitely odd there.

    Colin

  • mjm14mjm14 Posts: 2Questions: 0Answers: 0

    Thank you so much Colin. I refactored this a ton and it's now working, minus search and rendered sorting. I really am not seeing a lot of examples of using datatables with a rest api surprisingly.

    I have to also figure out how on earth to do edits using the same wordpress ajax file. I literally can't find a single example of someone doing this with wordpress and a rest api so far. I currently have like 100 tabs open from my googling. :)

    Outside of that datatables sure seems nice. I would pay for support but I just don't know how far out of scope some of this stuff is.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    edited March 2021

    It would be worth looking at this blog post - it discusses how to install Editor within a WordPress environment. That would be the best place to start to get that editing in place.

    Colin

This discussion has been closed.