| 1 |
<?php |
| 2 |
/** |
| 3 |
* Ability: wpdatatables/get-table-data |
| 4 |
* |
| 5 |
* Returns actual row data from a wpDataTable. |
| 6 |
* |
| 7 |
* Behaviour varies by table mode: |
| 8 |
* - Non-server-side tables: returns ALL rows in a single response, |
| 9 |
* together with column metadata so the AI can format them. |
| 10 |
* - Server-side tables: accepts optional pagination (page, per_page), |
| 11 |
* sorting (order_by, order_dir), and search (search, column_search) |
| 12 |
* parameters and returns one page of results plus totals. |
| 13 |
* - Simple (spreadsheet) tables: returns every cell via |
| 14 |
* WPDataTableRows::loadWpDataTableRows(). |
| 15 |
* |
| 16 |
* @package wpDataTables_MCP_Server |
| 17 |
*/ |
| 18 |
|
| 19 |
defined( 'ABSPATH' ) or die('Access denied.'); |
| 20 |
|
| 21 |
add_action( 'wp_abilities_api_init', 'wdtmcp_register_get_table_data_ability' ); |
| 22 |
|
| 23 |
function wdtmcp_register_get_table_data_ability() { |
| 24 |
wp_register_ability( |
| 25 |
'wpdatatables/get-table-data', |
| 26 |
array( |
| 27 |
'label' => __( 'Get wpDataTable Data', 'wpdatatables' ), |
| 28 |
'description' => __( 'Returns row data from a wpDataTable. For non-server-side tables the full dataset is returned in one call. For server-side tables (server_side=true in get-table-info) you MUST use the search and column_search parameters to filter data server-side rather than paginating through all rows manually. Use "search" for a global text search across all columns, or "column_search" to filter specific columns (e.g. {"Name": "bob"} to find rows where the Name column contains "bob"). You can also paginate (page, per_page), and sort (order_by, order_dir). The response includes column_headers with key/label/type so you know how to interpret the rows. Use get-table-info first to discover column names (orig_header) and whether the table is server-side. WHEN TO CALL: when the user asks to see, analyze, export, or answer questions about table contents. REQUIRED INPUT: table_id (integer). OPTIONAL: page, per_page, order_by (orig_header string), order_dir (asc|desc), search (global text), column_search (object mapping orig_header → value). DO NOT paginate through every page of a large server-side table — filter with search/column_search instead. TYPICAL WORKFLOW: list-tables → get-table-info → get-table-data. RETURNS: rows[], column_headers[], total_rows, page info, and server_side flag.', 'wpdatatables' ), |
| 29 |
'category' => 'wpdatatables-data', |
| 30 |
|
| 31 |
'input_schema' => array( |
| 32 |
'type' => 'object', |
| 33 |
'properties' => array( |
| 34 |
'table_id' => array( |
| 35 |
'type' => 'integer', |
| 36 |
'description' => 'The unique wpDataTable ID.', |
| 37 |
), |
| 38 |
'page' => array( |
| 39 |
'type' => 'integer', |
| 40 |
'description' => 'Page number (1-based). Only used for server-side tables. Defaults to 1.', |
| 41 |
), |
| 42 |
'per_page' => array( |
| 43 |
'type' => 'integer', |
| 44 |
'description' => 'Rows per page (max 500). Only used for server-side tables. Defaults to the table\'s display_length setting.', |
| 45 |
), |
| 46 |
'order_by' => array( |
| 47 |
'type' => 'string', |
| 48 |
'description' => 'Column orig_header to sort by. Only used for server-side tables.', |
| 49 |
), |
| 50 |
'order_dir' => array( |
| 51 |
'type' => 'string', |
| 52 |
'description' => 'Sort direction: "asc" or "desc". Defaults to "asc". Only used for server-side tables.', |
| 53 |
'enum' => array( 'asc', 'desc' ), |
| 54 |
), |
| 55 |
'search' => array( |
| 56 |
'type' => 'string', |
| 57 |
'description' => 'Global search term: filters rows where ANY column contains this text (case-insensitive LIKE match). For server-side tables only. Example: "bob" finds all rows containing "bob" in any column.', |
| 58 |
), |
| 59 |
'column_search' => array( |
| 60 |
'type' => 'object', |
| 61 |
'description' => 'Per-column filters: an object mapping column orig_header names to search values. Each filter matches rows where that column contains the value (LIKE match). For server-side tables only. Example: {"Name": "bob", "CountryCode": "USA"} finds rows where Name contains "bob" AND CountryCode contains "USA".', |
| 62 |
'additionalProperties' => array( 'type' => 'string' ), |
| 63 |
), |
| 64 |
), |
| 65 |
'required' => array( 'table_id' ), |
| 66 |
), |
| 67 |
|
| 68 |
'output_schema' => array( |
| 69 |
'type' => 'object', |
| 70 |
'properties' => array( |
| 71 |
'table_id' => array( |
| 72 |
'type' => 'integer', |
| 73 |
'description' => 'The table ID that was queried.', |
| 74 |
), |
| 75 |
'table_type' => array( |
| 76 |
'type' => 'string', |
| 77 |
'description' => 'Source type of this table.', |
| 78 |
), |
| 79 |
'server_side' => array( |
| 80 |
'type' => 'boolean', |
| 81 |
'description' => 'Whether server-side processing is active.', |
| 82 |
), |
| 83 |
'column_headers' => array( |
| 84 |
'type' => 'array', |
| 85 |
'description' => 'Ordered list of columns with their key, display label, and data type.', |
| 86 |
'items' => array( |
| 87 |
'type' => 'object', |
| 88 |
'properties' => array( |
| 89 |
'key' => array( 'type' => 'string', 'description' => 'Column orig_header (use as row object key).' ), |
| 90 |
'label' => array( 'type' => 'string', 'description' => 'Human-readable display header.' ), |
| 91 |
'type' => array( 'type' => 'string', 'description' => 'Data type (string, int, float, date, datetime, time, etc.).' ), |
| 92 |
), |
| 93 |
), |
| 94 |
), |
| 95 |
'rows' => array( |
| 96 |
'type' => 'array', |
| 97 |
'description' => 'Array of row objects keyed by column orig_header.', |
| 98 |
'items' => array( 'type' => 'object' ), |
| 99 |
), |
| 100 |
'total_rows' => array( |
| 101 |
'type' => 'integer', |
| 102 |
'description' => 'Total number of rows (before pagination/filtering).', |
| 103 |
), |
| 104 |
'filtered_rows' => array( |
| 105 |
'type' => 'integer', |
| 106 |
'description' => 'Number of rows matching the current filters (server-side only; equals total_rows otherwise).', |
| 107 |
), |
| 108 |
'page' => array( |
| 109 |
'type' => 'integer', |
| 110 |
'description' => 'Current page number (server-side only; 1 otherwise).', |
| 111 |
), |
| 112 |
'per_page' => array( |
| 113 |
'type' => 'integer', |
| 114 |
'description' => 'Rows per page used (server-side only; equals total_rows otherwise).', |
| 115 |
), |
| 116 |
), |
| 117 |
), |
| 118 |
|
| 119 |
'execute_callback' => 'wdtmcp_execute_get_table_data', |
| 120 |
|
| 121 |
'permission_callback' => function () { |
| 122 |
return current_user_can( 'manage_options' ); |
| 123 |
}, |
| 124 |
|
| 125 |
'meta' => array( |
| 126 |
'annotations' => array( |
| 127 |
'instructions' => __( 'Requires table_id. Call get-table-info first. For server_side=true tables, filter with search or column_search instead of paginating through every page.', 'wpdatatables' ), |
| 128 |
'readonly' => true, |
| 129 |
'destructive' => false, |
| 130 |
'idempotent' => true, |
| 131 |
), |
| 132 |
), |
| 133 |
) |
| 134 |
); |
| 135 |
} |
| 136 |
|
| 137 |
/** |
| 138 |
* Execute callback for wpdatatables/get-table-data. |
| 139 |
* |
| 140 |
* @param array $input |
| 141 |
* @return array|WP_Error |
| 142 |
*/ |
| 143 |
function wdtmcp_execute_get_table_data( $input ) { |
| 144 |
$table_id = isset( $input['table_id'] ) ? (int) $input['table_id'] : 0; |
| 145 |
if ( $table_id <= 0 ) { |
| 146 |
return new \WP_Error( |
| 147 |
'wdtmcp_invalid_input', |
| 148 |
__( 'A valid table_id (positive integer) is required.', 'wpdatatables' ) |
| 149 |
); |
| 150 |
} |
| 151 |
|
| 152 |
if ( ! class_exists( 'WDTConfigController' ) ) { |
| 153 |
return new \WP_Error( |
| 154 |
'wdtmcp_missing_class', |
| 155 |
__( 'wpDataTables core class WDTConfigController is not available.', 'wpdatatables' ) |
| 156 |
); |
| 157 |
} |
| 158 |
|
| 159 |
try { |
| 160 |
$table_config = \WDTConfigController::loadTableFromDB( $table_id ); |
| 161 |
} catch ( \Exception $e ) { |
| 162 |
return new \WP_Error( |
| 163 |
'wdtmcp_load_error', |
| 164 |
sprintf( __( 'Error loading table %d: %s', 'wpdatatables' ), $table_id, $e->getMessage() ) |
| 165 |
); |
| 166 |
} |
| 167 |
|
| 168 |
if ( empty( $table_config ) ) { |
| 169 |
return new \WP_Error( |
| 170 |
'wdtmcp_not_found', |
| 171 |
sprintf( __( 'Table with ID %d was not found.', 'wpdatatables' ), $table_id ) |
| 172 |
); |
| 173 |
} |
| 174 |
|
| 175 |
$table_type = isset( $table_config->table_type ) ? $table_config->table_type : ''; |
| 176 |
|
| 177 |
// Simple (spreadsheet) tables use a completely different storage model. |
| 178 |
if ( $table_type === 'simple' ) { |
| 179 |
return wdtmcp_get_simple_table_data( $table_id, $table_config ); |
| 180 |
} |
| 181 |
|
| 182 |
$is_server_side = ! empty( $table_config->server_side ); |
| 183 |
|
| 184 |
// Non-server-side: load everything via WPDataTable::loadWpDataTable(). |
| 185 |
if ( ! $is_server_side ) { |
| 186 |
return wdtmcp_get_full_table_data( $table_id, $table_config ); |
| 187 |
} |
| 188 |
|
| 189 |
// Server-side: build a controlled query with pagination/filter/sort. |
| 190 |
return wdtmcp_get_server_side_table_data( $table_id, $table_config, $input ); |
| 191 |
} |
| 192 |
|
| 193 |
/** |
| 194 |
* Build column_headers metadata from the table config's columns array. |
| 195 |
*/ |
| 196 |
function wdtmcp_build_column_headers( $table_config ) { |
| 197 |
$headers = array(); |
| 198 |
if ( ! empty( $table_config->columns ) && is_array( $table_config->columns ) ) { |
| 199 |
foreach ( $table_config->columns as $col ) { |
| 200 |
if ( ! empty( $col->visible ) ) { |
| 201 |
$headers[] = array( |
| 202 |
'key' => isset( $col->orig_header ) ? (string) $col->orig_header : '', |
| 203 |
'label' => isset( $col->display_header ) ? (string) $col->display_header : '', |
| 204 |
'type' => isset( $col->type ) ? (string) $col->type : 'string', |
| 205 |
); |
| 206 |
} |
| 207 |
} |
| 208 |
} |
| 209 |
return $headers; |
| 210 |
} |
| 211 |
|
| 212 |
/** |
| 213 |
* Full load for non-server-side tables (CSV, Excel, JSON, Google Sheets, SQL |
| 214 |
* without server-side, manual without server-side, etc.). |
| 215 |
*/ |
| 216 |
function wdtmcp_get_full_table_data( $table_id, $table_config ) { |
| 217 |
if ( ! class_exists( 'WPDataTable' ) ) { |
| 218 |
return new \WP_Error( 'wdtmcp_missing_class', __( 'WPDataTable class is not available.', 'wpdatatables' ) ); |
| 219 |
} |
| 220 |
|
| 221 |
try { |
| 222 |
$wdt = \WPDataTable::loadWpDataTable( $table_id, null, true ); |
| 223 |
} catch ( \Exception $e ) { |
| 224 |
return new \WP_Error( |
| 225 |
'wdtmcp_load_error', |
| 226 |
sprintf( __( 'Error loading table data for table %d: %s', 'wpdatatables' ), $table_id, $e->getMessage() ) |
| 227 |
); |
| 228 |
} |
| 229 |
|
| 230 |
$rows = $wdt->getDataRows(); |
| 231 |
$row_count = is_array( $rows ) ? count( $rows ) : 0; |
| 232 |
$table_type = isset( $table_config->table_type ) ? $table_config->table_type : ''; |
| 233 |
|
| 234 |
return array( |
| 235 |
'table_id' => $table_id, |
| 236 |
'table_type' => ( $table_type === 'mysql' ) ? 'SQL' : $table_type, |
| 237 |
'server_side' => false, |
| 238 |
'column_headers' => wdtmcp_build_column_headers( $table_config ), |
| 239 |
'rows' => is_array( $rows ) ? array_values( $rows ) : array(), |
| 240 |
'total_rows' => $row_count, |
| 241 |
'filtered_rows' => $row_count, |
| 242 |
'page' => 1, |
| 243 |
'per_page' => $row_count, |
| 244 |
); |
| 245 |
} |
| 246 |
|
| 247 |
/** |
| 248 |
* Paginated / filtered load for server-side tables. |
| 249 |
* |
| 250 |
* Instead of manipulating $_POST globals for wpDataTables' internal server-side |
| 251 |
* path, we run a controlled query directly against the table's SQL content. This |
| 252 |
* keeps things safe (our own parameter allowlisting) and avoids side-effects. |
| 253 |
* |
| 254 |
* Mirrors the relevant parts of WPDataTable::queryBasedConstruct(): |
| 255 |
* - Applies wpDataTables filter hooks so third-party code can modify queries. |
| 256 |
* - Uses vendor-aware LIMIT/OFFSET syntax (MySQL, MSSQL, PostgreSQL). |
| 257 |
* - Uses vendor-aware LIKE expressions (PostgreSQL LOWER(CAST(...))). |
| 258 |
*/ |
| 259 |
function wdtmcp_get_server_side_table_data( $table_id, $table_config, $input ) { |
| 260 |
global $wpdb; |
| 261 |
|
| 262 |
$base_query = isset( $table_config->content ) ? $table_config->content : ''; |
| 263 |
if ( empty( $base_query ) ) { |
| 264 |
return new \WP_Error( |
| 265 |
'wdtmcp_no_query', |
| 266 |
__( 'Server-side table has no SQL query content.', 'wpdatatables' ) |
| 267 |
); |
| 268 |
} |
| 269 |
|
| 270 |
$column_headers = wdtmcp_build_column_headers( $table_config ); |
| 271 |
$valid_columns = array_column( $column_headers, 'key' ); |
| 272 |
|
| 273 |
if ( empty( $valid_columns ) ) { |
| 274 |
return new \WP_Error( 'wdtmcp_no_columns', __( 'No visible columns found for this table.', 'wpdatatables' ) ); |
| 275 |
} |
| 276 |
|
| 277 |
// Resolve connection vendor (MySQL is the default for the WP connection). |
| 278 |
$connection_name = isset( $table_config->connection ) ? $table_config->connection : ''; |
| 279 |
$use_separate = class_exists( 'Connection' ) && \Connection::isSeparate( $connection_name ); |
| 280 |
|
| 281 |
$vendor = 'mysql'; |
| 282 |
$lq = '`'; |
| 283 |
$rq = '`'; |
| 284 |
if ( $use_separate ) { |
| 285 |
$vendor = \Connection::getVendor( $connection_name ); |
| 286 |
$lq = \Connection::getLeftColumnQuote( $vendor ); |
| 287 |
$rq = \Connection::getRightColumnQuote( $vendor ); |
| 288 |
} |
| 289 |
|
| 290 |
$is_mysql = ( $vendor === 'mysql' ); |
| 291 |
$is_mssql = ( $vendor === 'mssql' ); |
| 292 |
$is_postgresql = ( $vendor === 'postgresql' ); |
| 293 |
|
| 294 |
// Apply placeholder variables ($wdtVar1 … $wdtVar9) and sanitize. |
| 295 |
if ( class_exists( 'WDTTools' ) ) { |
| 296 |
$base_query = \WDTTools::applyPlaceholders( $base_query ); |
| 297 |
} |
| 298 |
if ( function_exists( 'wdtSanitizeQuery' ) ) { |
| 299 |
$base_query = wdtSanitizeQuery( $base_query ); |
| 300 |
} |
| 301 |
|
| 302 |
// Hook: let third-party code modify the query before LIMIT is appended. |
| 303 |
$base_query = apply_filters( 'wpdatatables_filter_query_before_limit', $base_query, $table_id ); |
| 304 |
|
| 305 |
$wrapped = "SELECT * FROM ({$base_query}) AS wdtmcp_data"; |
| 306 |
|
| 307 |
// ----- WHERE clause -------------------------------------------------- |
| 308 |
$where_parts = array(); |
| 309 |
|
| 310 |
if ( ! empty( $input['search'] ) && is_string( $input['search'] ) ) { |
| 311 |
$like_parts = array(); |
| 312 |
foreach ( $valid_columns as $col ) { |
| 313 |
$like_parts[] = wdtmcp_like_expr( $vendor, $lq, $rq, $col, $input['search'], $wpdb ); |
| 314 |
} |
| 315 |
if ( ! empty( $like_parts ) ) { |
| 316 |
$where_parts[] = '(' . implode( ' OR ', $like_parts ) . ')'; |
| 317 |
} |
| 318 |
} |
| 319 |
|
| 320 |
if ( ! empty( $input['column_search'] ) && is_array( $input['column_search'] ) ) { |
| 321 |
foreach ( $input['column_search'] as $col => $val ) { |
| 322 |
if ( ! in_array( $col, $valid_columns, true ) ) { |
| 323 |
continue; |
| 324 |
} |
| 325 |
$where_parts[] = wdtmcp_like_expr( $vendor, $lq, $rq, $col, $val, $wpdb ); |
| 326 |
} |
| 327 |
} |
| 328 |
|
| 329 |
$where_sql = ''; |
| 330 |
if ( ! empty( $where_parts ) ) { |
| 331 |
$where_sql = ' WHERE ' . implode( ' AND ', $where_parts ); |
| 332 |
} |
| 333 |
|
| 334 |
// ----- ORDER BY ------------------------------------------------------ |
| 335 |
$order_sql = ''; |
| 336 |
if ( ! empty( $input['order_by'] ) && in_array( $input['order_by'], $valid_columns, true ) ) { |
| 337 |
$dir = ( ! empty( $input['order_dir'] ) && strtolower( $input['order_dir'] ) === 'desc' ) ? 'DESC' : 'ASC'; |
| 338 |
$order_sql = " ORDER BY {$lq}{$input['order_by']}{$rq} {$dir}"; |
| 339 |
} |
| 340 |
|
| 341 |
// ----- Pagination (vendor-aware LIMIT/OFFSET) ------------------------ |
| 342 |
$display_length = isset( $table_config->display_length ) ? (int) $table_config->display_length : 25; |
| 343 |
$per_page = isset( $input['per_page'] ) ? min( max( (int) $input['per_page'], 1 ), 500 ) : $display_length; |
| 344 |
$page = isset( $input['page'] ) ? max( (int) $input['page'], 1 ) : 1; |
| 345 |
$offset = ( $page - 1 ) * $per_page; |
| 346 |
|
| 347 |
$limit_sql = wdtmcp_limit_expr( $vendor, $per_page, $offset, $order_sql ); |
| 348 |
|
| 349 |
// ----- Build final SQL statements ------------------------------------ |
| 350 |
$count_total_sql = "SELECT COUNT(*) FROM ({$base_query}) AS wdtmcp_cnt"; |
| 351 |
$count_filtered_sql = "SELECT COUNT(*) FROM ({$base_query}) AS wdtmcp_data{$where_sql}"; |
| 352 |
$data_sql = "{$wrapped}{$where_sql}{$order_sql}{$limit_sql}"; |
| 353 |
|
| 354 |
// Hook: let third-party code modify the final data query. |
| 355 |
$data_sql = apply_filters( 'wpdatatables_filter_mysql_query', $data_sql, $table_id ); |
| 356 |
|
| 357 |
// ----- Execute ------------------------------------------------------- |
| 358 |
if ( $use_separate ) { |
| 359 |
$sql_link = \Connection::getInstance( $connection_name ); |
| 360 |
|
| 361 |
$total_raw = $sql_link->getField( $count_total_sql ); |
| 362 |
if ( false === $total_raw ) { |
| 363 |
$last_err = method_exists( $sql_link, 'getLastError' ) ? $sql_link->getLastError() : ''; |
| 364 |
if ( ! empty( $last_err ) ) { |
| 365 |
return new \WP_Error( 'wdtmcp_query_error', 'Count query failed: ' . $last_err ); |
| 366 |
} |
| 367 |
$total_raw = 0; |
| 368 |
} |
| 369 |
$total_rows = (int) $total_raw; |
| 370 |
|
| 371 |
$filtered_raw = $sql_link->getField( $count_filtered_sql ); |
| 372 |
$filtered = ( false !== $filtered_raw ) ? (int) $filtered_raw : 0; |
| 373 |
|
| 374 |
$rows = $sql_link->getAssoc( $data_sql ); |
| 375 |
if ( false === $rows || ! is_array( $rows ) ) { |
| 376 |
$last_err = method_exists( $sql_link, 'getLastError' ) ? $sql_link->getLastError() : ''; |
| 377 |
if ( ! empty( $last_err ) ) { |
| 378 |
return new \WP_Error( 'wdtmcp_query_error', 'Data query failed: ' . $last_err ); |
| 379 |
} |
| 380 |
$rows = array(); |
| 381 |
} |
| 382 |
} else { |
| 383 |
$total_rows = (int) $wpdb->get_var( $count_total_sql ); |
| 384 |
$filtered = (int) $wpdb->get_var( $count_filtered_sql ); |
| 385 |
$rows = $wpdb->get_results( $data_sql, ARRAY_A ); |
| 386 |
|
| 387 |
if ( ! is_array( $rows ) ) { |
| 388 |
return new \WP_Error( |
| 389 |
'wdtmcp_query_error', |
| 390 |
__( 'Failed to retrieve data from the table.', 'wpdatatables' ) . |
| 391 |
( ! empty( $wpdb->last_error ) ? ' ' . $wpdb->last_error : '' ) |
| 392 |
); |
| 393 |
} |
| 394 |
} |
| 395 |
|
| 396 |
$table_type = isset( $table_config->table_type ) ? $table_config->table_type : ''; |
| 397 |
|
| 398 |
return array( |
| 399 |
'table_id' => $table_id, |
| 400 |
'table_type' => ( $table_type === 'mysql' ) ? 'SQL' : $table_type, |
| 401 |
'server_side' => true, |
| 402 |
'column_headers' => $column_headers, |
| 403 |
'rows' => $rows, |
| 404 |
'total_rows' => $total_rows, |
| 405 |
'filtered_rows' => $filtered, |
| 406 |
'page' => $page, |
| 407 |
'per_page' => $per_page, |
| 408 |
); |
| 409 |
} |
| 410 |
|
| 411 |
/** |
| 412 |
* Build a vendor-appropriate LIKE expression for a single column. |
| 413 |
* |
| 414 |
* Matches the patterns used in WPDataTable::getLikeExpression(): |
| 415 |
* - MySQL / MSSQL : `col` LIKE '%value%' |
| 416 |
* - PostgreSQL : LOWER(CAST("col" AS TEXT)) LIKE LOWER('%value%') |
| 417 |
* |
| 418 |
* @param string $vendor Connection vendor identifier. |
| 419 |
* @param string $lq Left column quote character. |
| 420 |
* @param string $rq Right column quote character. |
| 421 |
* @param string $column Column orig_header. |
| 422 |
* @param string $value Raw search value (will be escaped). |
| 423 |
* @param wpdb $wpdb WordPress database object (used for escaping). |
| 424 |
* @return string SQL fragment. |
| 425 |
*/ |
| 426 |
function wdtmcp_like_expr( $vendor, $lq, $rq, $column, $value, $wpdb ) { |
| 427 |
$like_value = '%' . $wpdb->esc_like( $value ) . '%'; |
| 428 |
|
| 429 |
if ( $vendor === 'postgresql' ) { |
| 430 |
return $wpdb->prepare( |
| 431 |
"LOWER(CAST({$lq}{$column}{$rq} AS TEXT)) LIKE LOWER(%s)", |
| 432 |
$like_value |
| 433 |
); |
| 434 |
} |
| 435 |
|
| 436 |
return $wpdb->prepare( |
| 437 |
"{$lq}{$column}{$rq} LIKE %s", |
| 438 |
$like_value |
| 439 |
); |
| 440 |
} |
| 441 |
|
| 442 |
/** |
| 443 |
* Build a vendor-appropriate LIMIT/OFFSET clause. |
| 444 |
* |
| 445 |
* Matches the patterns used in WPDataTable::queryBasedConstruct(): |
| 446 |
* - MySQL : LIMIT {per_page} OFFSET {offset} |
| 447 |
* - PostgreSQL : LIMIT {per_page} OFFSET {offset} |
| 448 |
* - MSSQL : [ORDER BY (SELECT NULL)] OFFSET {offset} ROWS FETCH NEXT {per_page} ROWS ONLY |
| 449 |
* |
| 450 |
* @param string $vendor Connection vendor identifier. |
| 451 |
* @param int $per_page Number of rows to return. |
| 452 |
* @param int $offset Number of rows to skip. |
| 453 |
* @param string $order_sql The ORDER BY clause already built (empty string if none). |
| 454 |
* @return string SQL fragment. |
| 455 |
*/ |
| 456 |
function wdtmcp_limit_expr( $vendor, $per_page, $offset, $order_sql ) { |
| 457 |
if ( $vendor === 'mssql' ) { |
| 458 |
$needs_default_order = empty( $order_sql ); |
| 459 |
return ( $needs_default_order ? ' ORDER BY (SELECT NULL)' : '' ) |
| 460 |
. " OFFSET {$offset} ROWS FETCH NEXT {$per_page} ROWS ONLY"; |
| 461 |
} |
| 462 |
|
| 463 |
// MySQL and PostgreSQL share the same LIMIT/OFFSET syntax. |
| 464 |
return " LIMIT {$per_page} OFFSET {$offset}"; |
| 465 |
} |
| 466 |
|
| 467 |
/** |
| 468 |
* Simple (spreadsheet) tables store data in wp_wpdatatables_rows as JSON. |
| 469 |
*/ |
| 470 |
function wdtmcp_get_simple_table_data( $table_id, $table_config ) { |
| 471 |
if ( ! class_exists( 'WPDataTableRows' ) ) { |
| 472 |
return new \WP_Error( 'wdtmcp_missing_class', __( 'WPDataTableRows class is not available.', 'wpdatatables' ) ); |
| 473 |
} |
| 474 |
|
| 475 |
try { |
| 476 |
$wdt_rows = \WPDataTableRows::loadWpDataTableRows( $table_id ); |
| 477 |
} catch ( \Exception $e ) { |
| 478 |
return new \WP_Error( |
| 479 |
'wdtmcp_load_error', |
| 480 |
sprintf( __( 'Error loading simple table %d: %s', 'wpdatatables' ), $table_id, $e->getMessage() ) |
| 481 |
); |
| 482 |
} |
| 483 |
|
| 484 |
$col_headers_raw = $wdt_rows->getColHeaders(); |
| 485 |
$column_headers = array(); |
| 486 |
foreach ( $col_headers_raw as $idx => $label ) { |
| 487 |
$column_headers[] = array( |
| 488 |
'key' => 'col_' . $idx, |
| 489 |
'label' => (string) $label, |
| 490 |
'type' => 'string', |
| 491 |
); |
| 492 |
} |
| 493 |
|
| 494 |
$raw_rows = $wdt_rows->getRowsData(); |
| 495 |
$rows = array(); |
| 496 |
foreach ( $raw_rows as $row_obj ) { |
| 497 |
$row = array(); |
| 498 |
if ( isset( $row_obj->cells ) && is_array( $row_obj->cells ) ) { |
| 499 |
foreach ( $row_obj->cells as $idx => $cell ) { |
| 500 |
$key = 'col_' . $idx; |
| 501 |
$row[ $key ] = isset( $cell->data ) ? $cell->data : ''; |
| 502 |
} |
| 503 |
} |
| 504 |
$rows[] = $row; |
| 505 |
} |
| 506 |
|
| 507 |
$row_count = count( $rows ); |
| 508 |
|
| 509 |
return array( |
| 510 |
'table_id' => $table_id, |
| 511 |
'table_type' => 'simple', |
| 512 |
'server_side' => false, |
| 513 |
'column_headers' => $column_headers, |
| 514 |
'rows' => $rows, |
| 515 |
'total_rows' => $row_count, |
| 516 |
'filtered_rows' => $row_count, |
| 517 |
'page' => 1, |
| 518 |
'per_page' => $row_count, |
| 519 |
); |
| 520 |
} |
| 521 |
|
