I do not have a way to test this, but it should be close to what you need. Replace the current $where with this:
$where .= " AND vipwp_posts.id = (SELECT id FROM {$wpdb->prefix}posts p2, {$wpdb->prefix}postmeta pm
where pm.object_id = p2.ID
AND pm.meta_key = 'name_key'
AND p2.post_status = 'publish'
AND p2.post_author = {$wpdb->prefix}posts.post_author
ORDER BY pm.meta_value ASC Limit 0,1)";
I suggest you use phpMyAdmin or a similar tool to test the query before putting it into your function.
Thanks vtxyzzy,
i’ve tried the function, but the posts are not displayed.
You will need to do some debugging. Just after the query, insert a print_r() call like this:
global $wp_query;
print_r('<p>REQUEST:');print_r($wp_query->request);print_r('</p>');
Then copy the query from the screen and test it in phpMyAdmin to see if it gives any errors, or just no records.
I found one error: replace ‘pm.object_id’ with ‘pm.post_id’ in this line: ‘where pm.object_id = p2.ID’
just no records:
REQUEST:
Did you try replacing pm.object_id with pm.post_id?
I am puzzled why no request showed up. Can you put the code for the file containing the query_posts() into a pastebin and post a link to it here?
Query:
<?php
//Displaying latest post per author on front page
function filter_where($where = '') {
global $wp_query;
print_r('<p>REQUEST:');print_r($wp_query->request);print_r('</p>');
$where .= " AND vipwp_posts.id = (SELECT id FROM {$wpdb->prefix}posts p2,
{$wpdb->prefix}postmeta pm where pm.post_id = p2.ID AND pm.meta_key = 'key_count'
AND p2.post_status = 'publish'
AND p2.post_author = {$wpdb->prefix}posts.post_author
ORDER BY pm.meta_value ASC Limit 0,1)";
return $where;
}
add_filter('posts_where', 'filter_where');
?>
<?php
$week = date('W');
$year = date('Y');
?>
<?php if (have_posts()) { ?>
<?php
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$args = array(
'posts_per_page' => 12,
'year' => $year,
'w' => $week,
'meta_key' => 'key_count',
'orderby' => 'meta_value',
'paged'=>$paged
);
query_posts($args);
while (have_posts()) { the_post();
?>
These statements go just after the call to query_posts(), not inside the function:
global $wp_query;
print_r('<p>REQUEST:');print_r($wp_query->request);print_r('</p>');
Sorry.
REQUEST:SELECT SQL_CALC_FOUND_ROWS vipwp_posts.ID FROM vipwp_posts INNER JOIN vipwp_postmeta ON (vipwp_posts.ID = vipwp_postmeta.post_id) WHERE 1=1 AND YEAR(vipwp_posts.post_date)='2012' AND WEEK(vipwp_posts.post_date, 1 ) = '42' AND vipwp_posts.post_type = 'post' AND (vipwp_posts.post_status = 'publish' OR vipwp_posts.post_status = 'private') AND (vipwp_postmeta.meta_key = 'key_count' ) AND vipwp_posts.id = (SELECT id FROM posts p2, postmeta pm where pm.post_id = p2.ID AND pm.meta_key = 'key_count' AND p2.post_status = 'publish' AND p2.post_author = posts.post_author ORDER BY pm.meta_value ASC Limit 0,1) GROUP BY vipwp_posts.ID ORDER BY vipwp_postmeta.meta_value DESC LIMIT 0, 12
If you look at this section of the request:
vipwp_posts.id = (SELECT id FROM posts p2, postmeta pm where pm.post_id = p2.ID AND pm.meta_key = 'key_count' AND p2.post_status = 'publish' AND p2.post_author = posts.post_author ORDER BY pm.meta_value ASC Limit 0,1)
You can see that {$wpdb->prefix}posts is getting translated to posts. In other words, $wpdb->prefix is empty.
I think you need to declare $wpdb global in your function.
Hi,
now $wpdb global is declared, but shows only one post instead of 12 and the only post showing is wrong.
REQUEST:SELECT SQL_CALC_FOUND_ROWS vipwp_posts.ID FROM vipwp_posts INNER JOIN vipwp_postmeta ON (vipwp_posts.ID = vipwp_postmeta.post_id) WHERE 1=1 AND YEAR(vipwp_posts.post_date)=’2012′ AND WEEK( vipwp_posts.post_date, 1 ) = ’43’ AND vipwp_posts.post_type = ‘post’ AND (vipwp_posts.post_status = ‘publish’ OR vipwp_posts.post_status = ‘private’) AND (vipwp_postmeta.meta_key = ‘tweets_count’ ) AND vipwp_posts.id = (SELECT id FROM vipwp_posts p2, vipwp_postmeta pm where pm.post_id = p2.ID AND pm.meta_key = ‘tweets_count’ AND p2.post_status = ‘publish’ AND p2.post_author = vipwp_posts.post_author ORDER BY pm.meta_value ASC Limit 0,1) GROUP BY vipwp_posts.ID ORDER BY vipwp_postmeta.meta_value DESC LIMIT 0, 12
As I said, I have no way to test this, but I think that you may need to change the way you match post_id. Try this:
vipwp_posts.id IN (SELECT id FROM vipwp_posts p2, vipwp_postmeta pm where pm.post_id = p2.ID AND pm.meta_key = 'tweets_count' AND p2.post_status = 'publish' AND p2.post_author = vipwp_posts.post_author ORDER BY pm.meta_value ASC )
