How to get an array of years from all of the search results, and use it to filter by year the paginated loop?

admin管理员组

文章数量:1332395

I hope this is not too dumb: even though I'm learning at an acceptable rate, my WP knowledge still has some important gaps.

What do I have:

My search.php template gets its results from the normal global $wp_query; $wp_query -> found_posts correctly returns the whole amount of results (say, 132) and $wp_query -> post_count, being the loop paginated, returns the correct number of posts fot that page up to the maximum of posts_per_page (say, 10).

What do I need:

I'm trying to put a filter by year <select> at the bottom of the page; of course it should allow the user to pick from the years from ALL of the 132 results and not only by the 10 in the current page.

What does trouble me:

$wp_query -> posts only holds the post_count posts pertinent to the current page, not all of the 132. If I alter the search query via a pre_get_posts filter, telling her to set posts_per_page to -1 when is_search(), of course I get all the 132 posts, but also I lose pagination.

What did I come up to so far:

I thought I'd need a "whole" query, parallel to $wp_query, holding up all of its potential results, not only the LIMITed ones. So how do I populate this elusive array of years?

1. Just to be sure to get from the very same bunch of results of $wp_query, I assign $wp_query -> request, but without its LIMIT clause, to a variable; then I $wpdb -> get_results it;
2. I pick the year from each result and I put it into the array;
3. I array_unique the array.

Conclusions

I'm pretty sure there must be a better, shorter, more efficient and maybe even "WP core"ish way to to this; but I haven't been able to get to it so far, nor to find an answer here or elsewhere.

Could any of you route me to the right direction?

Thanks!

I hope this is not too dumb: even though I'm learning at an acceptable rate, my WP knowledge still has some important gaps.

What do I have:

My search.php template gets its results from the normal global $wp_query; $wp_query -> found_posts correctly returns the whole amount of results (say, 132) and $wp_query -> post_count, being the loop paginated, returns the correct number of posts fot that page up to the maximum of posts_per_page (say, 10).

What do I need:

I'm trying to put a filter by year <select> at the bottom of the page; of course it should allow the user to pick from the years from ALL of the 132 results and not only by the 10 in the current page.

What does trouble me:

$wp_query -> posts only holds the post_count posts pertinent to the current page, not all of the 132. If I alter the search query via a pre_get_posts filter, telling her to set posts_per_page to -1 when is_search(), of course I get all the 132 posts, but also I lose pagination.

What did I come up to so far:

I thought I'd need a "whole" query, parallel to $wp_query, holding up all of its potential results, not only the LIMITed ones. So how do I populate this elusive array of years?

1. Just to be sure to get from the very same bunch of results of $wp_query, I assign $wp_query -> request, but without its LIMIT clause, to a variable; then I $wpdb -> get_results it;
2. I pick the year from each result and I put it into the array;
3. I array_unique the array.

Conclusions

I'm pretty sure there must be a better, shorter, more efficient and maybe even "WP core"ish way to to this; but I haven't been able to get to it so far, nor to find an answer here or elsewhere.

Could any of you route me to the right direction?

Thanks!

• loop
• filters
• pagination
• search

Share Improve this question asked Jul 6, 2020 at 17:51 MarksMarks 3122 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

This is really interesting one! I've tried to solve this, and here is what I ended up with.

With a pure SQL, we have an ability to get all distinct years of all our posts with the following SQL request (recipe founded here):

SELECT DISTINCT YEAR(wp_posts.post_date) FROM wp_posts

getting something like this:

mysql> SELECT DISTINCT YEAR(wp_posts.post_date) FROM wp_posts;
+--------------------------+
| YEAR(wp_posts.post_date) |
+--------------------------+
|                     2017 |
|                     2018 |
|                     2019 |
|                     2020 |
+--------------------------+
4 rows in set (0.00 sec)

But how to combine this with our main query, especially taking in account all the search criteria etc? Well, we have a posts_clauses hook where we can get all the request clauses. We need to save this clauses for later use, and we have to make sure we are dealing with the main query clauses. Here is the code (this one goes to the functions.php):

function check_query_clauses( $clauses, $query ) {
    if ( $query->is_main_query() ) {
        global $main_query_clauses;
        $main_query_clauses = $clauses;
    }
    return $clauses;
}
add_filter( 'posts_clauses', 'check_query_clauses', 10, 2 );

The $main_query_clauses array would consists of the following fields:

Array (
    [where] => ...
    [groupby] => ...
    [join] => ...
    [orderby] => ...
    [distinct] => ...
    [fields] => wp_posts.*
    [limits] => ...
)

Not all of them will be needed in the future. Next, going to your search.php template, here is the code which we'll use:

global $wpdb;
global $main_query_clauses;
$years_query =
    "SELECT DISTINCT YEAR({$wpdb->posts}.post_date) FROM {$wpdb->posts} {$main_query_clauses['join']} WHERE 1=1 {$main_query_clauses['where']}"
    . ( $main_query_clauses['groupby'] ? " GROUP BY {$main_query_clauses['groupby']}" : '' )
    . ( $main_query_clauses['orderby'] ? " ORDER BY {$main_query_clauses['orderby']}" : '' );
$years_results = $wpdb->get_results( $years_query, 'ARRAY_N' );

Note that we don't use 'limits' clause here (as well as 'fields' or 'distinct' ones). At this step we've got something like

Array (
    [0] => Array (
        [0] => 2020
    )
    [1] => Array (
        [0] => 2019
    )
    [2] => Array (
        [0] => 2018
    )
    [3] => Array (
        [0] => 2017
    )
)

The last piece of code is

$years = wp_list_pluck( $years_results, 0 );

and we are finished up with

Array (
    [0] => 2020
    [1] => 2019
    [2] => 2018
    [3] => 2017
)

Depending on your search criteria the years in array may appear in different order, so you should additionally sort it according to your needs. Voila!

Update

After you select a particular year your query would change and this code would produce an array with only one item. To fix it you'll need to remove the year filter part from WHERE SQL clause:

global $wpdb;
global $main_query_clauses;
$pattern = '/\sAND\s*\(\s*YEAR\s*\(\s*' . $wpdb->posts . '\.post_date\s*\)\s*=\s*\d+\s*\)/is';
$where = preg_replace($pattern, ' ', $main_query_clauses['where']);
$years_query =
    "SELECT DISTINCT YEAR({$wpdb->posts}.post_date) FROM {$wpdb->posts} {$main_query_clauses['join']} WHERE 1=1 $where"
    . ( $main_query_clauses['groupby'] ? " GROUP BY {$main_query_clauses['groupby']}" : '' )
    . ( $main_query_clauses['orderby'] ? " ORDER BY {$main_query_clauses['orderby']}" : '' );
$years_results = $wpdb->get_results( $years_query, 'ARRAY_N' );

本文标签：