Dynatable from AJAX?

[hellomoto]

I want to implement tables with Dynatable populated via ajax. Here's what I could muster:

if($config->ajax) {
	$results = $pages->find($sanitizer->selector());
	foreach($results as $r) $array[] = [ $r->title, $r->modified ];
  header("Content-type: application/json");
	echo json_encode($array);
	exit;
}
$content .= <<<EOT
  <table id="my-ajax-table"></table>
  <script>
  $("#my-ajax-table").dynatable({
    dataset: {
      ajax: true,
      ajaxUrl: '.',
      ajaxOnLoad: true,
      records: []
    }
  });
  </script>
EOT;

But that's really nothing. Pro tips?

[abdus]

Here's how I did it. Searching, sorting, filtering, pagination all works

<?php namespace ProcessWire;

/* @var $config Config */
/* @var $pages Pages */
/* @var $input WireInput */
/* @var $sanitizer Sanitizer */


// fields to return
$fields = ['id', 'name', 'publishedStr', 'createdStr'];

if ($config->ajax) {

    // sanitize inputs
    $search = $sanitizer->selectorValue($input->get->queries['search']);
    $sorts = [];
    if ($input->get->sorts) {
        foreach ($input->get->sorts as $f => $direction) {
            $key = $sanitizer->fieldName($f);
            $direction = $sanitizer->int($direction, ['min' => -1, 'max' => 1]);
            if ((!$key) || (!$direction)) continue;
            $sorts[$key] = $direction > 0 ? '' : '-'; // sort=field or sort=-field
        }
    }
    $page = $sanitizer->int($input->get->page, ['min' => 0, 'blankValue' => 1]);
    $perPage = $sanitizer->int($input->get->perPage, ['min' => 10, 'max' => 100, 'blankValue' => 10]);
    $offset = $sanitizer->int($input->get->offset, ['min' => 0]);

    // base selector
    $selector = [
        "id>0",
        "include=all",
    ];
    $selectorFiltered = array_merge($selector, [
        "name|title*=$search", // change fields to search
    ]);
    $selectorFilteredLimited = array_merge($selectorFiltered, [
        "limit=$perPage",
        "start=$offset"
    ]);
    // include sorts as sort=(-)fieldName
    foreach ($sorts as $f => $d) {
        $selectorFilteredLimited[] = "sort={$d}{$f}";
    }

    // perform database query
    $totalCount = $pages->count(join(', ', $selector)); // # of all pages
    $queryCount = $pages->count(join(', ', $selectorFiltered)); // # of filtered pages
    $pageData = $pages->find(join(', ', $selectorFilteredLimited))->explode($fields); // data to return

    // output json
    header("Content-type: application/json");
    $data = [
        'records' => array_values($pageData),
        'queryRecordCount' => $queryCount,
        'totalRecordCount' => $totalCount
    ];
    echo json_encode($data);

    // stop
    return $this->halt();
}


?>
<?php if (!$config->ajax): ?>
    <table class="table">
        <thead>
        <tr>
            <?php foreach ($fields as $field): ?>
                <td><?= $field ?></td>
            <?php endforeach; ?>
        </tr>
        </thead>
        <tbody></tbody>
    </table>

    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta/css/bootstrap.min.css">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/Dynatable/0.3.1/jquery.dynatable.min.js"></script>
    <script>
        $('table').dynatable({
            dataset: {
                ajax: true,
                ajaxUrl: '.', // current page
                ajaxOnLoad: true,
                records: []
            }
        });
    </script>
<?php endif; ?>

Here's a screenshot

 

Although I think the code is clear, feel free to ask me if you have any questions.

https://processwire.com/api/ref/wire-array/explode/
https://processwire.com/api/ref/sanitizer/
https://processwire.com/api/ref/pages/count/
https://processwire.com/blog/posts/processwire-2.6.8-brings-new-version-of-reno-admin-theme-and-more/#new-this-gt-halt-method-for-use-in-template-files

[abdus]

Also, if your data doesnt really change often, you can use WireCache class to cache all relevant pages beforehand and perform searches in memory instead of db. This helps you reduce the server load and probably be a lot faster for large sets.

https://processwire.com/api/ref/cache/