Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have the following query:

SELECT
  p.*
FROM
  orders_items oi
  INNER JOIN products pr
    ON pr.`post_id` = oi.`product_id`
  INNER JOIN posts p
    ON p.`ID` = pr.`post_id`
    AND p.`post_type` = 'product'
GROUP BY oi.`product_id`
ORDER BY COUNT(oi.id) DESC;

I want to list the post based on the above query. Can anyone please tell me how can change the following code to incorporate above query?

    $args = array(
        'posts_per_page' => '10',
        'post_type'      => 'product',
        'paged'          => '1'
    );
    global $query_string;
    query_posts($args);

Any help guys?

share|improve this question
add comment

2 Answers

You don't need to use query_posts function. It could be done without it. Use setup_postdata function to set up global post data.

$sql = "
    SELECT
      p.*
    FROM
      orders_items oi
      INNER JOIN products pr
        ON pr.`post_id` = oi.`product_id`
      INNER JOIN posts p
        ON p.`ID` = pr.`post_id`
        AND p.`post_type` = 'product'
    GROUP BY oi.`product_id`
    ORDER BY COUNT(oi.id) DESC
";

echo '<ul>';

$result = $wpdb->get_results( $sql );
foreach ( $result as $post ):
    setup_postdata( $post );
    ?><li>
        <a href="<?php the_permalink() ?>"><?php the_title() ?></a>
    </li><?php 
endforeach;

echo '</ul>';
share|improve this answer
    
Thanks a lot for your solution Eugene Manuilov :) But I have some pagination code from some third party theme which only works with query_posts($args); Can this be done using query_posts($args);? –  Rashy Jan 10 '13 at 9:58
add comment
global $paged;
$temp = $wp_query;
$wp_query = null;

$wp_query = new WP_Query( array('post_type' => 'product','posts_per_page' => 10, 'paged' => $paged ) ); ?>
<?php while (have_posts()) : the_post();

/* Your product content data here */


<?php               
endwhile;
wp_reset_query();
?>
<?php echo paginate_links( $args ) ?>
<?php
global $wp_query;
$big = 999999999; // need an unlikely integer
echo paginate_links( array(
    'base' => str_replace( $big, '%#%', esc_url( get_pagenum_link( $big ) ) ),
    'format' => '?paged=%#%',
    'current' => max( 1, get_query_var('paged') ),
    'total' => $wp_query->max_num_pages,
    'prev_text'    => __('Previous'),
'next_text'    => __('Next')
) );
?> 

i think this will solve ur problem. if u face any problem let me know
share|improve this answer
    
Thanks Arvind but this will list all the products ordered by ID. But, I want to list best sellers which will be fetched from orders table. –  Rashy Jan 10 '13 at 9:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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