wp query - WP_QUERY with group by with custom fields

admin管理员组

文章数量:1426292

I need to use group by in my posts with a custom field "eventMonth" that will have values like "April-2019" - "June-2019"..

(I never used the wp_query with a meta_query data so...). I've the next query and code in my category fieldfunction

query_group_by_filter($groupby){
    global $wpdb;
    return $wpdb->postmeta . ".meta_value='eventMonth'";
 }:

$query = array(
    "category_name"=>"events",
    "posts_per_page"=>-1,
    "order"=>"ASC",
    "meta_query"=>array(
        'relation'=>"OR",
        array(
            'key'=>"eventMonth",
            'compare'=> "="
        )
    ),
    'meta_key'=>"eventMonth"
);

add_filter( 'posts_groupby', 'query_group_by_filter' );

If I make the loop, I can see only 1 result when I've four posts with this custom field.

How could I do that group by?

Thanks for the help!

I need to use group by in my posts with a custom field "eventMonth" that will have values like "April-2019" - "June-2019"..

(I never used the wp_query with a meta_query data so...). I've the next query and code in my category fieldfunction

query_group_by_filter($groupby){
    global $wpdb;
    return $wpdb->postmeta . ".meta_value='eventMonth'";
 }:

$query = array(
    "category_name"=>"events",
    "posts_per_page"=>-1,
    "order"=>"ASC",
    "meta_query"=>array(
        'relation'=>"OR",
        array(
            'key'=>"eventMonth",
            'compare'=> "="
        )
    ),
    'meta_key'=>"eventMonth"
);

add_filter( 'posts_groupby', 'query_group_by_filter' );

If I make the loop, I can see only 1 result when I've four posts with this custom field.

How could I do that group by?

Thanks for the help!

• wp-query
• custom-field

Share Improve this question asked May 13, 2019 at 7:35 MarcosMarcos 18722 silver badges1212 bronze badges 2

• OK, and what are the values of eventMonth for those 4 posts? – Krzysiek Dróżdż Commented May 13, 2019 at 7:59
• @KrzysiekDróżdż 2 have "April-2019" and others have "June-2019" and "May-2019"...I supose that this query should give this 4 post but applying the group by, isn't it? – Marcos Commented May 13, 2019 at 9:47

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

(Revised answer)

So after discussing via the comments, what you really need is a custom ORDER BY clause, which in WordPress post queries (WP_Query), you can set the clause via the posts_orderby filter just like you can set custom GROUP BY clause via the posts_groupby filter.

The order that I need is June - 2019 | May - 2019 | April - 2019 | April - 2019

If the value of the meta eventMonth is always in this format: {month name} - {4-digit year} such as June - 2019 — note the dash/hypen which shouldn't be "–" or any other special characters, then the following ORDER BY clause can sort the posts in the order specified above: (the WordPress table prefix is assumed as being wp_ and this example sorts in descending order)

ORDER BY STR_TO_DATE( REPLACE( wp_postmeta.meta_value, '-', '1,' ), '%M %e, %Y' ) DESC

And the PHP code:

1. Modify the ORDER BY clause:

   add_filter( 'posts_orderby', 'query_order_by_filter', 10, 2 );
   function query_order_by_filter( $orderby, $query ) {
       // Modify only if and when applicable.
       if ( 'eventMonth_date' === $query->get( 'orderby' ) ) {
           global $wpdb;
           $order = $query->get( 'order' );
           return "STR_TO_DATE( REPLACE( $wpdb->postmeta.meta_value, '-', '1,' ), '%M %e, %Y' ) $order";
       }
   
       return $orderby;
   }
   

2. When making your post/WP_Query queries, set the orderby parameter to eventMonth_date:

   $args = array(
       'meta_key' => 'eventMonth',
       'orderby'  => 'eventMonth_date',
       'order'    => 'DESC',
       // ... other args.
   );
   $query = new WP_Query( $args );
   

   PS: If you use get_posts() instead of new WP_Query(), make sure to set suppress_filters to false.

本文标签： wp queryWPQUERY with group by with custom fields