query products by category

Hello,

I'm having a strange query issue trying to retrieve products by category in my mp_category template.

The store in question is selling music, so the top level are the 'actual' categories, the sub-categories are the 'albums' and the products are the individual tracks that the customers can purchase.

The problem is when I'm querying the products by subcategory.

So an individual mp_category.php page displays like so:

/////////////////////////////////////////////////////////////////////////
Page header:
- top-level category title
- top-level category description

- sub-category title
- product 1 | product 2 | product 3 | product 4 | product 5

- sub-category title
- product 1 | product 2 | product 3 | product 4 | product 5

- sub-category title
- product 1 | product 2 | product 3 | product 4 | product 5

- sub-category title
- product 1 | product 2 | product 3 | product 4 | product 5

Page footer
/////////////////////////////////////////////////////////////////////////

So basically for a specific category page, I need to have a top level loop that goes through any of the sub-categories, and then another sub-loop that displays ONLY the products within that sub-category.

I've found some wp snippets to do sort of what I want in a normal archive template, but none of this seems to work with custom post types and custom taxonomies. It doesn't seem like it should be that difficult to do, but this is has been an ongoing nightmare for a week or so and I don't seem to be having any luck.

What I have so far in my mp_category template is basically this:

<?php $cat = get_the_category();
$cat_name = single_cat_title('', false);

//for a given post type, return all sub posts
$post_type = 'products';
$tax = 'product_category';

// get categories that are a child of our parent category
$tax_terms = get_terms($tax, 'child_of='. $cat);

// loop within the subcategory to display the individual products
?>

I've tried what seems like a dozen variations of this, but I can't seem to get wordpress to return me a list of terms that are children of the main product_category terms. I have no idea why this is so difficult, but if anyone has any ideas what I might be doing wrong, it would be greatly appreciated.

Thanx in advance,
Mike W

  • gekido

    this is getting ridiculous. ugh.

    So figured out the sub-category wierdness.

    What I was trying to do is use the get_query_var function to determine the current category id / info on the mp_category page, which didn't work (was empty).

    So, instead, I am using the following:

    // get information about this taxonomy term for later
    $term = get_term_by( 'slug', get_query_var( 'term' ), get_query_var( 'taxonomy' ) );

    Which gives me the information I need to cycle through the various sub-categories no problem. Great, figured that one out.

    This is being done like so:

    $subcategories = get_terms('product_category', 'parent='.$term->term_id);

    if(empty($subcategories))
    {
    $thiscat = get_term(get_query_var('cat'),'product_category');
    $subcategories = get_terms('product_category', 'parent='.$thiscat->parent.'');
    }
    if(empty($subcategories))
    $subcategories = array();

    if(!empty($subcategories))
    {
    // ok we have subcategories, let's loop through them
    foreach($subcategories as $subcat)
    {

    ....

    Next, from within the subcategory loop, I now need to do a simple query for the products in that sub-category, but NOOO, that's not so simple.

    I don't get why this is being such a pain. Ugh.

    So here's my sub-query for products within the category:

    $args=array(
    'product_category' => $subcat->name,
    'post_type' => 'products',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'orderby' => 'title',
    'order' => 'ASC',
    );
    $my_query = null;
    $my_query = new WP_Query($args);
    if( $my_query->have_posts() ): while ($my_query->have_posts()) : $my_query->the_post();

    But the above returns zero posts. I know that $subcat->name is valid & correct, I've tried doing name, slug, id but nothing returns the posts in that sub-category.

    Can anyone see why the above would NOT give me a list of products within a specific category?

    This is pretty much copy & pasted straight from the Codex, but I just cannot for the life of me get wordpress to return the list of products in a category.

    Anyone?

  • gekido

    yeah it's wierd - i am outputting the category name, term ID & slug in an echo right above and they are displaying fine. I just don't get it - this isn't a fancy query at all.

    The page in question is here:
    http://musiquecorazon.com/store/products/category/rock-n-reality/

    Each of the sub-categories that are listed have 5 products in them, I've double-checked the product's, their categories etc - but they just don't want to display.

    Commenting out the taxonomy (or even the post type) still doesn't display anything. I am lost for a reason why. Very strange.

    Here is the full mp_category.php file i'm using:

    <?php get_header(); ?>
    <!--// rendering mp_category.php -->
    	<div style=&quot;min-height: 772px;&quot; id=&quot;main&quot; class=&quot;clearfix zIndex fadein-content&quot;>
    		<div style=&quot;display: block;&quot; id=&quot;primary&quot;>
    			<div id=&quot;content&quot; role=&quot;main&quot;>
    				<div class=&quot;page hentry&quot;>
    					<?php $cat = get_the_category();
    						$cat_name = single_cat_title('', false); 
    
    						// get information about this taxonomy term for later
    						$term = get_term_by( 'slug', get_query_var( 'term' ), get_query_var( 'taxonomy' ) );
    						//print_r($term);
    						?>
    					<div class=&quot;entry-header&quot;>
    						<?php
    							$args = array(
    								'attr' => 'class=alignleft'
    								);
    							print apply_filters( 'taxonomy-images-queried-term-image', '', $args );
    							?>
    						<h2 class=&quot;page-description&quot;> <?php single_cat_title( ); ?> </h2>
    						<p> <?php echo category_description( $cat ); ?> </p>
    					</div><!-- .entry-header -->
    					<div class=&quot;clearfix&quot;> </div>
    					<div class=&quot;entry-content&quot;>
    						<?php
    						$subcategories = get_terms('product_category', 'parent='.$term->term_id);
    
    						if(empty($subcategories))
    						{
    							$thiscat = get_term(get_query_var('cat'),'product_category');
    							$subcategories = get_terms('product_category', 'parent='.$thiscat->parent.'');
    						}
    						if(empty($subcategories))
    							$subcategories = array();
    
    						// ok we have subcategories, let's loop through them
    						if(!empty($subcategories))
    						{
    							foreach($subcategories as $subcat)
    							{
    								// show our sub-category heading
    								?>
    								<div class=&quot;clearfix&quot;> </div>
    								<div id=&quot;mp_product_list&quot;>
    									<h2 style=&quot;padding-top: 10px;&quot;><?php echo $subcat->name; ?></h2>
    									<?php
    									echo 'querying sub-category<br>
    									name: '.$subcat->name. ' term_id: ' .$subcat->term_id .'  slug: '.$subcat->slug .'<br>';
    									$args=array(
    										'product_category' => $subcat->term_id,
    										'post_type' => 'products',
    										'post_status' => 'publish',
    										'posts_per_page' => -1,
    										'orderby' => 'title',
    										'order' => 'ASC',
    									);
    									//$my_query = null;
    									$my_query = new WP_Query($args);
    									if( $my_query->have_posts() )
    									{
    										echo ' we have posts! ';
    										while ($my_query->have_posts()) : $my_query->the_post();
    											echo'rendering post...';
    										?>
    											<span style=&quot;float:left; width: 100px; margin: 5px&quot;>
    												<?php
    													$product_content = gekido_product_image( false, 'list', $post->ID );
    													$product_content .= $mp->product_excerpt($post->post_excerpt, $post->post_content, $post->ID);
    													print apply_filters( 'mp_product_list_content', $product_content, $post->ID );
    												?>
    											</span>
    											<?php
    										endwhile;
    									}
    									wp_reset_query();
    									?>
    									<div class=&quot;clearfix&quot;> </div>
    								</div><?php
    							}
    						}
    
    						//	echo '<pre>' . print_r($tax_terms) . '</pre>';
    
    							/*$count = 1;
    							if ($tax_terms)
    							{
    								foreach ($tax_terms as $tax_term)
    								{
    									// only loop if we're in a sub-category
    									if ($tax_term->name != $cat_name)
    									{
    										$args=array(
    											'product_category' => $tax_term->term_id,
    											'post_type' => 'products',
    											'post_status' => 'publish',
    											'posts_per_page' => -1,
    											'orderby' => 'title',
    											'order' => 'ASC',
    											); // END $args
    										$my_query = null;
    										$my_query = new WP_Query($args);
    
    										if( $my_query->have_posts() )
    										{
    											?>
    											<div class=&quot;clearfix&quot;> </div>
    											<div id=&quot;mp_product_list&quot;><h2 style=&quot;padding-top: 10px;&quot;><?php echo $tax_term->name; ?></h2>
    											<?php
    											while ($my_query->have_posts()) : $my_query->the_post();
    												?>
    												<span style=&quot;float:left; width: 100px; margin: 5px&quot;>
    													<?php
    														$product_content = gekido_product_image( false, 'list', $post->ID );
    														$product_content .= $mp->product_excerpt($post->post_excerpt, $post->post_content, $post->ID);
    														print apply_filters( 'mp_product_list_content', $product_content, $post->ID );
    													?>
    												</span>
    												<?php
    											endwhile;
    										} // END if have_posts loop
    										wp_reset_query();
    										?>
    											<div class=&quot;clearfix&quot;> </div>
    										</div><?php
    									}
    									else
    									{
    										echo '
    <!-- Skipping category '. $count.' name: '.$tax_term->name .' -->';
    									}
    									$count ++;
    								} // END foreach $tax_terms
    							} // END if $tax_terms
    							*/
    						?>
    						</div><!-- end of product list -->
    
    						<div class=&quot;clearfix&quot;> </div>
    
    					</div><!-- .entry-content -->
    				</div>
    			</div><!-- #content -->
    		</div><!-- #primary -->
    
    	</div><!-- #main -->
    
    <?php get_footer(); ?>

    And I've posted screenshots of both the output and the admin page showing how many products are in each category. It's absolutely bizarre. I've done quite a bit of work with custom post types & taxonomies in the past, but have never seen something so strange.

  • gekido

    One thing that may or may not be related (probably not) is that in the admin area if you click on any of the links to the posts in a specific category, it doesn't properly filter the resulting list of products - all products are displayed.

    Normally, this would filter the list of posts and only show the ones in that specific category.

    Not sure if this might be an issue with how the custom post type and/or taxonomy was registered or what?

  • gekido

    yeah that was a typo - thanx for pointing that out.

    so trying what seems like endless variations of the query - this one:

    query_posts(
        array (
        'post_type' => 'product',
        'showposts' => -1,
        'tax_query' => array(
    	'taxonomy' => 'product_category',
    	'field' => 'slug',
    	'terms' => $subcat->slug //category slug
        )
    ));
    while ( have_posts() ) : the_post();

    FINALLY displays posts, but instead of filtering posts by that specific sub-category (by slug), it displays ALL of the products.

    Frankly I think that wordpress's custom taxonomy querying is fubared, but maybe that's just me.

    The fact that different ways of doing the same query result in vastly different results is very strange.

    And note that doing the above query using field => 'id' results in the same thing: every product gets displayed, not just the sub-category that is being filtered, as shown below:

    query_posts(
    array (
    'post_type' => 'product',
    'showposts' => -1,
    'tax_query' => array(
    'taxonomy' => 'product_category',
    'field' => 'id',
    'terms' => $subcat->term_id //category slug
    )
    )
    );
    while ( have_posts() ) : the_post();

  • gekido

    Tried the same thing with a raw custom query, like so:

    <?php
    $querystr = "
    	SELECT * FROM $wpdb->posts
    	LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
    	LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    	LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    	LEFT JOIN $wpdb->terms ON($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id)
    	WHERE $wpdb->terms.name = $subcat->name
    	AND $wpdb->term_taxonomy.taxonomy = 'product_category'
    	AND $wpdb->posts.post_status = 'publish'
    	AND $wpdb->posts.post_type = 'product'
    	AND $wpdb->postmeta.meta_key = 'order'
    	ORDER BY $wpdb->postmeta.meta_value ASC
    ";
    
    $pageposts = $wpdb->get_results($querystr, OBJECT);
    ?>

    And...same result as the original - no products returned. Not 100% sure about the above syntax mind you - that's pretty much copy/pasted from the wordpress codex with changes for the taxonomy & post_type.

    Just dunno...

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.