ka2

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,

Hi,
I’m very sorry, this trouble didn’t in by Arabic column name.

Because there had contained a single-byte space in the column name of “set” type.

“مواصفات أخرى” is wrong.

“مواصفات_أخرى” is OK.

Please try to modify the column name of the “set” type without single-byte spaces.

Thank you,

I could see of the problems in your favor.

In this plugin, it does not work when is using the characters other than alphanumeric and allowed symbols at the table name. Because the JavaScript that is rendered via the shortcode of “cdbt-view” and “cdbt-edit” cannot handle operating normally.

You should change to alphanumeric characters from the table name of Arabic.
Their shortcodes works normally after I change the names of all tables using the SQL below.

alter table used_cars change الموديل model varchar(255) NOT NULL comment 'الموديل';
alter table used_cars change (سعة المحرك (سي سي engine_capacity int(255) NOT NULL comment '(سعة المحرك (سي سي';
alter table used_cars change الماركة brand enum('أم جي','أوبل','أودي','ألفا روميو','بروتون','بريليانس','بورش','بي إم دبليو','بيجو','تويوتا','جاجوار','جريت وول','جيب','جيلي','دايهاتسو','دودج','رينو','سانج يونج','سيتروين','سكودا','سوبارو','سوزوكي','سيات','شنجان','شيري','شيفروليه','فورد','فولفو','فولكس فاجن','فيات','كرايسلر','كيا','لادا','لاند روفر','مازدا','ميتسوبيشي','مرسيدس','ميني','نيسان','هوندا','هيونداي') NOT NULL comment 'الماركة';
alter table used_cars change سنة الصنع manufacturing_year year(4) NOT NULL comment 'سنة الصنع';
alter table used_cars change عداد المسافة odometer int(11) NOT NULL comment 'عداد المسافة';
alter table used_cars change  الوصف description text comment 'الوصف';
alter table used_cars change تليفون telephone varchar(255) NOT NULL comment 'تليفون';
alter table used_cars change المحافظة province varchar(255) NOT NULL comment 'المحافظة';
alter table used_cars change مواصفات أخرى other_specifications set('تكييف','زجاج كهربائي','فتحة سقف','نظام فرامل ABS','سنتر لوك','إنذار','مثبت سرعة','توزيع الكتروني للفرامل EBD','باور','وسائد هوائية','راديو كاسيت') DEFAULT NULL comment 'مواصفات أخرى';
alter table used_cars change ملاحظات notes text comment 'ملاحظات';

Or, it may re-create the table in the following SQL.

CREATE TABLE used_cars (
  ID int(11) NOT NULL AUTO_INCREMENT,
  brand enum('أم جي','أوبل','أودي','ألفا روميو','بروتون','بريليانس','بورش','بي إم دبليو','بيجو','تويوتا','جاجوار','جريت وول','جيب','جيلي','دايهاتسو','دودج','رينو','سانج يونج','سيتروين','سكودا','سوبارو','سوزوكي','سيات','شنجان','شيري','شيفروليه','فورد','فولفو','فولكس فاجن','فيات','كرايسلر','كيا','لادا','لاند روفر','مازدا','ميتسوبيشي','مرسيدس','ميني','نيسان','هوندا','هيونداي') NOT NULL COMMENT 'الماركة',
  model varchar(255) NOT NULL COMMENT 'الموديل',
  engine_capacity int(255) NOT NULL COMMENT '(سعة المحرك (سي سي',
  manufacturing_year year(4) NOT NULL COMMENT 'سنة الصنع',
  odometer int(11) NOT NULL COMMENT 'عداد المسافة',
  description text COMMENT 'الوصف',
  telephone varchar(255) NOT NULL COMMENT 'تليفون',
  province varchar(255) NOT NULL COMMENT 'المحافظة',
  other_specifications set('تكييف','زجاج كهربائي','فتحة سقف','نظام فرامل ABS','سنتر لوك','إنذار','مثبت سرعة','توزيع الكتروني للفرامل EBD','باور','وسائد هوائية','راديو كاسيت') DEFAULT NULL COMMENT 'مواصفات أخرى',
  notes text COMMENT 'ملاحظات',
  PRIMARY KEY (ID)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

Thank you,

Thank you very much for reporting.

I found a bug of conversion processing of options to 2.x from version 1.x.
I’m going to release a fix in the next version.

For now, please try to the first aid by source modification of the following.

$_max_host_id = ! empty( $_api_hosts ) ? max( array_keys( $_api_hosts ) ) : 0;

Modify place is a 32-line of the “plugins/custom-database-tables/lib/webapis.php”.

Thank you,

Hi,

At the values of enum type, it should use single quote.
I was able to successfully created table in the following SQL.

CREATE TABLE wp_ha65rf_pin (
  ID bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  DOB date NOT NULL,
  TOB time NOT NULL,
  Planet enum('Sun','Moon','Mars','Venus','Jupiter','Saturn','Uranus','Neptune','Pluto') NOT NULL,
  Sign enum('Aries','Taurus','Gemini','Cancer','Leo','Virgo','Libra','Scorpio','Sagittarius','Capricorn','Aquarius','Pisces') NOT NULL,
  House enum('First','Second','Third','Fourth','Fifth','Sixth','Seventh','Eighth','Ninth','Tenth','Eleventh','Twelfth') NOT NULL,
  Aspect enum('Conjunction','Opposition','Sextile','Semi-Sextile','Square','Semi-Square','Trine','Quincunx','Sesqui-Square','Quintile','Bi-Quintile','Parallel','Contra Parallel') NOT NULL,
  NP enum('Sun','Moon','Mars','Venus','Jupiter','Saturn','Uranus','Neptune','Pluto') NOT NULL,
  NS enum('Aries','Taurus','Gemini','Cancer','Leo','Virgo','Libra','Scorpio','Sagittarius','Capricorn','Aquarius','Pisces') NOT NULL,
  NH enum('First','Second','Third','Fourth','Fifth','Sixth','Seventh','Eighth','Ninth','Tenth','Eleventh','Twelfth') NOT NULL,
  OtherAs enum('Yes','No') NOT NULL,
  Event varchar(3000) NOT NULL,
  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 DEFAULT CHARSET=utf8 COMMENT='Planets in Transit'

Thank you,

Hi,

I’m sorry, “enable_repeater=false” does not work yet.
Its function was still unfinished.

I am very sorry, but please wait until it is implemented.

Thank you,

Thank you very much for reporting.

I could not understand how to reproduce that you said.
There is no excuse, please tell me in detail the context which the phenomenon occurs.

Thank you,

This ticket was closed to have resolved.