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');
}
}
Share
Improve this question
edited Nov 2, 2020 at 19:59
emjay
asked Nov 2, 2020 at 15:15
emjayemjay
1235 bronze badges
1 Answer
Reset to default 0Okay, 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.