Combine two queris using $query->set WordPress
- SOLVED
Hi!
I'm having this site where the user, via a drop down menu, can choose how the posts should be sorted (asc, desc, by views etc...)
The code looks like this:
<form action="" method="GET">
<select name="sort" id="sort">
<option value="senaste" <?php selected( get_query_var( 'sort' ), 'DESC' ); ?>>Visa senaste först</option>
<option value="views" <?php selected( get_query_var( 'sort' ), 'views' ); ?>>Visa populärast först</option>
<option value="pris" <?php selected( get_query_var( 'sort' ), 'pris' ); ?>>Visa billigast först</option>
</select>
<button type="submit" id="sort-submit">Sortera</button>
</form>
Then, in functions.php I'm having this function:
add_action( 'pre_get_posts', 'custom_pre_get_posts' );
function custom_pre_get_posts( $query ) {
// don't affect wp-admin screens
if ( is_admin() )
return;
// vars
$sort = get_query_var( 'sort' );
/** change the order of posts in the main query */
if ( $query->is_main_query() && $sort ) {
// change 'order' (ASC or DESC)
if ( in_array( $sort, array( 'ASC', 'DESC' ) ) ) {
$query->set( 'order', $sort );
// most views
} else if ( 'views' == $sort ) {
$query->set( 'v_sortby', 'views');
$query->set( 'v_orderby', 'desc' );
$query->set( 'v_timespan', 'month' );
add_filter('posts_fields', 'post_views_fields');
add_filter('posts_join', 'post_views_join');
add_filter('posts_where', 'post_views_where');
add_filter('posts_orderby', 'post_views_orderby');
} else if ( 'price' == $sort ) {
$query->set( 'orderby', 'meta_value_num' );
$query->set( 'meta_key', $sort );
$query->set( 'order', 'ASC' );
}
}
}
My problem has to do with the <strong>views</strong> part of the code, so keep your focus there!
To keep track of the views, I'm using the Post Views plugin:
http://wordpress.org/extend/plugins/post-views/
As you can see in the code above, I'm filtering the popular posts by views to monthly, to keep the results as relevant and fresh as possible. The function works as it should, but I would like to modify it a bit:
I have a lot of posts on the site in question, and only some of them has got views the last month. This means that the function <strong>won't show the posts that haven't got any views</strong> the last month.
What I would like to do is to first <strong>use the function above</strong> to get the posts with views this month and sort them by views, just like it already does. But then I would like to <strong>fetch all remaining posts</strong> and display them after the popular posts.
So I need to <strong>merge (combine) these two queries into one</strong>. Could anyone help me with this?
Hope I'm making myself clear, just ask me if you need more information!
Thanks!
// Jens.
Answers (3)
Eric P. answers:
@Dbrains, I don't think that the view counter is even set on posts with no views at all. So I'm not sure your solution will work for all posts. It would push to the bottom all posts with views, but with zero views this month, but it wouldn't show new posts with no views that have been posted this month (the newest posts).
I guess I'm not sure that @Jens Filipsson has told us completely what should come next after the posts with views this month >0. What is wanted there will determine what code is needed (and how complex that code will be.
I think just removing the line "add_filter('posts_where', 'post_views_where');" would be the first step. That should keep all posts and just sort them by the views in the past month.
The question then is sorting. You would want to sort first on views this month, but then for the zero views this month, by post date (newest first)? Or unset views, newest first, then total views in descending order?
Jens Filipsson comments:
Eric, you understand what I'm after! I would first like to sort by views this month, yes, and then for the zero views, I think a normal query (newest first) would be perfect.
So I'm thinking something like putting the function in some kind of array, then merge it with a normal query, use posts_not_in the first array and then query them all somehow.
Am I thinking right or is this not doable?
// Jens.
Jens Filipsson comments:
The questions is, if I give all posts a default value of 1 to views and remove the line "add_filter('posts_where', 'post_views_where');" , would that do the trick? Or is there a more bulletproof solution perhaps?
Eric P. comments:
@Jens Filipsson, did you try just commenting out your "add_filter('posts_where', 'post_views_where');" line? What were the results of that?
In theory, that should get you all the posts, but sort them by views this month (putting the post with zero posts this month at the bottom if you do descending sort).
The question then is how to set up a secondary sort order for posts with the same number of views this month (especially for the last ones with zero views this month).
Setting every post with views=1 won't help. Those views will age off (next month, they'll be > one month old, and in theory drop to zero views for the past month again)
Jens Filipsson comments:
I tried it and so far so good, it seems like it fetches all the posts and put the ones with views this month first. Older posts seem to show up as well. These do have views set to 1 though, but I'm ok with that, as long as they show up. Gonna test it a bit more, but it feels like we're on to something here! :)
Eric P. comments:
Sounds like you're on the way to a good solution.
posts_where is used to change the results that are returned by the wp_query object. When you add a filter on 'posts_where' you usually get a different result set (maybe fewer posts, maybe more posts, maybe just different posts).
If you want the same posts as before in a different order, leave 'posts_where' alone and focus on 'posts_orderby'. The return set is sorted/ordered by the 'posts_orderby' filter.
You should realize that any action added to posts_where (and posts_join) will affect <strong>all</strong> wp_query objects (except queries created with 'suppress_filters'=>true ). get_posts() defaults to suppress_filters=>true, but get_pages() and some other functions that use wp_query objects use all the filters.
Eric P. comments:
@Dbranes, if the query returns the right result set without a filter on "posts_where", then it's best not to use a filter on "posts_where" at all.
You are adding "(post_type ='post' AND post_status = 'publish' AND post_views_total IS NULL ))" to every WP_Query object ( well, every one that isn't created with "suppress_filters"=>ture ).
For some queries which don't meet the first str_replace, you're adding two unbalanced closing parenthesis? What happens with your "posts_where" filter if a query is created without the "AND {$wpdb->posts}.post_type = 'post'" text in the where part? Then you get a database error from a mal-formed query.
If you do that, you have to predicate the second change on finding and replacing the text in the first str_replace. If that does nothing, then you should't add the ") OR ..." part.
Dbranes answers:
You could try to replace <strong>!= 0</strong> with <strong>>= 0</strong> in the <strong>posts_where</strong> filter.
Something like this (untested):
add_filter('posts_where','my_posts_where', 99, 1);
function my_posts_where($where){
return str_replace("!= 0",">= 0",$where);
}
Jens Filipsson comments:
Yes, I tried this, but this still doesn't work when I filter it by month, if the post has no views the last month.
Jens Filipsson comments:
Sorry, answered the wrong post...
Dbranes comments:
@Eric, you are right about the posts with no ivews.
You could try to modify the WHERE part with this:
add_filter('posts_where','my_posts_where', 99, 1);
function my_posts_where($where){
global $wpdb;
$where= str_replace("AND {$wpdb->posts}.post_type = 'post'", "AND (({$wpdb->posts}.post_type = 'post'", $where);
$where .= ") OR (post_type ='post' AND post_status = 'publish' AND post_views_total IS NULL ) ) ";
return $where;
}
to add the <strong>NULL </strong>part, since the plugin is using <strong>LEFT JOIN</strong>.
Dbranes comments:
... and to control the second order you can play with:
add_filter('posts_orderby','my_posts_orderby', 99, 1);
function my_posts_orderby($orderby){
return $orderby.", post_title ASC ";
}
So your code will look like
} else if ( 'views' == $sort ) {
$query->set( 'v_sortby', 'views');
$query->set( 'v_orderby', 'desc' );
$query->set( 'v_timespan', 'month' );
add_filter('posts_fields', 'post_views_fields');
add_filter('posts_join', 'post_views_join');
add_filter('posts_where', 'post_views_where');
add_filter('posts_orderby', 'post_views_orderby');
add_filter('posts_where', 'my_posts_where', 99, 1); // Extra
add_filter('posts_orderby', 'my_posts_orderby', 99, 1); // Extra
}
Dbranes comments:
ps: this should allow you to keep the native post-view order.
Dbranes comments:
@Eric, I think we can fix that by targetting the main query and the v_sortby parameter:
add_filter('posts_where','my_posts_where', 99, 1);
function my_posts_where($where){
global $wpdb;
if(!is_admin() && is_main_query() && get_query_var('v_sortby')){
$where= str_replace("AND {$wpdb->posts}.post_type = 'post'", "AND (({$wpdb->posts}.post_type = 'post'",$where);
$where .= ") OR ({$wpdb->posts}.post_type ='post' AND {$wpdb->posts}.post_status = 'publish' AND post_views_total IS NULL)) ";
}
return $where;
}
add_filter('posts_orderby','my_posts_orderby', 99, 1);
function my_posts_orderby($orderby){
global $wpdb;
if(!is_admin() && is_main_query() && get_query_var('v_sortby'))
$orderby .= ", post_title ASC ";
return $orderby;
}
By removing the post_where filter, we loose all the ordering that the plugin is making:
$content .= " AND ".WP_POST_VIEWS_TABLE.".view_type = '".$v_sortby."' AND ".WP_POST_VIEWS_TABLE.".output_type = '".$v_outtype."' AND ".WP_POST_VIEWS_TABLE.".".$v_timespan." != 0 ";
that's of course ok, if the asker is looking for that kind of solution ;-)
About the paranthesis part, one could of course adjust them, if that's a problem ;-)
Daniel Yoen answers:
hello,
Just idea : you can add a default value "1" to "views" post meta
Jens Filipsson comments:
Yes, I tried this, but this still doesn't work when I filter it by month, if the post has no views the last month.
Daniel Yoen comments:
how do you grouping posts? (monthly), like this ?
function post_views_where($where = '')
{
$where .= " AND post_date > '" . date('Y-m-d', strtotime('-30 days')) . "'";
return $where;
}
add_filter('posts_where', 'post_views_where');