这是一个复杂的查询,我使用它按分类法段塞对POST查询进行数字排序和过滤。
$my_post_type = \'some_post_type\';
$my_term_slug_to_be_used_for_Ordering = \'some_slug\';
$my_term_slug_to_be_used_for_Filtering = \'some_other_slug\';
$querystr = "
SELECT
$wpdb->posts.*, $wpdb->term_relationships.object_id,
(SELECT wpt.slug
FROM $wpdb->term_taxonomy wptt
INNER JOIN $wpdb->term_relationships wptr ON wptt.term_taxonomy_id = wptr.term_taxonomy_id
INNER JOIN $wpdb->terms wpt ON wpt.term_id = wptt.term_id
WHERE
wptr.object_id = $wpdb->term_relationships.object_id AND
wptt.taxonomy = \'my_term_slug_to_be_used_for_Ordering\') AS numeric_column_value
FROM $wpdb->term_taxonomy INNER JOIN $wpdb->term_relationships ON $wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id
INNER JOIN $wpdb->posts ON $wpdb->posts.ID = $wpdb->term_relationships.object_id
INNER JOIN $wpdb->terms ON $wpdb->terms.term_id = $wpdb->term_taxonomy.term_id
WHERE
$wpdb->terms.slug = \'{$my_term_slug_to_be_used_for_Filtering}\' AND
$wpdb->posts.post_type = \'$my_post_type\' AND $wpdb->posts.post_status = \'publish\'
ORDER BY numeric_column_value * 1 DESC"; // Actually it is a string so, * 1 for numeric
$r = $wpdb->get_results($querystr, OBJECT);