sethbeckerman5 Staff asked 4 years ago
WordPress has inbuilt feature to filter out terms based on arguments. But I want custom taxonomy result based on other taxonomy sorted in hierarchy. Is there any mysql query or other way to do it?
1 Answers
Jehal Desai Staff answered 4 years ago
This may help!
with recursive cte (id, taxonomy, parent, `count`, lvl) as (
select term_taxonomy_id,
taxonomy,
parent,
t1.`count`,
IF(t1.parent = 0, term_taxonomy_id, t1.parent) AS lvl
from {$wpdb->prefix}term_taxonomy t1
where taxonomy = ''
union all
select t2.term_taxonomy_id,
t2.taxonomy,
t2.parent,
t2.`count`,
IF(t2.parent > 0, t2.parent, t2.term_taxonomy_id) as lvl
from {$wpdb->prefix}term_taxonomy t2
inner join cte
on t2.parent = cte.id WHERE t2.taxonomy = ''
)
select cte.*, t.name, t.slug from cte
INNER JOIN {$wpdb->prefix}terms t on t.term_id = cte.id
INNER JOIN {$wpdb->prefix}term_relationships tr on tr.term_taxonomy_id = cte.id
LEFT JOIN
(
select ID from {$wpdb->prefix}posts WHERE post_type ='post' AND post_status='publish' AND ID IN (SELECT object_id from {$wpdb->prefix}term_relationships {$any_arguments})) p on tr.object_id = p.ID
WHERE p.ID is not null
GROUP BY t.term_id
ORDER BY lvl, parent $limit;
Create a function in theme or plugin php file
global $wpdb;
$query = 'Above Query'; // change parameters based on your requirement
$result = $wpdb->get_results($query); // Output custom get_terms();