Wordpress query_posts order by date when excluding a category

Question

I want to exclude a category and order the results by date with query_posts while preserving existing query parameters and order the results by date.

According to the Wordpress documentation I should add (or over-ride) parameters in the following way:

global $query_string;
query_posts( $query_string . '&orderby=date&order=ASC&cat=-1' );

The results are right but ordering them by date doesn't work (ASC and DESC).

Only ordering the results works fine:

global $query_string;
query_posts( $query_string . '&orderby=date&order=ASC' );

Only excluding a category, without ordering the results, works fine:

global $query_string;
query_posts( $query_string . '&cat=-1 );

Also including certain categories and ordering the results works fine:

global $query_string;
query_posts( $query_string . '&orderby=date&order=ASC&cat=2' );

Of course I can work around it by building an array of the right categories first, then use these categories in the query_posts. Something like the following:

$include = array();

$categories = get_categories( array('exclude' => 1) );

foreach ( $categories as $category ) {
    $include[] = $category->term_id;
}

But I can't figure out why the combination of excluding a category and ordering by date doesn't work when using query_posts.

Tested with versions 3.9.1 and 4.0.1 of Wordpress. Both giving same results.

Is this a bug in Wordpress or is it something in my code?

EDIT:

Tested WP_Query and pre_get_posts based on comments but both returning same results as query_posts.

WP_Query example

$args = array(
    'category__not_in'  => array(1),
    'post_type'         => 'post',
    'orderby'           => 'date',
    'order'             => 'ASC',
);

// The Query
$query = new WP_Query( $args );

pre_get_posts example

function exclude_category( $query ) {
    if ( $query->is_home() && $query->is_main_query() ) {
        $query->set( 'orderby', 'date' );
        $query->set( 'order', 'ASC' );
        $query->set( 'cat', '-1' );
    }
}
add_action( 'pre_get_posts', 'exclude_category' );

Everything works fine until a category is excluded.

`query_posts` is problematic and not the recommended way to alter a query (see http://codex.wordpress.org/Function_Reference/query_posts). Use `pre_get_posts` instead. — diggy, Dec 16 '14 at 12:29
Getting same results with pre_get_posts. Only ordering or only excluding a category works fine, but when using a combination of both, the ordering doesn't work anymore. — vicente, Dec 16 '14 at 12:38

score 3 · Accepted Answer · edited May 23 '17 at 12:07

After some research I think the problem is caused by the generated SQL query (which is the same using query_posts or WP_Query or pre_get_posts).

When ordering by date and excluding a category with the following code:

query_posts( $query_string . '&orderby=date&order=ASC&cat=-1' );

The following query is executed:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
WHERE 1=1 
AND ( wp_posts.ID NOT IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (1) ) ) 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date ASC 
LIMIT 0, 10

In this case the ORDER BY doesn't work in combination with GROUP BY. You can find more info on this in other questions on Stackoverflow. For example: Using ORDER BY and GROUP BY together

I think the best workaround is to first build an array of categories which needs to be included. Then use this array in query_posts:

$include = array();

// Get all categories except category_id 1 (or other).
$categories = get_categories( array('exclude' => 1) ); 

// Loop categories to build array with category id's
foreach ( $categories as $category ) {
    $include[] = $category->term_id;
}

// Query posts: order by date and only include categories from array
query_posts( $query_string . '&orderby=date&order=ASC&cat=' . implode(',',$include) );

The generated query will be (2 and 3 are the included category id's):

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
WHERE 1=1 
AND ( wp_term_relationships.term_taxonomy_id IN (2,3) ) 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date ASC
LIMIT 0, 10

In this case the ordering by date will be working fine because of the different query buildup (using INNER JOIN).

score 0 · Answer 2 · answered Dec 16 '14 at 13:04

0

You should try declaring a new WP_Query instead of using query_posts().

answered Dec 16 '14 at 13:04

Tiki-Web

106
8

No luck. WP_Query giving same result as query_posts and pre_get_posts. Ordering by date doesn't work when excluding a category. – vicente Dec 16 '14 at 13:24

Wordpress query_posts order by date when excluding a category

2 Answers2