Using DISTINCT in wp_query

Question

I need to create a system where the user first selects the state, then select the city and then see the information of the stores in that city.

For this, I created a cpt called store with 2 meta boxes: state and city.

For the first drop-down list, I'm using a wp_query with the following args:

$args = array(
    'post_type' => 'store',
    'posts_per_page' => -1,
    'meta_key' => 'state',
    'orderby' => 'meta_value', 
    'order' => ASC,
);

But it is returning repeated states, because there is more than one store in the same state.

How can I solve this? I thought about using mysql DISTINCT but do not know if it is possible.

UPDATE

The complete loop:

$args = array(
    'post_type' => 'store',
    'posts_per_page' => -1,
    'meta_key' => 'state',
    'orderby' => 'meta_value', 
    'order' => ASC,
);

 function search_distinct() { return "DISTINCT"; }
 add_filter('posts_distinct', 'search_distinct');
 $the_query = new WP_Query( $args );

 if ( $the_query->have_posts() ) {
   echo '<ul>';
   while ( $the_query->have_posts() ) {
      $the_query->the_post();
      echo '<li>' . get_custom_field('estado') . '</li>';
   }
   echo '</ul>';
 }
 wp_reset_postdata();
 remove_filter('posts_distinct', 'search_distinct');

But the filter is not taking effect

Answer 1

I haven't tried it myself, but that sounds like something for the posts_distinct or posts_groupby filters:

function search_distinct() {
    return "DISTINCT";
}
add_filter('posts_distinct', 'search_distinct');

Answer 2

I'm not sure this is the best way to achive this, but you can use a raw query like this:

$states = $wpdb->get_results("SELECT DISTINCT meta_value 
                              FROM wp_postmeta 
                              WHERE meta_key = 'state'
                              ORDER BY meta_value");

The above will return an array of unique state metadata values, which you then can display in a list using:

if (count($states)) {
  print '<ul>';
  foreach ($states as $state) {
    print '<li>' . $state->meta_value . '</li>';
  }
  print '</ul>';
}