Its a common requirement to list posts and pages that are in multiple categories. Sometimes you need content that matches any of the categories (ie, an OR type query) and sometimes you need content that needs to be in all the categories (ie, and AND type query). The heavy-duty query_posts makes this straight-forward.
However, query_posts isn’t always the best solution when trying to extract data using a custom database structure, or you just want to avoid the overhead of query_posts. For instance, you might just want the raw post data to drive the featured post function on your front page, or you have a custom table indexed by post id and you want to select records from it based on category.
As an example, consider a bug-tracking system. A post is created describing the bug and its categories set to indicate what software or system it applies to and its current status. A custom table is used to hold notes relating to the analysis and fix of each bug. You would want to allow people to search on bugs based on the software, version, resolution, severity, ticket number, etc. An example is WordPress’s own Tracking system.
To generate a buglist like WordPress’s, you’d need to run the massive query_posts to get the IDs and title and then run an additional query for each post to get the custom data like the Ticket Number. Or you can create a custom query to get want you need in one hit.
The following is a generic query function to return posts by category which can also be modified to select records from a custom table that is indexed on post ID.
function get_category_posts($cats, $type='and') {
global $wpdb;
$incats = implode(',', $cats);
$qry = "SELECT * FROM $wpdb->posts p
INNER JOIN $wpdb->term_relationships tr ON (p.id = tr.object_id)
INNER JOIN $wpdb->term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
WHERE tt.term_id IN ({$incats}) and tt.taxonomy='category' GROUP BY p.id";
if ($type == 'and') $qry .= " HAVING count(p.id) = ".count($cats);
$qry .= " ORDER BY p.post_date DESC";
return $wpdb->get_results($qry);
}The function is passed an array of the required category IDs and optionally the type of join, which is AND by default. Pass anything other than ‘and’ to perform an OR search. Here’s an example of its use:
$cats = array();
$cats[] = 23;
$cats[] = 57;
//could also use $cats = array(23,57);
$posts = get_category_posts($cats);
foreach ($posts as $apost) { ..To return records from a custom table that is indexed by post ID, substitute $wpdb->posts with your table name, ie {$table_prefix}mytable (don’t forget to global $table_prefix), substitute p.id for the name of the ID field and modify the ORDER BY clause accordingly.
