I tried to add a column "Downloads" in the media library and make it sortable. This all works fine, but as soon as I search for a filename next to the sorting, the following error occurs:

WordPress-Database-Error: [Not unique table/alias: 'wp_postmeta']
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
    LEFT JOIN wp_postmeta AS sq1 ON (wp_posts.ID = sq1.post_id
            AND sq1.meta_key = '_wp_attached_file')
WHERE
    1 = 1
    AND(((wp_posts.post_title LIKE '%pdf%')
    OR(wp_postmeta.meta_value LIKE '%pdf%')
    OR(wp_posts.post_excerpt LIKE '%pdf%')
    OR(wp_posts.post_content LIKE '%pdf%')
    OR(sq1.meta_value LIKE '%pdf%')))
    AND(wp_postmeta.meta_key = 'download_count')
    AND wp_posts.post_type = 'attachment'
    AND((wp_posts.post_status = 'inherit'
        OR wp_posts.post_status = 'private'))
GROUP BY
    wp_posts.ID
ORDER BY
    wp_postmeta.meta_value + 0 ASC
LIMIT 0,
20

I know that it is because the tablename is used multiple times. But I don't know how I can avoid this. Can you tell me how I can work around this error? Here you can see how I added the column and made it sortable:

/*
 * Add Sortable Download Count Column to the Media Library
 *
 */

if( is_admin() )
{
    add_filter( 'manage_upload_columns', 'my_download_count_column_register' );
    add_action( 'manage_media_custom_column', 'my_download_count_column_display', 10, 2 );
    add_filter( 'manage_upload_sortable_columns', 'my_download_count_column_sortable' );
    add_action( 'pre_get_posts', 'my_download_count_column_do_sort' );
}


/*
 * Adding Download Count column
 *
 */

function my_download_count_column_register( $columns )
{
    $columns['downloads'] = 'Downloads';

    return $columns;
}

/*
 * Display the columns
 *
 */

function my_download_count_column_display( $column_name, $post_id )
{
    if( 'downloads' != $column_name || wp_attachment_is_image( $post_id ))
        return;

    if (get_post_meta($post_id, 'download_count', true)) {
        echo get_post_meta($post_id, 'download_count', true);
    } else {
        echo '0';
    }
}

/*
 * Registering columns as sortable
 *
 */

function my_download_count_column_sortable( $columns )
{
    $columns['downloads'] = 'download_count';
    return $columns;
}

/*
 * Sort the columns
 *
 */

  function my_download_count_column_do_sort($query)
  {
      global $current_screen;
      if( 'upload' != $current_screen->id )
          return;

      $orderby = $query->get('orderby');

      if ('download_count' == $orderby) {
          $query->set('meta_key', 'download_count');
          $query->set('orderby', 'meta_value_num');
      }
  }

I tried to add a column "Downloads" in the media library and make it sortable. This all works fine, but as soon as I search for a filename next to the sorting, the following error occurs:

WordPress-Database-Error: [Not unique table/alias: 'wp_postmeta']
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
    LEFT JOIN wp_postmeta AS sq1 ON (wp_posts.ID = sq1.post_id
            AND sq1.meta_key = '_wp_attached_file')
WHERE
    1 = 1
    AND(((wp_posts.post_title LIKE '%pdf%')
    OR(wp_postmeta.meta_value LIKE '%pdf%')
    OR(wp_posts.post_excerpt LIKE '%pdf%')
    OR(wp_posts.post_content LIKE '%pdf%')
    OR(sq1.meta_value LIKE '%pdf%')))
    AND(wp_postmeta.meta_key = 'download_count')
    AND wp_posts.post_type = 'attachment'
    AND((wp_posts.post_status = 'inherit'
        OR wp_posts.post_status = 'private'))
GROUP BY
    wp_posts.ID
ORDER BY
    wp_postmeta.meta_value + 0 ASC
LIMIT 0,
20

I know that it is because the tablename is used multiple times. But I don't know how I can avoid this. Can you tell me how I can work around this error? Here you can see how I added the column and made it sortable:

/*
 * Add Sortable Download Count Column to the Media Library
 *
 */

if( is_admin() )
{
    add_filter( 'manage_upload_columns', 'my_download_count_column_register' );
    add_action( 'manage_media_custom_column', 'my_download_count_column_display', 10, 2 );
    add_filter( 'manage_upload_sortable_columns', 'my_download_count_column_sortable' );
    add_action( 'pre_get_posts', 'my_download_count_column_do_sort' );
}


/*
 * Adding Download Count column
 *
 */

function my_download_count_column_register( $columns )
{
    $columns['downloads'] = 'Downloads';

    return $columns;
}

/*
 * Display the columns
 *
 */

function my_download_count_column_display( $column_name, $post_id )
{
    if( 'downloads' != $column_name || wp_attachment_is_image( $post_id ))
        return;

    if (get_post_meta($post_id, 'download_count', true)) {
        echo get_post_meta($post_id, 'download_count', true);
    } else {
        echo '0';
    }
}

/*
 * Registering columns as sortable
 *
 */

function my_download_count_column_sortable( $columns )
{
    $columns['downloads'] = 'download_count';
    return $columns;
}

/*
 * Sort the columns
 *
 */

  function my_download_count_column_do_sort($query)
  {
      global $current_screen;
      if( 'upload' != $current_screen->id )
          return;

      $orderby = $query->get('orderby');

      if ('download_count' == $orderby) {
          $query->set('meta_key', 'download_count');
          $query->set('orderby', 'meta_value_num');
      }
  }

• query
• media-library
• pre-get-posts

1 Answer 1

Okay, I found the problem by debugging the query.

It was a generic LEFT JOIN added with the posts_join filter that has not alias the $wpdb->postmeta table which finally leads to the error.

So the sorting is working now without problems.