WP_List_Tableでデータベースからページネーションとソート【pagination】

DBから取得するサンプルがなかったのでメモ

データベースからページネーションとソート

wp-content/plugins/my-list-table.php

<?php

/*
Plugin Name: My List Table
Version: 0.0.1
*/

if (!class_exists('WP_List_Table')) {
    require_once(ABSPATH . 'wp-admin/includes/class-wp-list-table.php');
}

class My_List_Table extends WP_List_Table
{
    public function prepare_items()
    {
        global $wpdb;
        $per_page = 2;
        $orderby  = $_GET['orderby'] ?? 'price';
        $order    = $_GET['order'] ?? 'desc';
        $order_by = sanitize_sql_orderby("$orderby $order");
        $offset   = ($this->get_pagenum() - 1) * $per_page;
        $sql = $wpdb->prepare("SELECT SQL_CALC_FOUND_ROWS * FROM wp_fruit ORDER BY $order_by LIMIT %d, %d", $offset, $per_page);
        $this->items = $wpdb->get_results($sql, ARRAY_A);

        $this->set_pagination_args(
            array(
                'total_items' => $wpdb->get_var('SELECT FOUND_ROWS()'),
                'per_page'    => $per_page
            )
        );
    }

    public function get_columns()
    {
        return [
            'name' => '名前',
            'color' => '色',
            'price' => '値段'
        ];
    }

    public function get_sortable_columns(): array
    {
        return array(
            'name' => 'name',
            'color' => 'color',
            'price' => 'price'
        );
    }

    public function column_default($item, $column_name)
    {
        return $item[$column_name];
    }
}

function add_admin_menu()
{
    $myListTable = new My_List_Table();
    add_menu_page('My List Table', 'My List Table', 'activate_plugins', 'my_list_table', function () use ($myListTable) {
        $myListTable->prepare_items();
        $myListTable->display();
    });
}
add_action('admin_menu', 'add_admin_menu');



// DB作成
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta("CREATE TABLE IF NOT EXISTS wp_fruit (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(45),
    color VARCHAR(45),
    price INT,
    PRIMARY KEY (id) )");

if ($wpdb->get_var('SELECT count(*) FROM wp_fruit') == 0) {
    $wpdb->insert('wp_fruit', ['name' => 'りんご', 'color' => '赤', 'price' => 100]);
    $wpdb->insert('wp_fruit', ['name' => 'バナナ', 'color' => '黄', 'price' =>  60]);
    $wpdb->insert('wp_fruit', ['name' => 'メロン', 'color' => '緑', 'price' => 200]);
}

検索もする

wp-content/plugins/my-list-table.php

<?php

/*
Plugin Name: My List Table
Version: 0.0.1
*/

if (!class_exists('WP_List_Table')) {
    require_once(ABSPATH . 'wp-admin/includes/class-wp-list-table.php');
}

class My_List_Table extends WP_List_Table
{
    public function prepare_items()
    {
        global $wpdb;
        $per_page = 2;
        $s = $_GET['s'] ?? '';
        $orderby  = $_GET['orderby'] ?? 'price';
        $order    = $_GET['order'] ?? 'desc';
        $order_by = sanitize_sql_orderby("$orderby $order");
        $offset   = ($this->get_pagenum() - 1) * $per_page;
        $sql = "SELECT SQL_CALC_FOUND_ROWS * FROM wp_fruit WHERE name LIKE %s ORDER BY $order_by LIMIT %d, %d";
        $args = ['%' . $wpdb->esc_like($s) . '%', $offset, $per_page];
        $this->items = $wpdb->get_results($wpdb->prepare($sql, ...$args), ARRAY_A);

        $this->set_pagination_args(
            array(
                'total_items' => $wpdb->get_var('SELECT FOUND_ROWS()'),
                'per_page'    => $per_page
            )
        );
    }

    public function get_columns()
    {
        return [
            'name' => '名前',
            'color' => '色',
            'price' => '値段'
        ];
    }

    public function get_sortable_columns(): array
    {
        return array(
            'name' => 'name',
            'color' => 'color',
            'price' => 'price'
        );
    }

    public function column_default($item, $column_name)
    {
        return $item[$column_name];
    }
}

function add_admin_menu()
{
    $myListTable = new My_List_Table();
    add_menu_page('My List Table', 'My List Table', 'activate_plugins', 'my_list_table', function () use ($myListTable) {
        echo '<form>';
        $myListTable->prepare_items();
        $myListTable->search_box('検索する', 'items');
        printf('<input type="hidden" name="page" value="%s" />', esc_attr($_GET['page']));
        $myListTable->display();
        echo '</form>';
    });
}
add_action('admin_menu', 'add_admin_menu');



// DB作成
require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta("CREATE TABLE IF NOT EXISTS wp_fruit (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(45),
    color VARCHAR(45),
    price INT,
    PRIMARY KEY (id) )");

if ($wpdb->get_var('SELECT count(*) FROM wp_fruit') == 0) {
    $wpdb->insert('wp_fruit', ['name' => 'りんご', 'color' => '赤', 'price' => 100]);
    $wpdb->insert('wp_fruit', ['name' => 'バナナ', 'color' => '黄', 'price' =>  60]);
    $wpdb->insert('wp_fruit', ['name' => 'メロン', 'color' => '緑', 'price' => 200]);
}