[wp-hackers] Query by multiple custom fields/taxonomies, MySQL LIMIT

[Matthew Gerring]

Hello all,

I'm working on a plugin for a client that allows narrowing of search results, i.e. querying by multiple custom taxonomies and meta keys at once. So far as I can determine there's no built in way to do this (as in get only posts that are in both one taxonomy AND another, or are both in a category AND have a particular tag, or have a certain meta value AND another meta value of a different key, and so forth).

My problem is that I don't know SQL that well, and I'm wondering if there's a means of setting up the record set I'm returning to automatically work with WordPress paging. If not, I was wondering how you get the total size of the record set regardless of the LIMIT statement so I can set up paging manually. I also want to know in general if there's a better way to do what I'm doing. Here's the code:

function sw_custom_query($input) {
	global $wpdb;
	$input = wp_parse_args($input);
	//var_dump($input);
	$bio_meta_keys = array('dob','loc');
	$bio_tax = array('faith-tradition','alive','type');
	foreach ($input as $key=>$value) {
		if ( in_array($key,$bio_meta_keys) || in_array($input['orderby'], $bio_meta_keys) ) {
			
			if (in_array($key,$bio_meta_keys)) {	
				$input['where'] .= ' AND '.$key.'.meta_value = \''.$value.'\''; 
			}
			
			if ($key == 'orderby') { $key = $value; }
			
			if ( in_array($input['orderby'], $bio_meta_keys) ) {
				$input['select'] .= ', '.$key.'.meta_value';
				$input['order'] .= ' ORDER BY '.$key.'.meta_value';
			}
			
			$input['from'] .= ', '.$wpdb->postmeta.' '.$key.' ';
			$input['where'] .= ' AND '.$key.'.meta_key = \''.$key.'\'';
			$input['where'] .= ' AND p.id = '.$key.'.post_id';
		}
		
		if (in_array($key,$bio_tax) || in_array($input['orderby'], $bio_tax)) {
			if ($key == 'orderby') { $key = $value; }
			$modkey = str_replace('-','_',$key);
			if ( in_array($key,$bio_tax) ) {
				$sort = ' AND '.$modkey.'t.slug = \''.$value.'\'';
			} else {
				$sort = '';
			}
			if ( in_array($input['orderby'], $bio_tax) ) {
				$input['select'] = ', '.$modkey.'t.slug';
				$input['order'] .= ' ORDER BY '.$modkey.'t.slug';
			}
			$input['from'] .= ', '.$wpdb->terms.' '.$modkey.'t';
			$input['from'] .= ', '.$wpdb->term_taxonomy.' '.$modkey.'tt';
			$input['from'] .= ', '.$wpdb->term_relationships.' '.$modkey.'tr ';				
			$input['where'] .= ' AND p.id = '.$modkey.'tr.object_id';
			$input['where'] .= ' AND '.$modkey.'t.term_id = '.$modkey.'tt.term_id';
			$input['where'] .= ' AND '.$modkey.'tr.term_taxonomy_id = '.$modkey.'tt.term_taxonomy_id';
			$input['where'] .= ' AND ('.$modkey.'tt.taxonomy = \''.$key.'\' AND '.$modkey.'tt.term_id = '.$modkey.'t.term_id'.$sort.' )';
		}
	}
	if ( $input['orderby'] == 'name' ) { $input['order'] = " ORDER BY p.post_title "; }
	$querystr .= "SELECT p.*".$input['select'];
	$querystr .= " from $wpdb->posts p, $wpdb->postmeta meta";
	$querystr .= $input['from'];
	$querystr .= " WHERE p.id = meta.post_id";
	$querystr .= $input['where'];
	$querystr .="
			AND meta.meta_key = 'mf_page_type'
	    		AND meta.meta_value = 'Biography'
	    		AND p.post_type = 'page' 
			AND p.post_status = 'publish'
			";
	$querystr .= $input['order'];
	$querystr .=" LIMIT 5, 5"; // This is here for testing purposes- no variable input here yet.
	//echo $querystr;
	return $querystr;
}

Thanks for any help you can provide.
Matthew