Filter posts by category and custom field key and value

“I have the feeling that this will require a custom SQL query“

So do I… But actually, here’s two ways to do it.

1. This is similar to what I use on my site for my plugins Page; it uses query_posts() to pull in the category, then tests custom key/value:

<?php
$catid = ''; // set to category ID #
$customkey = ''; // set to your custom key
$customvalue = ''; // set to custom value

query_posts("cat=$catid&showposts=-1");
while(have_posts()) : the_post();
$value = post_custom("$customkey");
if($customvalue == $value) : $more = 0;
?>

~ Your “Loop” template tags and whatnot go here ~

<?php endif; endwhile; ?>

2. The custom SQL query you were expecting:

<?php
$catid = ''; // set to category ID #
$customkey = ''; // set to your custom key
$customvalue = ''; // set to custom value

global $wpdb;
$my_posts = $wpdb->get_results("SELECT * FROM $wpdb->posts, $wpdb->post2cat, $wpdb->postmeta WHERE ID = $wpdb->post2cat.post_id AND ID = $wpdb->postmeta.post_id AND $wpdb->post2cat.category_id = '$catid' AND meta_key = '$customkey' AND meta_value = '$customvalue' ORDER BY post_date DESC");

foreach($my_posts as $post) : setup_postdata($post);
?>

~ Your “Loop” template tags and whatnot go here ~

<?php endforeach; ?>

For either option, just make sure to set the correct values for the three variables at the top of each code block: $catid, $customkey and $customvalue.

Cheers Kafkaesqui, query_posts() method worked a charm.

I’m sorting the posts by custom field value and it shows all the posts.
Is there any way to show only 10 posts and navigate through the rest of posts using “next_posts_link” or “previous_posts_link”, just like a simple pagination?

This is the code I’m using:

<?php $pageposts = $wpdb->get_results("SELECT wposts.* FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta, $wpdb->post2cat wpost2cat WHERE wposts.ID = wpostmeta.post_id AND wposts.ID = wpost2cat.post_id AND wpost2cat.category_id = '1' AND wpostmeta.meta_key = 'POSICION' ORDER BY wpostmeta.meta_value , wpostmeta.meta_id DESC", OBJECT);
?>
<?php if ($pageposts) : foreach ($pageposts as $post): setup_postdata($post);?>
<?php include(TEMPLATEPATH."/includes/whatever.php"); ?>
<?php endforeach;?>
<?php endif; ?>

Thanks a lot.

“Is there any way to show only 10 posts and navigate through the rest of posts using “next_posts_link” or “previous_posts_link”, just like a simple pagination?“

Not without a lot of work recreating WordPress’ functionality. But I’ll make the same offer as I do here:

https://www.ads-software.com/support/topic/96851#post-483662

My Custom Home plugin could be rewritten to work within category queries, but not in a Page (or category incorporating a template) using code like the SQL queries above.