Skip to content

Instantly share code, notes, and snippets.

@ka215
Last active June 7, 2016 05:15
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ka215/996da83eca367e2ee250388180d0ddac to your computer and use it in GitHub Desktop.
Save ka215/996da83eca367e2ee250388180d0ddac to your computer and use it in GitHub Desktop.
「Custom DataBase Tables」のショートコード[cdbt-edit]および[cdbt-view]でログインしているuserIDのデータのみを表示させる(ただし管理者は全データにアクセス可能)を実現するためのフィルターフック。
<?php
// As a prerequisite, there are stored each user ID to the "user_id" column (as numric type) in the "your_table_name" as target table.
// (対象テーブル「your_table_name」には「user_id」カラム(数値型)にユーザーIDが格納されているという前提)
function custom_filter_get_data_sql( $sql, $table_name, $sql_clauses ) {
if ( ! is_admin() && "your_table_name" === $table_name ) {
$_current_user_id = 0; // For guest user
if ( is_user_logged_in() ) {
$current_user = wp_get_current_user();
$_current_user_id = $current_user->ID;
if ( ! isset( $current_user->caps['administrator'] ) || ! $current_user->caps['administrator'] ) { // If login-user is not administrator
$_new_sql = <<<SQL
SELECT %s
FROM %s
WHERE user_id=%s
%s %s
SQL;
$sql = sprintf( $_new_sql, $sql_clauses[0], $table_name, $_current_user_id, $sql_clauses[2], $sql_clauses[3] );
}
}
}
return $sql;
}
add_filter( 'cdbt_crud_get_data_sql', 'custom_filter_get_data_sql', 10, 3 );
<?php
// As a prerequisite, there are stored each user ID to the "user_id" column (as numric type or string type) in the "your_table_name" as target table.
// (対象テーブル「your_table_name」には「user_id」カラム(数値型および文字列型共用)にユーザーIDが格納されているという前提)
// In the array variable of "$target_table", you specify the table name that you want to filter as the key, then you specify the column name that stored user ID in that table as first argument, and the column value type (is whether int or varchar) as second argument to the children array. Thereby, you will be able to filter multiple table by only one code of filter hook.
// "$target_table"の配列変数にフィルターするテーブル名をキーにユーザーIDが格納されているカラム名、カラム値の形式(intかvarcharか)を指定することで、このフィルターフックの1コードだけで複数のテーブルをフィルターできます。
function custom_filter_sql( $sql, $table_name, $sql_clauses ) {
$target_table = [ 'your_table_name' => [ 'userid_column_intval', 'int' ], 'your_table_name2' => [ 'userid_column_strval', 'varchar' ] ];
if ( ! is_admin() && array_key_exists( $table_name, $target_table ) ) {
$_current_user_id = 0; // For guest user
if ( is_user_logged_in() ) {
$current_user = wp_get_current_user();
$_current_user_id = $current_user->ID;
}
list( $userid_column, $userid_column_type ) = $target_table[$table_name];
if ( ! isset( $current_user->caps['administrator'] ) || ! $current_user->caps['administrator'] ) { // If login-user is not administrator
if ( is_array( $sql_clauses[1] ) ) {
// Narrowing is find_data()
$_add_query = 'int' === $userid_column_type ? sprintf( 'AND %s=%d ', $userid_column, $_current_user_id ) : sprintf( "AND %s='%s' ", $userid_column, strval( $_current_user_id ) );
foreach ( $sql_clauses[1] as $_i => $_union_query ) {
$_before_query = function_exists( 'mb_substr' ) ? mb_substr( $_union_query, 0, -1 ) : substr( $_union_query, 0, -1 );
$sql_clauses[1][$_i] = $_before_query . $_add_query . ')';
}
$sql = implode( ' ', $sql_clauses[1] ) .' '. $sql_clauses[2] .' '. $sql_clauses[3];
} else {
// Narrowing is get_data()
$_new_sql = <<<SQL
SELECT %s
FROM %s
%s
%s %s
SQL;
$_add_query = 'int' === $userid_column_type ? sprintf( '%s=%d ', $userid_column, $_current_user_id ) : sprintf( "%s='%s' ", $userid_column, strval( $_current_user_id ) );
$_where_clause = empty( $sql_clauses[1] ) ? 'WHERE '. $_add_query : $sql_clauses[1] .'AND '. $_add_query;
$sql = sprintf( $_new_sql, $sql_clauses[0], $table_name, $_where_clause, $sql_clauses[2], $sql_clauses[3] );
}
}
}
return $sql;
}
add_filter( 'cdbt_crud_find_data_sql', 'custom_filter_sql', 10, 3 );
add_filter( 'cdbt_crud_get_data_sql', 'custom_filter_sql', 10, 3 );
@ka215
Copy link
Author

ka215 commented Jun 4, 2016

ショートコードで設定した絞り込み条件を同時に有効にしつつフィルターするコードを「with-narrow-down.php」として追加しました。

@ka215
Copy link
Author

ka215 commented Jun 7, 2016

with-narrow-down.phpの方、管理者のフルアクセス制御が抜けてますね…orz。
管理者制御したい場合は、16行目のif ( is_array( $sql_clauses[1] ) ) {のif文をnon-narrow-down.phpの11行目のif文で括れば対応できるかと。

@ka215
Copy link
Author

ka215 commented Jun 7, 2016

with-narrow-down.phpに管理者フルアクセス用の条件分岐を追加しました。

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment