database - Combining two select posts from mysql - to get least viewed posts

admin管理员组

文章数量:1122832

I want to display least viewed posts from all categories except one

1- with this code I can display least viewed posts from all categories :

function NotSoPop (){
 global $wpdb;
 $return = "<ul>";
 $tops = $wpdb->get_results( "SELECT * FROM wp_popularpostsdata, wp_posts WHERE wp_popularpostsdata.postid = $wpdb->posts.ID AND $wpdb->posts.post_type = 'post' ORDER BY pageviews ASC limit 10");
 foreach ( $tops as $top ) {
 $return .= '<li><a href="'.get_permalink($top->postid).'">' . get_the_title($top->postid) .'</a> - '. $top->pageviews.'</li>';
 }
 $return .= "</ul>";
 return $return;
}
add_shortcode ("notpop", "NotSoPop");

Note : wp_popularpostsdata is a table created by popular posts plugin

2- and with this code I can select and list all posts except posts that belongs to category with id=73

$query ="
SELECT *
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.term_id NOT IN (73)
GROUP BY wp_posts.ID limit 2";
/---- FOR DISPLAY RESULT -----/
$resultfirst = $wpdb->get_results($query);
foreach( $resultfirst as $result ){
echo $result->post_title;
echo ' - ';
}

is there a way to combine this two code snippet? to get least viewed posts from all categories except category with id=73

I want to display least viewed posts from all categories except one

1- with this code I can display least viewed posts from all categories :

function NotSoPop (){
 global $wpdb;
 $return = "<ul>";
 $tops = $wpdb->get_results( "SELECT * FROM wp_popularpostsdata, wp_posts WHERE wp_popularpostsdata.postid = $wpdb->posts.ID AND $wpdb->posts.post_type = 'post' ORDER BY pageviews ASC limit 10");
 foreach ( $tops as $top ) {
 $return .= '<li><a href="'.get_permalink($top->postid).'">' . get_the_title($top->postid) .'</a> - '. $top->pageviews.'</li>';
 }
 $return .= "</ul>";
 return $return;
}
add_shortcode ("notpop", "NotSoPop");

Note : wp_popularpostsdata is a table created by popular posts plugin

2- and with this code I can select and list all posts except posts that belongs to category with id=73

$query ="
SELECT *
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.term_id NOT IN (73)
GROUP BY wp_posts.ID limit 2";
/---- FOR DISPLAY RESULT -----/
$resultfirst = $wpdb->get_results($query);
foreach( $resultfirst as $result ){
echo $result->post_title;
echo ' - ';
}

is there a way to combine this two code snippet? to get least viewed posts from all categories except category with id=73

• database
• select
• popular-posts

Share Improve this question edited Jul 4, 2024 at 20:35 ehsan asked Jul 4, 2024 at 20:33 ehsanehsan 1333 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

This should be the SQL you're looking for (off the top of my head, so may need some tweaking).

"SELECT * FROM wp_posts
LEFT JOIN wp_popularpostsdata ON (wp_posts.ID =  wp_popularpostsdata.postid)
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE posts.post_type = 'post' 
AND wp_term_taxonomy.term_id NOT IN (73)
GROUP BY wp_posts.ID 
ORDER BY pageviews ASC limit 10"

本文标签： databaseCombining two select posts from mysqlto get least viewed posts