0

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?

0

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>';
  • 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
0
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
  • 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

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.