Error in filtering custom post by acf fields in admin

Question

I am adding filter for custom post 'magazine' by acf fields 'issue_year'. I create the dropdown and filter the post but I am getting following error.

Error:

WordPress database error Not unique table/alias: 'wp_postmeta' for query SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id  WHERE 1=1  AND ( 
  ( wp_postmeta.meta_key = 'issue_year' AND wp_postmeta.meta_value = '2017' )
) AND wp_posts.post_type = 'magazine' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'future' OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 20 made by WP_List_Table->display, WP_List_Table->display_tablenav, WP_Posts_List_Table->extra_tablenav, do_action('manage_posts_extra_tablenav'), WP_Hook->do_action, WP_Hook->apply_filters, WPSEO_Link_Columns->count_objects, WPSEO_Link_Columns->set_count_objects, WP_Query->get_posts

Code: for dropdown:

add_action('restrict_manage_posts', 'filter_experts_by_issue_year');
function filter_experts_by_issue_year() {
    $post_type = 'magazine';
    global $wpdb;
    $query = $wpdb->prepare('
        SELECT DISTINCT pm.meta_value FROM %1$s pm
        LEFT JOIN %2$s p ON p.ID = pm.post_id
        WHERE pm.meta_key = "%3$s" 
        AND p.post_status = "%4$s" 
        AND p.post_type = "%5$s"
        ORDER BY "%3$s"',
        $wpdb->postmeta,
        $wpdb->posts,
        'issue_year',
        'publish',
        $post_type
    );
    $results = $wpdb->get_col($query);
    if(empty($results))
        return;

    /** Grab all of the options that should be shown */
    $options[] = sprintf('<option value="-1">%1$s</option>', __('Issue Year', 'your-text-domain'));
    foreach($results as $result) :
        $options[] = sprintf('<option value="%1$s">%2$s</option>', esc_attr($result), $result);
    endforeach;

    /** Output the dropdown menu */
    echo '<select class="" id="issue_year" name="issue_year">';
    echo join("\n", $options);
    echo '</select>';
}

for filter:

add_filter( 'parse_query', 'prefix_magazine_filter' );
function  prefix_magazine_filter($query) {
    global $pagenow;
    $current_page = isset( $_GET['post_type'] ) ? $_GET['post_type'] : '';

    if ( is_admin() && 'magazine' == $current_page && 'edit.php' == $pagenow && isset( $_GET['issue_year'] ) && $_GET['issue_year'] != '-1') {
        $query->query_vars['meta_key'] = 'issue_year';
        $query->query_vars['meta_value'] = $_GET['issue_year'];
        $query->query_vars['compare'] = '=';

    }
}

wp version: 4.9.1 acf pro: 5.6.5

Deepti chipdey · Answer 1 · 2018-01-19T04:11:05.873

1

it seems to me your order by clause has the error

USE this code

$query = $wpdb->prepare('
    SELECT DISTINCT pm.meta_value 
    FROM $wpdb->postmeta pm
    LEFT JOIN $wpdb->posts p
    ON p.ID = pm.post_id
    WHERE pm.meta_key = "%s" 
    AND p.post_status = "%s" 
    AND p.post_type = "%s"
    ORDER BY pm.meta_value',
    'issue_year',
    'publish',
     $post_type
);

edited Jan 19 '18 at 04:11

answered Jan 18 '18 at 11:34

Deepti chipdey

1,157
9
19

not working still getting the same error. [Not unique table/alias: 'wp_postmeta'] – samjhana joshi Jan 19 '18 at 03:59
Just updated the query, please check and let me know what do you get – Deepti chipdey Jan 19 '18 at 04:03
can you remove the distinct and try, also tell me are you using WPML plugin and which version of WP, I have updated the query once again, Remove the distinct and try again – Deepti chipdey Jan 19 '18 at 04:12
no success. Right now I am not using any WPML plugin. WP version is 4.9 with ACF Pro version 5.6.5 – samjhana joshi Jan 19 '18 at 04:21
@Deeoti chipdey ... after I click the 'Filter' button the above error shows. – samjhana joshi Jan 19 '18 at 04:42

score 0 · Answer 2 · answered Feb 11 '18 at 09:31

I got the answer for the error caused. For the front end search I have added the following 3 filters.

add_filter('posts_join', 'cf_search_join' );
add_filter( 'posts_where', 'cf_search_where' );
add_filter( 'posts_distinct', 'cf_search_distinct' );

The error has been caused by this filter added. So, I changed them as follows.

if(!is_admin()) {
    add_filter('posts_join', 'cf_search_join' );
    add_filter( 'posts_where', 'cf_search_where' );
    add_filter( 'posts_distinct', 'cf_search_distinct' );
}

Hope this helps.

Error in filtering custom post by acf fields in admin

2 Answers2