I am very sorry, “sort_order” in the attributes of the shortcode to filter had leaked. Correct shortcode is as follows:
[cdbt-view table="wp_tips" display_title="false" order_cols="ID" sort_order="ID:asc"]
I could finally confirm the operating by configuration of the table, the shortcode and the filters such as the following.
Table structure:
CREATE TABLE wp_tips (
ID bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
username varchar(100) NOT NULL,
date_time datetime DEFAULT NULL,
league varchar(255) DEFAULT NULL,
match varchar(255) DEFAULT NULL,
tip varchar(10) DEFAULT NULL,
odds float(6,2) DEFAULT NULL,
book varchar(10) DEFAULT NULL,
win_loss varchar(4) DEFAULT NULL,
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
Display table in main page:
[cdbt-view table="wp_tips" display_title="false" exclude_cols="ID,username"]
Display table in other page (post id = 23):
[cdbt-view table="wp_tips" display_title="false" order_cols="ID" sort_order="ID:asc"]
Code of filter hooks into the “functions.php” at the theme:
if ( ! is_admin() ) :
function wp_tips_get_data_sql( $sql, $table_name, $sql_clauses ) {
if ( get_post()->ID === 23 && 'wp_tips' === $table_name ) {
$_overwrite_sql = <<< SQL
SELECT
count(id) AS BETS,
Sum(IF(win_loss = 'win', 1, 0)) As Wins,
Sum(IF(win_loss = 'loss', 1, 0)) As Looses,
Sum(IF(win_loss = 'void',1, 0)) AS Void
FROM %s ;
SQL;
$sql = sprintf( $_overwrite_sql, $table_name );
}
return $sql;
}
add_filter( 'cdbt_crud_get_data_sql', 'wp_tips_get_data_sql', 10, 3 );
function wp_tips_shortcode_custom_columns( $columns, $shortcode_name, $table ){
if ( get_post()->ID === 23 && 'cdbt-view' === $shortcode_name && 'wp_tips' === $table ) {
$columns = [];
$columns[] = [
'label' => 'BETS',
'property' => 'BETS',
'sortable' => false,
'sortDirection' => 'asc',
'dataNumric' => true,
'truncateStrings' => '0',
'className' => '',
];
$columns[] = [
'label' => 'Wins',
'property' => 'Wins',
'sortable' => false,
'sortDirection' => 'asc',
'dataNumric' => true,
'truncateStrings' => '0',
'className' => '',
];
$columns[] = [
'label' => 'Looses',
'property' => 'Looses',
'sortable' => false,
'sortDirection' => 'asc',
'dataNumric' => true,
'truncateStrings' => '0',
'className' => '',
];
$columns[] = [
'label' => 'Void',
'property' => 'Void',
'sortable' => false,
'sortDirection' => 'asc',
'dataNumric' => true,
'truncateStrings' => '0',
'className' => '',
];
}
return $columns;
}
add_filter( 'cdbt_shortcode_custom_columns', 'wp_tips_shortcode_custom_columns', 10, 3 );
endif;
Thank you,
