meta_query sorting by 2 keys WordPress
I need to sort (custom) posts by 2 custom field values...
custom field name 1: is_sponsored [ value can either be 1 or 0 ]
custom field name 2: sfp_date [ timestamp aka current post date in seconds ]
Posts whose "is_sponsored" value is 1 need to be on top, sorted by "sfp_date" in descending order.
All other posts whose "is_sponsored" value is 0 should be listed below - in descending order (by "sfp_date") as well.
I'm not good with mySQL but if sorting like that is possible with "normal" query, it should be possible with WP's meta_query as well.
What I have so far is here:
$sfp_query_args = array(
'tax_query' => array( array( 'taxonomy' => 'sfp_posts', 'terms' => array( 1, 5, 8) ) ),
'post_type' => 'sfpposts',
'post_status' => 'publish',
'showposts' => 15,
'paged' => $paged,
'meta_key' => 'sfp_date',
'orderby' => 'meta_value_num',
'order' => 'DESC', 'meta_query' => array( 'key' => 'is_sponsored', 'value' => 2, 'type' => 'NUMERIC', 'compare' => '<=' ),
);
$wp_q = new WP_Query( $sfp_query_args );
Answers (4)
Luis Abarca answers:
Maybe something like this [[LINK href="http://wordpress.stackexchange.com/questions/32175/sorting-by-2-custom-fields-post-title"]]http://wordpress.stackexchange.com/questions/32175/sorting-by-2-custom-fields-post-title[[/LINK]]
dameer comments:
Unfortunately I can't use standard mysql query. In that case I'll have to make "foreach" loop with setup_postdata() that doesn't seem to work well with pagination (wp_pagenavi).
Bryan Headrick answers:
you currently can't sort by multiple elements using the custom query format, but you don't necessarily have to do a complete sql query to sort by multiple items. You can use a filter hook to change the sorting -although you'll need to throw in some extra conditional statements to isolate the sorting to the specific loop you want to sort, otherwise, all loops will be sorted by those meta fields, and posts w/o those meta fields won't show up at all.
For your particular application, though, the simplest solution would be to just create 2 separate queries and then merge them. First, you create your query that you want to be first:
$sfp_query1 = get_posts(array(
'tax_query' => array( array( 'taxonomy' => 'sfp_posts', 'terms' => array( 1, 5, 8) ) ),
'post_type' => 'sfpposts',
'post_status' => 'publish',
'showposts' => 15,
'paged' => $paged,
'meta_key' => 'sfp_date',
'orderby' => 'meta_value_num',
'order' => 'DESC', 'meta_query' => array( 'key' => 'is_sponsored', 'value' => 1, 'type' => 'NUMERIC', 'compare' => '=' ),));
Then the second set where is_sponsored is 0:
$sfp_query2 = get_posts(array(
'tax_query' => array( array( 'taxonomy' => 'sfp_posts', 'terms' => array( 1, 5, 8) ) ),
'post_type' => 'sfpposts',
'post_status' => 'publish',
'showposts' => 15,
'paged' => $paged,
'meta_key' => 'sfp_date',
'orderby' => 'meta_value_num',
'order' => 'DESC', 'meta_query' => array( 'key' => 'is_sponsored', 'value' => 0, 'type' => 'NUMERIC', 'compare' => '=' ),
));
Now, merge the two results:
$sfp_query_posts = array_merge($sfp_query1,$sfp_query1);
$sfp_query_args = array('post__in' => $sfp_query_posts);
and your final query:
$wp_q = new WP_Query( $sfp_query_args );
You may just want to loop through the post id's from the $sfp_query_args array (which already has all the posts in the proper order). Now that I think of it, if you just feed that array into a new query, it will probably resort them by date.
dameer comments:
OK, I'll try with merging two queries and let you know the result.
As for making an "incomplete" query and then fixing it up with "posts_orderby" - it won't work. Why? Because I have to run this WP_Query on Page. If I apply "posts_orderby" filter on specific page it'll just modify Page's default query while my custom one remains intact.
dameer comments:
Uhmmm... it gets far away from any result Bryan. Only one single post is shown and it's not even of custom post type :)
daas answers:
Hi there,
Try with SQL query. :)
global $wpdb;
$sql = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta is_sponsored, $wpdb->postmeta sfp_date
WHERE wposts.ID = is_sponsored.post_id
AND wposts.ID = sfp_date.post_id
AND is_sponsored.meta_key = 'is_sponsored'
AND sfp_date.meta_key = 'sfp_date'
AND wposts.post_status = 'publish'
AND wposts.post_type = 'sfpposts'
ORDER BY is_sponsored.meta_value, sfp_date.meta_value DESC LIMIT 0,15;
";
$query = $wpdb->get_results( $sql, OBJECT );
dameer comments:
There's a reason why standard sql query is not possible here...and I think your query doesn't sort posts as required. I guess the proper method would be:
$custom_query = "SELECT SQL_CALC_FOUND_ROWS $wpdb->posts.ID
FROM $wpdb->posts
INNER JOIN $wpdb->term_relationships
ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
INNER JOIN $wpdb->postmeta
ON ($wpdb->posts.ID = $wpdb->postmeta.post_id)
WHERE 1=1
AND $wpdb->posts.post_type = 'sfpposts'
AND ($wpdb->posts.post_status = 'publish')
AND ($wpdb->postmeta.meta_key = 'is_sponsored' )
GROUP BY $wpdb->posts.ID
ORDER BY $wpdb->postmeta.meta_value DESC, $wpdb->posts.post_date DESC
LIMIT 0, $per_page";
Sébastien | French WordpressDesigner answers:
is_sponsored is 0:
$sfp_query_args = array(
'tax_query' => array( array( 'taxonomy' => 'sfp_posts', 'terms' => array( 1, 5, 8) ) ),
'post_type' => 'sfpposts',
'post_status' => 'publish',
'showposts' => 15,
'paged' => $paged,
'meta_key' => 'sfp_date',
'orderby' => 'meta_value_num',
'order' => 'DESC', 'meta_query' => array( 'key' => 'is_sponsored', 'value' => 0, 'type' => 'NUMERIC', 'compare' => '=' ),
);
$wp_q = new WP_Query( $sfp_query_args );
is_sponsored is 1:
$sfp_query_args = array(
'tax_query' => array( array( 'taxonomy' => 'sfp_posts', 'terms' => array( 1, 5, 8) ) ),
'post_type' => 'sfpposts',
'post_status' => 'publish',
'showposts' => 15,
'paged' => $paged,
'meta_key' => 'sfp_date',
'orderby' => 'meta_value_num',
'order' => 'DESC', 'meta_query' => array( 'key' => 'is_sponsored', 'value' => 1, 'type' => 'NUMERIC', 'compare' => '=' ),
);
$wp_q = new WP_Query( $sfp_query_args );