Get the list of custom taxonomy terms by count which have custom post

In my WP v5.9.3, I have custom post type "song" and a shared custom taxonomy "genre".

On the Songs archive page, I want to list all the terms of "genre" taxonomy with count, which have posts in "song" post type.

With get_terms I was able to get all the terms from "genre", but cannot filter terms which has "song" posts.

Though I am not expert at SQL Query, I have tried below but not able to get the list:

    $SQLquery = "SELECT wp_terms.* COUNT(*)from wp_terms
                 SELECT wp_posts.ID FROM wp_posts
                 INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
                 INNER JOIN wp_term_relationships ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
                 INNER JOIN wp_posts ON wp_posts.ID = wp_term_relationships.object_id
                 WHERE 1=1 
                 AND wp_posts.post_type IN ('song')
                 AND wp_posts.post_status = 'publish'
                 AND wp_term_taxonomy.taxonomy = 'genre'
                 GROUP BY wp_terms.term_id
                 ORDER BY wp_terms.term_id DESC";

    $SQLqueryResult = $wpdb->get_results($SQLquery, ARRAY_A);

    $SQL_term_ids = wp_list_pluck($SQLqueryResult, 'term_id');

How can I make the above SQL query work to get the terms list or is there any simple / built-in WordPress function? Thank you.

Update 1

Issue was with multiple SELECT in SQLquery, when removed SELECT wp_posts.ID FROM wp_posts the code worked as required.

CodePudding user response：

There is an issue concerning the begin of your query. You can't use two selects like this. When you want to select multiple columns, you need to use comma, meaning "select table1.column1, table2.column1...from yourselection."