Each post is showing twice in my custom query...?

Question

I've created a custom query to pull all posts from a custom post type, ordered by comment count. It's your run-of-the-mills custom query:

 <?php

 $querystr = "
    SELECT wposts.* 
    FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
    WHERE wposts.ID = wpostmeta.post_id 
    AND wposts.post_status = 'publish' 
    AND wposts.post_type = 'Tutorials' 
    ORDER BY wposts.comment_count DESC
 ";
 $tutorialposts = $wpdb->get_results($querystr, OBJECT);

 ?>

 <?php if ($tutorialposts): ?>
 <?php global $post; ?>
 <?php $i = 0; ?> 
 <?php foreach ($tutorialposts as $post): ?>
 <?php $i++; ?>
 <?php setup_postdata($post); ?>
<h2><?php echo $i ?>:&nbsp;<a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title(); ?>"> <?php the_title(); ?></a></h2>
 <?php endforeach; ?>
 <?php endif; ?>

It has a little code added to it to number the posts. But I took this code out and the same problem persisted.

It actually does what it's supposed to do! In fact, it's so excited about doing what it's supposed to do, that it does it twice.

Also if anyone could tell me how to parse this code so it only shows the top 10 posts retrieved by the query that would be excellent. But I could probably figure that out on my own, that's just me being kind of lazy.

I really just need to know why it's pulling each post and displaying it twice o.0

Answer 1

There is really no reason to use raw sql to query posts. You can accomplish just about any type of query using the WordPress API. For full reference see the codex, Function Reference query posts. Try the following:

global $post;

    $args=array(
    'post_type' => 'tutorial',
    'orderby' => 'comment_count',
    'posts_per_page' => 10
    );

   $posts= query_posts($args);

foreach ($posts as $post) :

//do stuff

endofforeach;

Answer 2

Why are you joining with postmeta if you don't use it neither in WHERE nor get a field from it?

Your double result is because you're running a non-distinct query and since you're joining postmeta, MySQL delivers a separate record for each postmeta record, even though you're only retrieving the fields from posts.

So either drop the postmeta join or do SELECT DISTINCT, the former would be better.

And i agree with Chris_O...always better to use query vars if you can, as WP then makes sure you don't cause yourself this kind of trouble.

Answer 3

I had to define some post meta field (custom field, taxonomy, or tag) and add it to the query select:

AND wpostmeta.meta_key = 'Platform'

Now it only shows each post once, but I am now locked in to defining a value to the custom field "platform" for every post under "Tutorials."