Usage of the new “posts_clauses” filter in WordPress 3.1?

1 Views

Just noticed that 3.1 added a new filter to customize queries: posts_clauses. All I could find about it is that instead of using the separate query filters like posts_where or posts_join, you can edit them all in one go. I was wondering if someone could give an example of the usage of the new “posts_clauses” filter?

Post Views: 1

Related posts

Leave a Reply

2 comments

  1. Below is the query I found somewhere and was going through it. The purpose of query is to sort the posts based on taxonomy. So, I am sharing the code so it might help you understand the use of posts_clauses.

    function color_clauses_mike( $clauses, $query ) {
global $wpdb;

if ( isset( $query->query['orderby'] ) && 'color' == $query->query['orderby'] ) {

    $clauses['join'] .= <<<SQL
LEFT OUTER JOIN {$wpdb->term_relationships} ON {$wpdb->posts}.ID={$wpdb->term_relationships}.object_id
LEFT OUTER JOIN {$wpdb->term_taxonomy} USING (term_taxonomy_id)
LEFT OUTER JOIN {$wpdb->terms} USING (term_id)
SQL;

    $clauses['where'] .= " AND (taxonomy = 'color' OR taxonomy IS NULL)";
    $clauses['groupby'] = "object_id";
    $clauses['orderby']  = "GROUP_CONCAT({$wpdb->terms}.name ORDER BY name ASC) ";
    $clauses['orderby'] .= ( 'ASC' == strtoupper( $query->get('order') ) ) ? 'ASC' : 'DESC';
}

return $clauses;
}
add_filter( 'posts_clauses', 'color_clauses_mike', 10, 2 );

    here is the source where I found the above query: http://scribu.net/wordpress/sortable-taxonomy-columns.html

  2. The answer from scribu / Hameedullah is a great pointer, but it will hide posts that are a member of a different taxonomy (e.g. ‘category’), but not of ‘color’. I have modified the code, and this seems to work great:

    if ( isset( $wp_query->query['orderby'] ) && 'color' == $wp_query->query['orderby'] ) {

    $clauses['join'] .= <<<SQL
LEFT OUTER JOIN {$wpdb->term_relationships} ON {$wpdb->posts}.ID={$wpdb->term_relationships}.object_id
LEFT OUTER JOIN {$wpdb->term_taxonomy} ON wp_term_taxonomy.term_taxonomy_id=wp_term_relationships.term_taxonomy_id AND wp_term_taxonomy.taxonomy='color'
LEFT OUTER JOIN {$wpdb->terms} USING (term_id)
SQL;

    $clauses['groupby'] = "wp_posts.ID";
    $clauses['orderby']  = "GROUP_CONCAT({$wpdb->terms}.name ORDER BY name ASC) ";
    $clauses['orderby'] .= ( 'ASC' == strtoupper( $wp_query->get('order') ) ) ? 'ASC' : 'DESC';
}