Custom MySQL query to list post

Question

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?

Answer 1

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

Answer 2

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