0

I have custom post type "store" that has two different custom fields: Coutry and City. When in the single country page I list all the stores in that country.

What I would like to do is to list all the distinct cities that have stores based on the country selected. Just to tell the user that this country has stores in these cities before I list all the stores.

I'm able to list all the cities but not the ones that are based on the country selected. I use this query that I call with a shortcode that has some other stuff there too but I need that $country to be included into that SQL query in a way that it works together with the 'city' and returns only the cities that are in that country:

$country= $atts['country'];
global $wpdb;

$query = "SELECT DISTINCT meta_value 
            FROM $wpdb->postmeta 
            WHERE meta_key = 'city'
            ;";
$cities= $wpdb->get_results( $query );

foreach( $cities as $city) :
echo'<li>'; echo $city->meta_value; echo '</li>';
endforeach;

I haven't been dealing with the SQL queries that much that I could figure this out.

So for example There are 50 stores in total and 5 of them are in USA: 3 in New York, 1 in Seattle and 1 in Miami. I would just like to have the cities listed that are in the USA like this:

Miami, New York, Seattle

So how could I query the unique meta field values from meta_key 'cities' based on the meta field value from meta_key 'country'?

Thanks in advance!

2
  • note that this would be much more performant, especially when filtering products/posts if you instead used 2 custom taxonomies. It would even give you free archive URLs and templates, as well as REST API endpoints for AJAX, and a dedicated WP Admin UI that tracks total post counts. Otherwise what you want to do will be expensive and might require multiplie queries since you need to know which posts have a meta key with the value country, then to use those post IDs to grab the meta values with city filtered by those post IDs
    – Tom J Nowell
    Commented Nov 10, 2023 at 10:21
  • 1
    Thanks Tom! Yes that is true. I just changed the structure to use taxonomies and editing the queries as we speak. Cheers!
    – chaq
    Commented Nov 10, 2023 at 11:11

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.