Ask your WordPress questions! Pay money and get answers fast! Comodo Trusted Site Seal
Official PayPal Seal

Custom Taxonomy Listing Help WordPress

  • SOLVED

I have setup two custom taxonomies for a custom post type that I have created. The post type is called beer, and the two taxonomies are country and brewers.

I would like to list them like this.

Country
-->Brewers
----->Beers


I can pull the countries using this code.

$terms_country = get_terms('country');
foreach ($terms_country as $term_country) {
echo "<h3 class=\"country-heading\" id=\"".$term_country->slug."\">";
echo '<a href="/beers/country/' . $term_country->name . '">' . $term_country->name . '</a>';
echo "</h3>";


I need to query the country term to list the Brewers that have that taxonomy attached to the post as well. I would then like to list the post itself.

Answers (1)

2012-11-13

Dbranes answers:

Hi, here is one idea to use only a single db query:


$sql=" SELECT
{$wpdb->term_relationships}.object_id as post_id,
{$wpdb->posts}.post_title as beer_name,
{$wpdb->terms}.name as term_name,
{$wpdb->term_taxonomy}.term_id as term_id,
{$wpdb->term_taxonomy}.taxonomy,
{$wpdb->terms}.slug as term_slug
FROM {$wpdb->terms}
LEFT JOIN {$wpdb->term_taxonomy}
ON {$wpdb->term_taxonomy}.term_id = {$wpdb->terms}.term_id
LEFT JOIN {$wpdb->term_relationships}
ON {$wpdb->term_taxonomy}.term_taxonomy_id = {$wpdb->term_relationships}.term_taxonomy_id
LEFT JOIN tfl_posts
ON {$wpdb->term_relationships}.object_id = {$wpdb->posts}.id
WHERE {$wpdb->term_taxonomy}.taxonomy in ( 'country','brewers')
AND {$wpdb->posts}.post_status ='publish'";

$data =$wpdb->get_results($sql,ARRAY_A);

$beers_per_country=array();
$brewers_per_beer=array();
$beers_per_brewer=array();

foreach($data as $d){
if($d['taxonomy']=="country"){
$beers_per_country[$d['term_name']][]=array("post_id"=>$d['post_id'],"country_id"=>$d['term_id'],"country_name"=>$d['term_name']);
}elseif($d['taxonomy']=="brewers"){
$beers_per_brewer[$d['term_name']][]=array("post_id"=>$d['post_id'],"beer_name"=>$d['beer_name']);
$brewers_per_beer[$d['post_id']][]=array("brewer_name"=>$d['term_name'],"brewer_id"=>$d['term_id'],"brewer_slug"=>$d['term_slug']);
}
}

echo "<pre>";

$a=array();
$b=array();
$c=array();

echo "<ul>";

foreach($beers_per_country as $country=>$beers){
echo "<li>".$country."<ul>";
foreach($beers as $beer){
foreach($brewers_per_beer[$beer['post_id']] as $brewer){
$a[]=$brewer['brewer_name'];
foreach($beers_per_brewer[$brewer['brewer_name']] as $item){
$b[$brewer['brewer_name']][]=$item['beer_name'];
}
}
}
$a=array_unique($a);
foreach($a as $brewer_name){
echo "<li>".$brewer_name."<ul>";
$c=array_unique($b[$brewer_name]);
foreach($c as $item){
echo "<li>".$item."</li>";
}
echo "</ul></li>";
}
echo "</ul></li>";
}
echo "</ul>";
echo "</pre>";



Drew Clardy comments:

I have added this in, and it is just giving a list of the countries with no brewers.


Drew Clardy comments:

Adding an image to show the results of the page.