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.

OK I need to query posts using the following criteria:

category_name=office, meta_key=featured_post, meta_value=Yes

and order these results using a second custom field which has a numerical value

meta_key=prop_order

I have the following query which pulls the correct posts, but doesn't order them by the second custom field.

<?php $recent = new WP_Query("category_name=office&meta_key=featured_post&meta_value=Yes&posts_per_page=3&orderby=date&order=ASC"); while($recent->have_posts()) : $recent->the_post(); ?>

However since I'm already using a custom field in the query I can't use orderby=meta_value.

I found the following SQL query on the forums but can't get it working.

    <?php
 global $wpdb;
 global $post;
 $querystr = "
SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta AS proporder ON(
$wpdb->posts.ID = proporder.post_id
AND proporder.meta_key = 'prop_order'
)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->term_taxonomy.term_id = 3
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->posts.post_status = 'publish'
AND $wpdb->wpostmeta.meta_key = 'featured_post' 
AND $wpdb->wpostmeta.meta_value = 'Yes' 
ORDER BY proporder.meta_value ASC
 ";

 $pageposts = $wpdb->get_results($querystr, OBJECT);

?>

Any help would be greatly appreciated!

Thanks

Dave

share|improve this question
1  
Do you have to use the meta_key prop_order? Or could you use a posts/pages reorder plugin? Then you can drag and drop the posts into the correct order, and use orderby=menu_order, order=ASC or order=DESC in your query then the whole thing would be WP_Query("category_name=office&meta_key=featured_post&meta_value=Yes&posts_per_p‌​age=3&orderby=menu_order&order=ASC");? –  eileencodes Nov 17 '10 at 16:40
    
Hi @ Eileen, thanks for replying! In truth I need a number of these queries across my site, using different categories etc so I need to have a query like this in the template rather than using a plugin, unfortunately. –  daveaspinall Nov 17 '10 at 16:51
    
I had a second look, as I thought I may be able to use your suggestion, unfortunately I'm using custom post types aswell and those plugins don't seem to be able to manage them individually. –  daveaspinall Nov 17 '10 at 17:00
    
Use this plugin for post reorder, it works for custom post type and regular posts. It pusts a redorder button under each menu item in the post types. I really like it and it works great. wordpress.org/extend/plugins/post-types-order –  eileencodes Nov 17 '10 at 20:30
    
Did you ever get this working properly? I need exactly the same thing... –  Amanda Feb 17 '11 at 17:11
add comment

2 Answers 2

I ran your query on my local workstation. It looks like there is a typo:

You should have $wpdb->postmeta.meta_value not $wpdb->wpostmeta.meta_value (used twice).

share|improve this answer
add comment
up vote 1 down vote accepted

Thanks for your input guys. Didnt realise i'd never posted back on this! Thanks to Ethan and a few other foums heres the working code:

<?php
 global $wpdb;
 global $post;
 $querystr = "
SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta AS proporder ON(
$wpdb->posts.ID = proporder.post_id
AND proporder.meta_key = 'prop_order'
)
LEFT JOIN $wpdb->postmeta AS propfeatured ON(
$wpdb->posts.ID = propfeatured.post_id
AND propfeatured.meta_key = 'featured_post'
)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->term_taxonomy.term_id = 4
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->posts.post_status = 'publish'
AND propfeatured.meta_value='Yes'
ORDER BY proporder.meta_value ASC
 ";

 $pageposts = $wpdb->get_results($querystr, OBJECT);
 //print_r($querystr);
?>
<?php foreach ($pageposts as $post): ?>
<?php setup_postdata($post); ?>

<!-- some post stuff here -->

<?php endforeach; ?>

where 'prop_order' and 'featured_post' are the custom fields and posts are returned that match featured_post='Yes' and then they are ordered by 'prop_order' in ASC order.

hope this helps!

share|improve this answer
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.