WordPress | Sort posts based on the children of the custom taxonomy term

Question

i have a cpt: pro

Custom taxonomy: servizi_pro

where pro they are the professionals

and servizi_pro are the services they can do to customers.

Example:

John Dean

servizi_pro
|
|_ Sito Web (Website)
   |
   |_1600$

Going to see what happens in the database of the wordpress tables, I find this situation.

posts.ID: 185

table: term_relationships

object_id  |  term_taxonomy_id

185             22
185             41

table: term_taxonomy

term_taxonomy_id    |    term_id        |    taxonomy        |     parent

      22                    22              servizi_pro              0
      41                    41              servizi_pro              22  

table terms:

term_id          |          name

22                          Sito Web
41                           1600

this is the structure inside my db for a professional, who has as services the creation of a website at a cost of 1600$

the structure shown above is only for a professional with ID 185, now making an example to 2 Professionals I report the same table including all the data

posts.ID: 185

posts.ID: 331

table: term_relationships

object_id  |  term_taxonomy_id

185             22
185             41
331             22
331             43

table: term_taxonomy

term_taxonomy_id    |    term_id        |    taxonomy        |     parent

      22                    22              servizi_pro              0
      41                    41              servizi_pro              22  
      43                    43              servizi_pro              22

table terms:

term_id          |          name

22                          Sito Web
41                           1600
43                           900

now that I have provided the data, I write the query I have in my code:

$qprice = $wpdb->get_results(
        $wpdb->prepare(" SELECT *
                         FROM {$wpdb->prefix}term_relationships 
                                             
                         LEFT JOIN {$wpdb->prefix}posts
                         ON {$wpdb->prefix}term_relationships.object_id = {$wpdb->prefix}posts.ID
                         
                         LEFT JOIN {$wpdb->prefix}term_taxonomy
                         ON {$wpdb->prefix}term_relationships.term_taxonomy_id = {$wpdb->prefix}term_taxonomy.term_id
                                             
                         LEFT JOIN {$wpdb->prefix}terms 
                         ON {$wpdb->prefix}term_relationships.term_taxonomy_id =  {$wpdb->prefix}terms.term_id 
                                             
                         WHERE {$wpdb->prefix}terms.name = '$art_p'
                         AND {$wpdb->prefix}posts.post_type = 'pro'
 
                         GROUP BY {$wpdb->prefix}posts.ID
                         ORDER BY  {$wpdb->prefix}terms.name DESC
                     
                     ")
    );

where $art_p is the service that the user requires in this case: Sito Web (Website).

how do i sort posts in ascending or descending order with respect to children then 1600 and 900.

Because the final case is it shows the prices of the professionals who are able to make the $ art_p service from the most expensive to the least expensive or vice versa by implementing the opposite.

But I can’t get the children sorted with my query

0
user14608849 2 months 0 Answers 10 views 0

Leave an answer