mysql - SQL Sort By Related Table's Count In WordPress Ajax

Question

I want to sort search results by count of the taxonomy's items. Normally it sorts as names of same table. I wrote a code but it's not working.

Taxonomies are in wp_terms Taxonomy items count is in wp_terms_relationships table.

Original SQL (sorts by name):

SELECT * FROM  $wpdb->terms 
         LEFT JOIN $wpdb->term_taxonomy ON {$wpdb->terms}.`term_id` = {$wpdb->term_taxonomy}.term_id 

         WHERE 
            {$wpdb->term_taxonomy}.`taxonomy` $sql_tax
            AND  {$wpdb->terms}.`name` LIKE %s 
        ORDER BY  {$wpdb->terms}.`name` ASC

My SQL to sort by item count of taxonomy:

SELECT * FROM  $wpdb->terms 
         LEFT JOIN $wpdb->term_taxonomy ON {$wpdb->terms}.`term_id` = {$wpdb->term_taxonomy}.term_id 
         RIGHT JOIN (SELECT COUNT(*) as count FROM $wpdb->term_relationships where term_taxonomy_id={$wpdb->term_taxonomy}.term_id) p ON p.term_taxonomy_id={$wpdb->terms}.`term_id`)
         WHERE 
            {$wpdb->term_taxonomy}.`taxonomy` $sql_tax
            AND  {$wpdb->terms}.`name` LIKE %s 
        ORDER BY  {$wpdb->terms}.`count` ASC

question from:https://stackoverflow.com/questions/65832549/sql-sort-by-related-tables-count-in-wordpress-ajax

Answer 1

Waitting for answers