display data from different tables with queries

Thank you for your inquiry.

You can display merged multiple table data by using filter hooks.
Firstly at the “cdbt_crud_get_data_sql” filter, you join other tables to specified table at the shortcode. Please refer below example.

Table 1:

CREATE TABLE test_customer (
  ID bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  first_name varchar(100) NOT NULL COMMENT 'FirstName',
  last_name varchar(100) NOT NULL COMMENT 'LastName',
  gender enum('male','female') NOT NULL COMMENT 'Gender',
  age tinyint(3) unsigned DEFAULT NULL COMMENT 'Age',
  created datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created Datetime',
  updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated Datetime',
  PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

Table 2

CREATE TABLE test_address (
  ID bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  user_id bigint(20) unsigned NOT NULL COMMENT 'UserID',
  zipcode varchar(8) DEFAULT NULL COMMENT 'Zipcode',
  prefecture varchar(10) DEFAULT NULL COMMENT 'Prefecture',
  address varchar(100) DEFAULT NULL COMMENT 'Address',
  PRIMARY KEY (ID),
  KEY index_uid (user_id),
  CONSTRAINT index_uid FOREIGN KEY (user_id) REFERENCES test_customer (ID)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

Table 3

CREATE TABLE test_sales (
  ID bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'OrderID',
  user_id bigint(20) unsigned NOT NULL COMMENT 'UserID',
  amount int(11) unsigned NOT NULL COMMENT 'Amount',
  notes text COMMENT 'Notes',
  created datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Created Datetime',
  updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated Datetime',
  PRIMARY KEY (ID),
  KEY index_userid (user_id),
  CONSTRAINT index_userid FOREIGN KEY (user_id) REFERENCES test_customer (ID)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4

Shortcode

[cdbt-view table="test_customer" bootstrap_style="true" enable_repeater="true" display_list_num="false" display_search="true" display_title="true" enable_sort="true" display_index_row="true" display_cols="ID,first_name,last_name" sort_order="created:desc" limit_items="0" truncate_strings="0" image_render="responsive" display_filter="false" display_view="false" thumbnail_width="100" ajax_load="false"]

Fiter hooks

function inner_join_get_data_sql( $sql, $table_name, $sql_clauses ) {
  if ( 'test_customer' === $table_name ) {
    $_new_sql = <<<SQL
SELECT customer.ID,customer.first_name,customer.last_name,addr.prefecture,addr.address,AVG(sales.amount) as average
FROM %s customer
INNER JOIN %s addr ON customer.ID=addr.user_id
INNER JOIN %s sales ON customer.ID=sales.ID
GROUP BY customer.ID;
SQL;
    $sql = sprintf( $_new_sql, $table_name, 'test_address', 'test_sales' );
  }
  return $sql;
}
add_filter( 'cdbt_crud_get_data_sql', 'inner_join_get_data_sql', 10, 3 );

function inner_join_shortcode_custom_columns( $columns, $shortcode_name, $table ){
  if ( 'cdbt-view' === $shortcode_name && 'test_customer' === $table ) {
    $columns[] = [
      'label' => 'Prefecture',
      'property' => 'prefecture',
      'sortable' => true,
      'sortDirection' => 'asc',
      'dataNumric' => false,
      'truncateStrings' => '0',
      'className' => '',
    ];
    $columns[] = [
      'label' => 'Address',
      'property' => 'address',
      'sortable' => true,
      'sortDirection' => 'asc',
      'dataNumric' => false,
      'truncateStrings' => '0',
      'className' => '',
    ];
    $columns[] = [
      'label' => 'Average',
      'property' => 'average',
      'sortable' => true,
      'sortDirection' => 'asc',
      'dataNumric' => true,
      'truncateStrings' => '0',
      'className' => '',
    ];
  }
  return $columns;
}
add_filter( 'cdbt_shortcode_custom_columns', 'inner_join_shortcode_custom_columns', 10, 3 );

You can display your own data in a shortcode by using as “cdbt_crud_get_data_sql” filter hook as described above.

Thank you,

thanks this was very helpful ??

This ticket was closed to have corresponded already.