Retrieve Postmeta in Custom WordPress Query-CodePudding

I am working on a custom query which looks as follows:

<?php
$querystr = "SELECT *, SUM(`total_votes`) AS tv
FROM `toptr_bpvm_summary`,`toptr_posts`
WHERE 1
AND toptr_bpvm_summary.postid = toptr_posts.ID
AND toptr_posts.post_status = 'publish'
AND toptr_bpvm_summary.post_type = 'cars'
AND toptr_bpvm_summary.vote_type=1
AND toptr_bpvm_summary.vote_date BETWEEN now() - interval 1 day
AND now()
GROUP BY `postid`
ORDER BY tv DESC limit 0, 3";
$pageposts = $wpdb->get_results($querystr, OBJECT);
?>
<?php if ($pageposts): ?>
<?php global $post; ?>
<?php foreach ($pageposts as $post): ?>
    <?php setup_postdata($post);?>
      <p ><?php echo $post->tv ?></p>
      <p><a  href="<?php the_permalink(); ?>"></a><?php echo the_title(); ?></p>  
<?php endforeach; ?>

<?php else : ?>
    <h2 >Not Found</h2>
<?php endif; ?>

Basically it's sorting and displaying results based on their like votes from the last 24 hours in a DESC order which is working perfectly fine, see here the output if I do an r_print:

Array ( [0] => stdClass Object ( [postid] => 31 [tv] => 50000000 ) [1] => stdClass Object ( [postid] => 311 [tv] => 8000 ) [2] => stdClass Object ( [postid] => 314 [tv] => 500 ) )

However I am a little bit stuck now as I would like to show additional information such as the post name, permalink etc:

<p><a  href="<?php the_permalink(); ?>"></a><?php echo the_title(); ?></p>

However it's simply not displaying. The only data that actually displays is echo $post->tv

I've tried to modify my query to include toptr_postmeta as follows:

FROM `toptr_bpvm_summary`,`toptr_posts`,`toptr_postmeta`

However after doing that the results are just completely wrong:

Array ( [0] => stdClass Object ( [postid] => 31 [tv] => 101350000000 ) [1] => stdClass Object ( [postid] => 311 [tv] => 16216000 ) [2] => stdClass Object ( [postid] => 314 [tv] => 1013500 )

I am sure there is something wrong in the main query, however I just can't seem to get my head around it. Some expert help would be greatly appreciated, thank you very much in advance for any help, lead or suggestion.

CodePudding user response：

Your main problem is that setup_postdata needs to take a WP_Post object or something WordPress can convert into one, which is either an object with an ID property or just an int. The object you are getting has postid, however, which WordPress knows nothing about.

One option is to try grouping by ID instead of postid which might work.

$querystr = "SELECT *, SUM(`total_votes`) AS tv
FROM `toptr_bpvm_summary`,`toptr_posts`
WHERE 1
AND toptr_bpvm_summary.postid = toptr_posts.ID
AND toptr_posts.post_status = 'publish'
AND toptr_bpvm_summary.post_type = 'cars'
AND toptr_bpvm_summary.vote_type=1
AND toptr_bpvm_summary.vote_date BETWEEN now() - interval 1 day
AND now()
GROUP BY `toptr_posts.ID`
ORDER BY tv DESC limit 0, 3";

The other option is to just call setup_postdata($post->postid). If you do this, however, I'd encourage you to rename $post so that someone in the future (which may or may not be you) understands that it isn't a "real" WP_Post object.

Edit: This might not be 100% correct, but hopefully it gets you close. If not, you can trace setup_postdata to get_post to sanitize_post