WP Query Group By

1 Views

I have a question about WP_Query. Let’s say you have a large number of posts in a custom post type with custom fields. Some of those custom fields are address information like street address, city and state. What I want to do is display a list of all states and how many posts are in each state.

I’ve succeeded at getting the number of results per state, but I am seeing multiple listings of the state. If there are 3 records in WV, i am seeing WV listed three times. That is consistent for each state.

Read More

I am attempting to do something like a “GROUP BY” but i am not having any success with that.

I blame part of my confusion on that it is a custom field that is holding the address so i’m not sure how to group them by the state.

Here is my code: 

$stateqry = array( 
'post_type' => 'plf', 
'posts_per_page' => -1,
'orderby' => 'meta_value',
'meta_key' => 'state',
'order' => 'DESC',
'status' => 'published'
);

$loop = new WP_Query( $stateqry );
while ( $loop->have_posts() ) : $loop->the_post();

 $firmName = get_the_title($post->ID);
 $profileLink = get_permalink($post->ID);
 $custom = get_post_custom($post->ID);
 $addressLine1 = $custom["addressLine1"][0];
 $addressLine2 = $custom["addressLine2"][0];
 $city = $custom["city"][0];
 $state = $custom["state"][0];

// get the PLF count by state
$getPLFs = $wpdb->get_results("SELECT * FROM $wpdb->postmeta 
WHERE meta_key = 'state' AND meta_value = '$state' 
GROUP BY meta_value" );

Then I am trying to do a foreach: 

<?php foreach($getPLFs as $plf) { 
    $state_count = $wpdb->get_var("SELECT COUNT( meta_value ) FROM $wpdb->postmeta WHERE meta_key = 'state' AND meta_value = '$state' " );
?>
<div class="statebox <?php echo $state; ?>">
    <h1><?php echo $state; ?>: <?php echo $state_count; ?></h1>
</div>
<?php } ?>
<?php endwhile; wp_reset_query(); ?>

Anyone have some suggestions on how i fix my query to group the states together and show a count of the number of posts in each state?

Post Views: 1

Related posts

Leave a Reply

1 comment

  1. Unless you need all the other details, I’d do this in a single query, not using WP_Query or the loop at all. Something like (this is untested, as I don’t currently have access to a test system):

    $plfsByState = $wpdb->get_results("
  select pm.meta_value as state
       , count(*) as postcount
    from $wpdb->posts p
         join $wpdb->postmeta pm on p.ID = pm.post_id
   where p.post_type = 'plf'
     and p.post_status = 'publish'
     and pm.meta_key = 'state'
group by pm.meta_value
order by pm.meta_value DESC" );

    Then just loop over the results (again untested):

    <?php foreach($plfsByState as $plf) { ?>
<div class="statebox <?php echo $plf->state; ?>">
    <h1><?php echo $plf->state; ?>: <?php echo $plf->postcount; ?></h1>
</div>
<?php } ?>