1

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?

3 Answers 3

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;
}
2
  • This is valid, but expensive: it's going to do one subquery per row found. Better group by and filter using a having clause imho. Dec 6, 2010 at 18:48
  • @Denis: Ah, probably a better query optimizer would be able to detect this and convert it to a join. It appears this was planned for MySQL 6.0, I don't know what happened to it.
    – Jan Fabry
    Dec 6, 2010 at 22:39
1

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.

4
  • But what happens when you already have a GROUP BY in your query, because you filter by tag? Then the terms will be joined with the posts, resulting in an unknown number of duplicates, so your HAVING might trigger incorrect posts. (Try it with a post that has only one krit_karakter, but five tags)
    – Jan Fabry
    Dec 6, 2010 at 22:11
  • that's the reason I suggested splitting it in two functions. The having clause needs to go last, so the priority needs to be large enough to accommodate other plugins. WP uses it like GROUP BY $groupby, if memory serves. Dec 7, 2010 at 10:13
  • Indeed, the HAVING still comes at the end, but my example of one post with five tags and one krit_karakter will still be returned while it should not be, if the query includes a join with the taxonomy tables (which it will if you filter by tag too).
    – Jan Fabry
    Dec 9, 2010 at 11:54
  • Good point. It should be something like: HAVING COUNT(DISTINCT postmeta.post_id) >= 5 in case there are other joins. Dec 9, 2010 at 17:27
0

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.

3
  • Custom fields with the same key can be added to a post, i think what the user is asking is how to find posts that have at least 5 custom fields with a particular key, which unfortunately is outside the capabilities of the WP_Query class..
    – t31os
    Nov 27, 2010 at 9:19
  • Yep, probably just as well too complex for querying. Storing count separately applies to that scenario as well.
    – Rarst
    Nov 27, 2010 at 12:29
  • Yes, I mean what t31os said. I don't have to use the WP-Query, I can also use an SQL-query.
    – Martin-Al
    Nov 28, 2010 at 14:43

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.