php - 通过 MySQL 查询按 Wordpress 用户显示名称获取帖子

Question

我在 Wordpress 中有一个搜索表单，可以搜索自定义帖子类型中的所有帖子标题、内容和自定义字段。但它不会按作者搜索。如果搜索关键字与显示名称匹配，我希望继续并输出该作者的任何帖子。

这是我当前的代码(它的 display_name 部分显然是错误的，但说明了我想要完成的事情)。除了通过 display_name 部分获取帖子外，一切正常。感谢任何帮助或指导:)

// Code originally modified 
// from http://www.deluxeblogtips.com/2012/04/search-all-custom-fields.html

global $wpdb;
// Gets the ?crs= search from the submitted form
$keyword = sanitize_text_field( $_GET['crs'] );
$keyword = '%' . like_escape( $keyword ) . '%'; 

// Search in all custom fields
$post_ids_meta = $wpdb->get_col( $wpdb->prepare( "
    SELECT DISTINCT post_id FROM {$wpdb->postmeta}
    WHERE meta_value LIKE '%s'
", $keyword ) );

// Search in post_title and post_content
$post_ids_post = $wpdb->get_col( $wpdb->prepare( "
    SELECT DISTINCT ID FROM {$wpdb->posts}
    WHERE post_title LIKE '%s'
    OR post_content LIKE '%s'
", $keyword, $keyword ) );

// Search in User Table for Display Name
// This is where I'm not sure what how to get the posts by display_name
$post_ids_user = $wpdb->get_col( $wpdb->prepare( "
    SELECT DISTINCT post_id FROM {$wpdb->users}
    WHERE display_name LIKE '%s'
", $keyword ) );

$post_ids = array_merge( $post_ids_meta, $post_ids_post, $post_ids_user );

// Query arguments for WP_Query
$args = array(
    'post_type'   => 'courses',
    'post_status' => 'publish',
    'limit' => '',
    'post__in'    => $post_ids,
);

Answer 1

我想建议的一件事是不要运行多个查询，而是加入您的表并立即解决

SELECT DISTINCT post_id FROM {$wpdb->users} WHERE display_name LIKE '%s' this is wrong query you cannot get the post ids from users table ,relation of posts and user is maintained in the posts table and each post contains user id in post_author column

试试这个

$post_ids = $wpdb->get_col( $wpdb->prepare( "
    SELECT p.ID FROM $wpdb->posts p
INNER JOIN $wpdb->users u ON (p.`post_author` = u.`ID`)
LEFT JOIN $wpdb->postmeta m ON (p.`ID`= m.`post_id`)
WHERE ( u.display_name LIKE '%s' OR p.post_title LIKE '%s' 
OR p.post_content LIKE '%s' OR m.meta_value LIKE '%s')
GROUP BY p.`ID`
", $keyword ) );

//print_r($post_ids); //you will get the post ids
$post_ids=array_values(array_unique($post_ids));