Filtering by Two Meta_Keys WordPress

• SOLVED

Hello - I have a business directory site with a list of locations and the price each of those locations offer. On the homepage of my site I show some statistics for the total number of locations and the average price across those locations. I am pulling these numbers from the database by use of custom fields.

price_dollars = price at location

I am able to grab the average price across all my locations using the following code - (note, some locations I do not have the price for, which is why I use the > '0')



<?php

//Get the sum of all of the Prices
$total=0;
$meta_key = 'price_dollars';//set this to your custom field meta key
$allprice=$wpdb->get_col($wpdb->prepare("SELECT meta_value FROM $wpdb->postmeta WHERE meta_key = %s", $meta_key));

foreach ($allprice as $price) {
$total_price = $total_price + $price;
}

//Count the number of locations that I have prices for
global $wpdb;
$pricecount = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->postmeta WHERE meta_value > '0' AND meta_key = 'price_dollars'");

echo '$' .round($total_price/$pricecount);

?>




This all works great. However, now I want to get the price average within a specific state. So, instead of querying the entire database I want to only query one state at a time using another custom field I have for each location called 'state_location'

I don't know how to query the database using two meta values. I have tried a few things, including creating a wp_query loop shown below that uses the same code as above, but refines the posts to a particular state, in this case 'NY'. No luck so far.

Code I tried:


<?php

$args = array(
'post_type'=> 'location',
'meta_value' => 'NY',
'meta_key' => 'state_location',
);

$new_query = new WP_Query( $args );

if( $new_query->have_posts() ):

while($new_query->have_posts()) : $new_query->the_post();

//Get the sum of all of the Prices
$total_state=0;
$meta_key_state = 'price_dollars';//set this to your custom field meta key
$allprice_state=$wpdb->get_col($wpdb->prepare("SELECT meta_value FROM $wpdb->postmeta WHERE meta_key = %s", $meta_key_state));

foreach ($allprice_state as $price_state) {
$total_price_state = $total_price_state + $price_state;
}

//Count the number of locations that I have prices for
global $wpdb;
$pricecount_state = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->postmeta WHERE meta_value > '0' AND meta_key = 'price_dollars'");

echo '$' .round($total_price_state/$pricecount_state);

endwhile;

endif;


?>





I'm not sure if that is even the right approach or if these is a easier way to do this. Thanks!

Answers (1)

2015-09-01

dimadin answers:

Why not using WP_Query? It is one query for all locations and single query for each location if you don't have object cache turned on.

This is how it can look:


<?php

$locations_args = array(
'post_type' => 'location',
'posts_per_page' => -1,
'meta_query' => array(
'relation' => 'AND',
array(
'key' => 'state_location',
'value' => 'NY',
),
array(
'key' => 'price_dollars',
),
),
);
$locations = get_posts( $locations_args );

$total_price_state = 0;
$pricecount_state = 0;

if ( $locations ) {
foreach ( $locations as $location ) {
$location_price = get_post_meta( $location->ID, 'price_dollars', true );

if ( $location_price ) {
$total_price_state = $total_price_state + $location_price;
$pricecount_state++;
}
}
}

unset( $locations );

if ( $total_price_state && $pricecount_state ) {
$locations_with_prices = round( $total_price_state / $pricecount_state );
}



---

jpequens comments:

That worked perfectly. Thank you very much!