query posty by meta value (counterize hack) WordPress
Hi dear community!
I want to get some posts and order it by a custom meta key. This is what i tried before.. but it didnt worked..
<?php $result = $wpdb->get_results("SELECT comment_count,ID,post_title FROM $wpdb->posts ORDER BY comment_count DESC LIMIT 0 , 5");
foreach ($result as $post) {
setup_postdata($post);
$postid = $post->ID;
$title = $post->post_title;
$sql = "SELECT SUM( `count` ) "
. " FROM `" . counterize_pageTable() . "` "
. " WHERE `postID` = $post->ID";
$result = $wpdb->get_var( $wpdb->prepare( $sql, $thePostID ) );
$commentcount = $result;
if ($commentcount != 0) { ?>
<li><a href="<?php echo get_permalink($postid); ?>" title="<?php echo $title ?>">
<?php echo $title ?></a> {<?php echo $commentcount ?>}</li>
<?php } } ?>
i want to use the code in my archive page - there my code-blocks looks like this:
<?php $i = 1; echo '<div class="post-row2">'; query_posts('cat=' . $cat_id = get_query_var('cat') .'&showposts=6&offset=6'); while ( have_posts() ): the_post(); ?>
And set this as the meta key. I use the code below to get the amount of views from the counterize plugin. This will be the meta key.
$sql = "SELECT SUM( `count` ) "
. " FROM `" . counterize_pageTable() . "` "
. " WHERE `postID` = $post->ID";
$result = $wpdb->get_var( $wpdb->prepare( $sql, $thePostID ) );
$commentcount = $result;
Many thanks in advance for any help!
<strong>EDIT:</strong> I found a code looks like it could work..
http://wpquestions.com/question/showChronoLoggedIn/id/8391
add_filter( 'pre_get_posts', 'orderby_comment_count' );
function orderby_comment_count( $query ) {
$query->set( 'orderby', 'comment_count' );
return $query;
}
does it make sense to combine it with this?
$sql = "SELECT SUM( `count` ) "
. " FROM `" . counterize_pageTable() . "` "
. " WHERE `postID` = $post->ID";
$result = $wpdb->get_var( $wpdb->prepare( $sql, $thePostID ) );
$commentcount = $result;
I think to update the post meta and set orderby post meta is the way to solve it.. hope 4 Help!
Answers (2)
Andrea P answers:
what is the name of the custom meta?
what's the name of the post_type?
what do you want to show for each post
anyway, this is waht you should do (changing the placeholders with your relevant names)
<?php
$args = array(
'post_type' => 'POST_TYPE_NAME',
'order' => 'ASC',
'meta_key' => 'CUSTOM_META_NAME',
'orderby' => 'meta_value', //or 'meta_value_num' if the value is numeric
);
$custom_query = new WP_Query ( $args );
if ( $custom_query->have_posts() ):
while ( $custom_query->have_posts() ) : $custom_query->the_post();
?>
<li><a href="<?php the_permalink(); ?>" title="<?php the_title(); ?>">
<?php the_title() ?></a> {<?php comments_number( 'no responses', 'one response', '% responses' ); ?>.}</li>
<?php endwhile;
endif; ?>
Patrick comments:
thanks for your answer, Andrea : )
the post type is simply "post"
the custom meta should be the value from counterize.. "$commentcount" - it returns simply the number of views.
$sql = "SELECT SUM( `count` ) "
. " FROM `" . counterize_pageTable() . "` "
. " WHERE `postID` = $post->ID";
$result = $wpdb->get_var( $wpdb->prepare( $sql, $thePostID ) );
$commentcount = $result;
Andrea P comments:
hold on. I might have misunderstood.
the "custom meta" must be a specific cell in the post_meta table. I mean that it must exists as a specific value, and not be a sum of values that you calculate onfly when looping the posts.
so by instance, you might have a custom field in the user-meta, which stores their age. so it's a custom field, filled with a single number.
then you can tell the WP_Query to order the posts by that value.
is this "counterize" a specific custom meta field? which is its name?
the variable is called "commentcount". what is exactly this counterize? is it just the number of comments which the post has got?
Patrick comments:
Counterize is a plugin for stats - its counting the clicks of evry page/post. (its a better solution than the comment count) It works fine to query posts and display the count of views (like in my first code). I think a workaround could be to query all posts of a category and set the $commentcount as meta-data for evry post. In the next query it could be ordered by it..
Andrea P comments:
I am sorry but I never used that plugin. and if it's the one I found on google, it looks like it's outdated, so I wouldn't recommend to use it.
in any case, if it's not saving the total-visits as a specific custom meta field within each post meta_table (and it rather calculate it on fly using a function to sum up the hits). then the total vists can't be used in a wp_query, as technically it's not a meta_field.
the situation might require to use custom sql queries, but I'd need first to learn where exactly the plugin stores the values, etc..
this will also probably require some further discussion with you to better understand the requirements and the current setup.
I am sorry but for $5 I cannot spend more time on this issue.
hopefully someone else will start from what we've discussed now and carry on.
Best of luck
Patrick comments:
I understand : / i`m sorry - i didnt thought its that complicated..
i found some simular issues - maybe there is allready a solution..
http://wpquestions.com/question/showChrono/id/8912
http://wordpress.stackexchange.com/questions/90652/wp-query-sort-by-php-variable
Rempty answers:
You need custom query like this to order posts by count
"SELECT SUM(count) as c, ID FROM ".counterize_pageTable().", ".$wpdb->posts." WHERE ".$wpdb->posts.".ID=".counterize_pageTable().".postID AND ".$wpdb->posts.".post_status LIKE 'publish' AND ".$wpdb->posts.".post_type LIKE 'post' GROUP by postID ORDER by c DESC"
Rempty comments:
You need a custom query like this
"SELECT SUM(count) as c, ID FROM " . counterize_pageTable() . ", ".$wpdb->posts." WHERE ".$wpdb->posts.".ID=" . counterize_pageTable() . ".postID AND ".$wpdb->posts.".post_status = 'publish' AND ".$wpdb->posts.".post_type = 'post' GROUP by ID ORDER by c DESC"
Patrick comments:
thanks for your answer, Rempty! : )
this looks fine, but i doesnt work.. it there a bug in my code?
<?php $result = $wpdb->get_results("SELECT SUM(count) as c, ID FROM ".counterize_pageTable().", ".$wpdb->posts." WHERE ".$wpdb->posts.".ID=".counterize_pageTable().".postID AND ".$wpdb->posts.".post_status LIKE 'publish' AND ".$wpdb->posts.".post_type LIKE 'post' GROUP by postID ORDER by c DESC");
foreach ($result as $post) {
setup_postdata($post);
$postid = $post->ID;
$title = $post->post_title;
$sql = "SELECT SUM( `count` ) "
. " FROM `" . counterize_pageTable() . "` "
. " WHERE `postID` = $post->ID";
$result = $wpdb->get_var( $wpdb->prepare( $sql, $thePostID ) );
$commentcount = $result;
<?php } } ?>
Rempty comments:
<?php $result = $wpdb->get_results("SELECT SUM(count) as c, ID,post_title FROM ".counterize_pageTable().", ".$wpdb->posts." WHERE ".$wpdb->posts.".ID=".counterize_pageTable().".postID AND ".$wpdb->posts.".post_status LIKE 'publish' AND ".$wpdb->posts.".post_type LIKE 'post' GROUP by postID ORDER by c DESC");
foreach ($result as $post) {
setup_postdata($post);
$postid = $post->ID;
$title = $post->post_title;
$commentcount = $post->c;
<?php } ?>
Try echo
echo $postid = $post->ID;
echo $title = $post->post_title;
echo $commentcount = $post->c;
Patrick comments:
If looks fine Rempty, many thanks at this point! But something is wrong.. i get a kind of "white page error" in this area and below..
Rempty comments:
In your wp_config.php change wp debug to true and find the error
Patrick comments:
is it showing the error line, even if it is in a "box"? - its not complete white-page..
Patrick comments:
-.- the error must be anyway somewhere there .. "<?php $result = $wpdb->get_results("SELECT SUM(count) as c, ID,post_title FROM ".counterize_pageTable().", ".$wpdb->posts." WHERE ".$wpdb->posts.".ID=".counterize_pageTable().".postID AND ".$wpdb->posts.".post_status LIKE 'publish' AND ".$wpdb->posts.".post_type LIKE 'post' GROUP by postID ORDER by c DESC"); "
Rempty comments:
USe this new query
$result = $wpdb->get_results("SELECT SUM(count) as c, ".$wpdb->posts.".* FROM ".counterize_pageTable().", ".$wpdb->posts." WHERE ".$wpdb->posts.".ID=".counterize_pageTable().".postID AND ".$wpdb->posts.".post_status LIKE 'publish' AND ".$wpdb->posts.".post_type LIKE 'post' GROUP by postID ORDER by c DESC");
foreach ($result as $p) {
echo $postid = $p->ID;
echo $title = $p->post_title;
echo $commentcount = $p->c;
}
But replace ".counterize_pageTable()." with the name of the counterize table.
Patrick comments:
i think ".counterize_pageTable()." is the name of the table.. it worked in this part.
$sql = "SELECT SUM( `count` ) "
. " FROM `" . counterize_pageTable() . "` "
. " WHERE `postID` = $post->ID";
$result = $wpdb->get_var( $wpdb->prepare( $sql, $thePostID ) );
$commentcount = $result;
the query you`ve written is not working for me : /
Patrick comments:
Hi Rempty! It worked :D like this..
thank you so much!
<?php $result = $wpdb->get_results("SELECT SUM(count) as c, ID,post_title FROM ".counterize_pageTable().", ".$wpdb->posts." WHERE ".$wpdb->posts.".ID=".counterize_pageTable().".postID AND ".$wpdb->posts.".post_status LIKE 'publish' AND ".$wpdb->posts.".post_type LIKE 'post' GROUP by postID ORDER by c DESC");
foreach ($result as $post) {
setup_postdata($post);
$postid = $post->ID;
$title = $post->post_title;
$sql = "SELECT SUM( `count` ) "
. " FROM `" . counterize_pageTable() . "` "
. " WHERE `postID` = $post->ID";
$result = $wpdb->get_var( $wpdb->prepare( $sql, $thePostID ) );
$commentcount = $result;
if ($commentcount != 0) { ?>
<li><a href="<?php echo get_permalink($postid); ?>" title="<?php echo $title ?>">
<?php echo $title ?></a> {<?php echo $commentcount ?>}</li>
<?php } } ?>
i use this snipped in my archive.. my code-blocks in it looks like this:
can i use the cat & showposts in the code on the top like it did it below?
<?php $i = 1; echo '<div class="post-row2">'; query_posts('cat=' . $cat_id = get_query_var('cat') .'&showposts=6&offset=6'); while ( have_posts() ): the_post(); ?>
<?php get_template_part('2col2'); ?>
<?php if($i % 2 == 0) { echo '</div><div class="post-row2">'; } $i++; endwhile; echo '</div>'; ?></div>
Rempty comments:
Try this new query
"SELECT SUM(count) as c, ID,post_title FROM ".counterize_pageTable().", ".$wpdb->posts." INNER JOIN ".$wpdb->term_relationships." ON (wp_posts.ID = ".$wpdb->term_relationships.".object_id) WHERE ( ".$wpdb->term_relationships.".term_taxonomy_id IN (". get_query_var('cat').") ) AND ".$wpdb->posts.".ID=".counterize_pageTable().".postID AND ".$wpdb->posts.".post_status LIKE 'publish' AND ".$wpdb->posts.".post_type LIKE 'post' GROUP by postID ORDER by c DESC LIMIT 6,6"
Patrick comments:
Thanks Rempty! ORDER by DESC LIMIT and its offset works liks charm. But if i use INNER JOIN it breaks my code again : /
Rempty comments:
echo the query and paste
and remember you must be in a archives or category page to use get_query_var('cat')
Patrick comments:
omg i feel so stupid right now.. im really sorry for all this questions..
im using this code in my archive.. it looks like this..
<?php if (stripos($_SERVER['REQUEST_URI'],'/category/') !== false): ?><div class="post-list group"><?php $i = 1; echo '<div class="post-row">'; query_posts('cat=' . $cat_id = get_query_var('cat') .'&showposts=1&offset=0&post_status=publish'); while ( have_posts() ): the_post(); ?>
<?php if($i % 2 == 0) { echo '</div><div class="post-row">'; } $i++; endwhile; echo '</div>'; ?>
<div class="catgridright ">
<?php get_sidebar('footer-1'); ?>
<?php $result = $wpdb->get_results("SELECT SUM(count) as c, ID,post_title FROM ".counterize_pageTable().", ".$wpdb->posts." INNER JOIN ".$wpdb->term_relationships." ON (wp_posts.ID = ".$wpdb->term_relationships.".object_id) WHERE ( ".$wpdb->term_relationships.".term_taxonomy_id IN (". get_query_var('cat').") ) AND ".$wpdb->posts.".ID=".counterize_pageTable().".postID AND ".$wpdb->posts.".post_status LIKE 'publish' AND ".$wpdb->posts.".post_type LIKE 'post' GROUP by postID ORDER by c DESC LIMIT 6,6");
foreach ($result as $post) {
setup_postdata($post);
$postid = $post->ID;
$title = $post->post_title;
$sql = "SELECT SUM( `count` ) "
. " FROM `" . counterize_pageTable() . "` "
. " WHERE `postID` = $post->ID";
$result = $wpdb->get_var( $wpdb->prepare( $sql, $thePostID ) );
$commentcount = $result;
if ($commentcount != 0) { ?>
<li><a href="<?php echo get_permalink($postid); ?>" title="<?php echo $title ?>">
<?php echo $title ?></a> {<?php echo $commentcount ?>}</li>
<?php } } ?>
<?php if($i % 2 == 0) { echo '</div><div class="post-row2">'; } $i++; endwhile; echo '</div>'; ?></div>
<?php $i = 1; echo '<div class="post-row2">'; query_posts('cat=' . $cat_id = get_query_var('cat') .'&showposts=6&offset=6'); while ( have_posts() ): the_post(); ?>
<?php get_template_part('2col2'); ?>
<?php if($i % 2 == 0) { echo '</div><div class="post-row2">'; } $i++; endwhile; echo '</div>'; ?></div>
I think you mean that i use i echo like "<?php $i = 1; echo" in this query? i tried it, but it didnt worked : /
Rempty comments:
echo "SELECT SUM(count) as c, ID,post_title FROM ".counterize_pageTable().", ".$wpdb->posts." INNER JOIN ".$wpdb->term_relationships." ON (wp_posts.ID = ".$wpdb->term_relationships.".object_id) WHERE ( ".$wpdb->term_relationships.".term_taxonomy_id IN (". get_query_var('cat').") ) AND ".$wpdb->posts.".ID=".counterize_pageTable().".postID AND ".$wpdb->posts.".post_status LIKE 'publish' AND ".$wpdb->posts.".post_type LIKE 'post' GROUP by postID ORDER by c DESC LIMIT 6,6"
Is there posts in the current category and have counts?
Patrick comments:
there are posts - and they have counts.
In this snipped, i get results.
<?php $result = $wpdb->get_results("SELECT SUM(count) as c, ID,post_title FROM ".counterize_pageTable().", ".$wpdb->posts." WHERE ".$wpdb->posts.".ID=".counterize_pageTable().".postID AND ".$wpdb->posts.".post_status LIKE 'publish' AND ".$wpdb->posts.".post_type LIKE 'post' GROUP by postID ORDER by c DESC LIMIT 6,6");
but in the snipped you postet right now, i get an error
Rempty comments:
Adding the inner join to the term_relationships, add fillter by categories get_query_var('cat') is the current category(archives or category page).
Maybe you don't have posts with category + counts.
I want check your query, because the query is working in my localhost.
Please echo the query
echo "SELECT SUM(count) as c, ID,post_title FROM ".counterize_pageTable().", ".$wpdb->posts." INNER JOIN ".$wpdb->term_relationships." ON (wp_posts.ID = ".$wpdb->term_relationships.".object_id) WHERE ( ".$wpdb->term_relationships.".term_taxonomy_id IN (". get_query_var('cat').") ) AND ".$wpdb->posts.".ID=".counterize_pageTable().".postID AND ".$wpdb->posts.".post_status LIKE 'publish' AND ".$wpdb->posts.".post_type LIKE 'post' GROUP by postID ORDER by c DESC LIMIT 6,6"
Patrick comments:
if i use echo, i get nothing.. you have a pm from me.