query_post and wpdb returning different results

Question

I am trying to get the woocommerce products by category.. When I query using the wpdb it gets the product correctly which exists in the backend products list. But when I query using the query_posts it gets another set of products that is not listed in the backend.

Why this discrepancy?

     global $post, $wpdb;                   


    $query = "SELECT $wpdb->posts.* FROM $wpdb->posts
    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)
    WHERE $wpdb->posts.post_status = 'publish'
    AND $wpdb->term_taxonomy.taxonomy = 'product_cat'
    AND $wpdb->term_taxonomy.term_id = 1
    $childcond
    ORDER BY post_date DESC"; 

    if ($wpdb->num_rows > 0) {
        foreach ($postresults as $post) { 
              //process product item
        }
    }

using query_post

 query_posts('cat=1&showposts=-1');

Answer 1

First, don't use query_posts. From the Codex:

query_posts() is the easiest, but not preferred or most efficient, way to alter the main query that WordPress uses to display posts. It does this by putting the main query to one side, and replacing it with a new query. To clean up after a call to query_posts, make a call to wp_reset_query(), and the original main query will be restored.

It is strongly recommended that you use the pre_get_posts action instead, and alter the main query by checking is_main_query

Second, your queries are different.

Compare the two. The query generated with those arguments-- 'cat=1&showposts=-1'-- is:

SELECT 
  wp_posts.* 
FROM wp_posts  
INNER JOIN wp_term_relationships ON (
  wp_posts.ID = wp_term_relationships.object_id
) 
WHERE 1=1  
AND ( 
  wp_term_relationships.term_taxonomy_id IN (1) 
) 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC

Your hand-written query has two LEFT JOINs not one INNER JOIN. Your query also has something called $childcond-- don't know what that is. It also has conditions for a particular term_taxonomy.taxonomy and there is no GROUP BY.

Answer 2

You're querying two different taxonomies- in your wpbd query you reference product_cat. in the call to query_posts you reference the default category taxonomy via the cat argument.

as an aside, you should be using WP_Query instead of query_posts.