Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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 have something like:

$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 );

...but not working. Any ideas?


Editors Note: This is a small plugin that should show how the query looks, as we likely don't have any data set available to test this.

<?php
/** Plugin Name: (#67600) Dump Query parts */
function wpse67600_dump_query_parts( $pieces )
{
    echo '<pre>'.var_export( $pieces, true ).'</pre>';
    return $pieces;
}
add_filter( 'posts_clauses', 'wpse67600_dump_query_parts' );

OP PLEASE ADD OUTPUT OF PLUGIN HERE - use the "edit" link.

EDIT by Dameer

OK, after tracing request and numerous workarounds, I've come up with the following...

If I simplify "$sfp_query_args" a little bit the result is close to what's required, however, inability to sort posts remains as is. Here it is:

$sfp_query_args1 = array(
    'tax_query' => array( array( 'taxonomy' => 'sfp_post_category', 'terms' => $cat_id_arr ) ),
    'post_type' => 'sfpposts',
    'post_status' => 'publish',
    'showposts' => (int)$per_page,
    'paged' => $paged,
    'meta_key' => 'is_sponsored', 
    'orderby' => 'meta_value date'
);
  • *orderby takes two attributes: meta_value and date*

So $wpdb->request with above arguments in query looks like this:

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, $wpdb->posts.post_date DESC 
LIMIT 0, $per_page

And finally, in order to be able to sort by meta_value as well, query should be set with only one minor difference:

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 [!ORDER MISSING!], $wpdb->posts.post_date DESC 
LIMIT 0, $per_page

Please spot [!ORDER MISSING!] placeholder. I guess the above should explain where exactly the problem occurs.

share|improve this question
    
I don't believe you can do that with default WP_Query class. Even in the docs it says "Do you know how to sort the query if the meta_value is an array? Write it here". You will probably have to write your own SQL query for this. –  Miha Rekar Oct 9 '12 at 9:15
    
Yeap, I know it hasn't been resolved yet but I thought this is the right place to sort it out :) –  Dameer Oct 9 '12 at 10:38
    
I've added a small plugin to your question, so you can show us the final SQL query parts. Please edit your question with that info. Thanks. –  kaiser Oct 9 '12 at 23:07
    
Oh and here is some info from a related question about how sorting works in general. –  kaiser Oct 9 '12 at 23:08
add comment

2 Answers

up vote 1 down vote accepted

OK, the final workaround would be to split query:

$sfp_query_args = array(
    'tax_query' => array( array( 'taxonomy' => 'sfp_post_category', 'terms' => $cat_id_arr ) ),
    'meta_key' => 'is_sponsored',
    'post_type' => 'sfpposts',
    'post_status' => 'publish',
    'showposts' => (int)$per_page,
    'paged' => $paged
);

...and use "posts_orderby" filter to modify ORDER part:

add_filter( 'posts_orderby', 'sfp_modify_orderby' );
function sfp_modify_orderby( $orderby ) {
    if( !is_admin() && is_tax( 'sfp_post_category' ) ) {
        global $wpdb;
        $orderby = " $wpdb->postmeta.meta_value DESC, $wpdb->posts.post_date DESC ";
    }
    return $orderby;
}

The most probably you'll need to remove filter after the loop on page in order to prevent 'posts_orderby' affect any other query (sidebar or footer). So here's another function to put in "functions.php":

function sfp_remove_orderby_filter() {
    remove_filter( 'posts_orderby', 'sfp_modify_orderby' );
}

...and on the page using our query discard filter:

if( have_posts() ) : while( have_posts() ) : the_post();
    // code
endwhile;
else :
    // code
endif;

sfp_remove_orderby_filter();

Hopefully it makes sense!

share|improve this answer
add comment

I am writing your query modifying slightly. I hope it may help.

$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', 
    'meta_query' => array(
    array(
        'key' => 'sfp_date',
            'type' => 'NUMERIC',
    ),
    array(
        'key' => 'is_sponsored',
        'value' => '2',
        'compare' => '<='
    )       
    ),
    'orderby' => 'meta_value_num', 
    'order' => 'DESC',
);
$wp_q = new WP_Query( $sfp_query_args );

Please let me know whether it works or not :-)

share|improve this answer
2  
This would default sort (by date), since you don't provide 'meta_key'. –  Miha Rekar Oct 9 '12 at 9:37
    
Thank you @MihaRekar, It was a mistake thanks for your correction –  Md Toufiqul Islam Oct 9 '12 at 10:00
    
I'm afraid it doesn't work, it just keeps sorting posts by date without putting those who have "is_sponsored" value of 1 to the top. –  Dameer Oct 9 '12 at 10:41
    
I am looking into finding a solution to this. May be @MihaRekar is right. You may have to write custom sql query for this –  Md Toufiqul Islam Oct 9 '12 at 10:44
    
Is there a way you can provide an example of "normal" mySQL query? –  Dameer Oct 9 '12 at 10:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.