0

I have subcategories(custom taxonomy).I want to fetch all products of that category in one table & then other category name & it's table. To achieve this,I grouped by & ordered by custom taxonomy.Also, I want to pass parent category (custom taxonomy) id to the query. But when I pass these 2 conditions at a time , no data is returned.

/* to order by taxonomy*/
function orderby_tax_clauses( $clauses, $wp_query ) {
global $wpdb;

if ( isset( $wp_query->query['orderby'] ) && 'product_category' == $wp_query->query['orderby'] ) {

    $clauses['join'] .= <<<SQL
    LEFT OUTER JOIN {$wpdb->term_relationships} ON {$wpdb->posts}.ID={$wpdb->term_relationships}.object_id
    LEFT OUTER JOIN {$wpdb->term_taxonomy} USING (term_taxonomy_id)
    LEFT OUTER JOIN {$wpdb->terms} USING (term_id)
    SQL;

    $clauses['where'] .= " AND (taxonomy = 'product_category' OR taxonomy IS NULL)";
    $clauses['groupby'] = "object_id";
    $clauses['orderby']  = "GROUP_CONCAT({$wpdb->terms}.name ORDER BY name ASC) ";
    $clauses['orderby'] .= ( 'ASC' == strtoupper( $wp_query->get('order') ) ) ? 'ASC' : 'DESC';
}

return $clauses;
}

Here is my function to fetch products :

 add_shortcode('product-list','get_product_list');
 function get_product_list($atts)
 {
   extract($atts = shortcode_atts(
     array(
    'cat' => '',
   ),
   $atts
  ));

global $post;
global $wp_query;



$paged = ( get_query_var( 'paged' ) ) ? get_query_var( 'paged' ) : 1;
add_filter( 'posts_clauses', 'orderby_tax_clauses', 10, 2 );
$args = array(
    'post_type' => 'product',
    'posts_per_page' => 50,
            'orderby'        => 'product_category',
            'order'       => 'ASC',
    'paged' => $paged
);


if($atts['cat'] != "") { 
    $args['tax_query' ][] =array(
            'taxonomy' => 'product_category',
            'field' => 'id',
            'terms' => $atts['cat'],
        );
}

$wp_query = new WP_Query( $args );
echo $wp_query->request;
//echo '********'.$wp_query->last_query; 
remove_filter( 'posts_clauses', 'orderby_tax_clauses', 10, 2 ); 

if ( $wp_query->have_posts() ) :
/* my code*/
endif;
}

I debugged & found this query :

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT OUTER JOIN wp_term_relationships ON wp_posts.ID=wp_term_relationships.object_id
LEFT OUTER JOIN wp_term_taxonomy USING (term_taxonomy_id)
LEFT OUTER JOIN wp_terms USING (term_id)
WHERE 1=1
  AND (wp_term_relationships.term_taxonomy_id IN (65))
  AND wp_posts.post_type = 'product'
  AND (wp_posts.post_status = 'publish'
       OR wp_posts.post_status = 'acf-disabled')
  AND (taxonomy = 'product_category'
       OR taxonomy IS NULL)
GROUP BY object_id
ORDER BY GROUP_CONCAT(wp_terms.name
                      ORDER BY name ASC) ASC
LIMIT 0,
      50
  • Generally I find the method you are using most reliable, but I don't remember if I had used this specific combination of arguments with it. Had you tried dumping resulting SQL query and see if it goes wrong somewhere? – Rarst Jul 10 '17 at 8:13
  • I have added query above. – user7193478 Jul 10 '17 at 8:20
0

Here's an example of working tax_query.

The problem I've run into with tax_query is forgetting to nest the array (i.e. 'tax_query' is array(array(...)). If you don't do that, it does not work.

$args = array(
    'post_type' => $post_type,
    'posts_per_page' => -1,  //show all posts
    'tax_query' => array(
         array(
            'taxonomy' => $taxonomy,
             //'field' => 'slug',
             //'terms' => $term->slug,
             'field' => 'term_id',
             'terms' => array([your values]),
          )
     )
 );
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.