Query on custom field count?

Question

I have the following code:

            <div id="pc" class="smartbar-content">
            <ul class="smartbar-items">
            <?php
            $paged = ( get_query_var('paged') ) ? get_query_var('paged') : 1;
            query_posts( array(
                        'cat' => '6', 
                        'posts_per_page' => 5,
                        'meta_key' => 'krit_karakter',
                        'orderby'=> 'menu_order',
                        'order'=> 'DESC',
                        'caller_get_posts' => 1, 
                        'paged' => $paged ) );
                    if (have_posts()) : while (have_posts()) : the_post(); ?>
                    <li><a href="<?php the_permalink(); ?>"><img src="<?php bloginfo('url');?>/wp-content/files_mf/<?php get_custom_field('game_poster', TRUE); ?>" width="80" height="110" alt="<?php the_title();?>" /> </a>
                    <br /><?php the_title();?></a></li>

                    <?php endwhile; ?>
                    <?php else : endif; ?>
            </ul>
            <?php wp_reset_query(); ?>
        </div>

I want to show only posts that have at least 5 entries in the custom field 'krit_karakter'.

Is that possible?

Answer 1

The following code allows you to add the parameters wpse4528_key and wpse4528_minimum to your queries, and will then add an extra criterium to the where clause that counts the number of meta values with your key.

add_filter( 'posts_where', 'wpse4528_posts_where', 10, 2 );
function wpse4528_posts_where( $where, &$wp_query )
{
    global $wpdb;
    $count_key = $wp_query->get( 'wpse4528_key' );
    if( $count_key ) {
        $where .= $wpdb->prepare( " AND (SELECT COUNT(*) FROM {$wpdb->postmeta} WHERE {$wpdb->postmeta}.post_id = {$wpdb->posts}.ID AND {$wpdb->postmeta}.meta_key = %s) >= %d", $count_key, $wp_query->get( 'wpse4528_minimum' ) );
    }
    return $where;
}

Answer 2

You need to use the posts_groupby filter, twice. They'll probably look something like this:

function add_my_groupby($groupby, &$query) {
  global $wpdb;
  $groupby .= "$wpdb->posts.ID";
  return $groupby;
}

function add_my_having($groupby, &$query) {
  $groupby .= " HAVING COUNT(*) >= 5";
  return $groupby;
}

Then, you need to do something like so in your template:

add_filter('posts_groupby', 'add_my_groupby', 10, 2);
add_filter('posts_groupby', 'add_my_having', 1000000, 2);

query_posts( array(/* your stuff...*/) );

remove_filter('posts_groupby', 'add_my_groupby', 10);
remove_filter('posts_groupby', 'add_my_having', 1000000);

(Note the extremely large number passed for the second filter's priority. It is meant to work around the lack of posts_having filter in the WP API.)

Alternatively, don't bother with the query_posts() garbage and query the DB directly, e.g.:

$wpdb->get_results("
  SELECT posts.*
  FROM $wpdb->posts as posts
  JOIN $wpdb->postmeta as postmeta
  ON postmeta.post_id = posts.ID
  AND postmeta.meta_key = 'whatever'
  GROUP BY posts.ID
  HAVING COUNT(*) >= 5
  ORDER BY posts.post_date DESC
  LIMIT 5
");

---

Update: As point out by Jan, it should be something like: HAVING COUNT(DISTINCT postmeta.post_id) >= 5 in case there are other joins.

Answer 3

Do I understand right that you store some kind of array in custom field?

Arrays are serialized (turned into plain text) when WordPress stores them in database. Since query works on MySQL level it cannot run any meaningful operations on such serialized data.

What you can do is store count (non-array value) you want to compare against in separate field and use meta_compare argument to filter.