Wordpress wp_query group by custom field

Question

I have to make a search form where i have to populate a select by the states of custom posts types. Some of them have the same state, then I would like to group-by to have a clean select list of states.. I've done this:

<?php
    function  query_group_by_filter($groupby){
        global $wpdb;
        return $wpdb->postmeta.".meta_key = 'state'";
    }
?>
<?php add_filter('posts_groupby', 'query_group_by_filter'); ?>
<?php $states = new WP_Query(array('post_type' => 'observatoire')); ?>
<?php remove_filter('posts_groupby', 'query_group_by_filter'); ?>
<select id="" class="" name="siege_pays" >
     <option value=""></option>
<?php
while ( $states->have_posts() ) : $states->the_post();
echo "<option value=".get_field_object('state').">".get_field_object('state')."</option>"; 
endwhile;
?>
</select>

the result query doesn't work.. Help please :)

'SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'observatoire' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') OR wp_posts.post_status = 'private') GROUP BY wp_postmeta.meta_key = 'siege_pays' ORDER BY wp_posts.post_date DESC LIMIT 0, 10'

Answer 1

You can try below code:

<?php
    function  query_group_by_filter($groupby){
       global $wpdb;

       return $wpdb->postmeta . '.meta_key = "state"';
    }
?>
<?php add_filter('posts_groupby', 'query_group_by_filter'); ?>
<?php $states = new WP_Query(array(
    'post_type' => 'observatoire',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'meta_key' => 'state'
)); 
?>
<?php remove_filter('posts_groupby', 'query_group_by_filter'); ?>
<select id="" class="" name="siege_pays" >
     <option value=""></option>
<?php
while ( $states->have_posts() ) : $states->the_post();
echo "<option value=".get_field_object('state').">".get_field_object('state')."</option>"; 
endwhile;
?>
</select>

Query Look like:

SELECT wp_posts.*
FROM wp_posts INNER JOIN wp_postmeta 
  ON (wp_posts.ID = wp_postmeta.post_id)
WHERE wp_posts.post_type = 'observatoire'
  AND (wp_posts.post_status = 'publish')
  AND (wp_postmeta.meta_key = 'state' )
GROUP BY wp_postmeta.meta_key = "state"
ORDER BY wp_posts.post_date DESC

Answer 2

I had to make a few improvements to Chirag Patels Code, to get it working:

<?php
    function  query_group_by_filter($groupby){
       global $wpdb;

       return $wpdb->postmeta . '.meta_value ';
    }
?>
<?php add_filter('posts_groupby', 'query_group_by_filter'); ?>
<?php $states = new WP_Query(array(
    'post_type' => 'observatoire',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'meta_key' => 'state'
)); 
?>
<?php remove_filter('posts_groupby', 'query_group_by_filter'); ?>
<select id="" class="" name="siege_pays" >
     <option value=""></option>
<?php
while ( $states->have_posts() ) : $states->the_post();
echo "<option value=".reset(get_post_custom_values('state')).">".reset(get_post_custom_values('state'))."</option>"; 
endwhile;
?>
</select>