Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

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;
}
share|improve this answer
    
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. –  Denis Dec 6 '10 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 '10 at 22:39
add comment

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.

share|improve this answer
    
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 '10 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. –  Denis Dec 7 '10 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 '10 at 11:54
    
Good point. It should be something like: HAVING COUNT(DISTINCT postmeta.post_id) >= 5 in case there are other joins. –  Denis Dec 9 '10 at 17:27
add comment

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.

share|improve this answer
    
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 '10 at 9:19
    
Yep, probably just as well too complex for querying. Storing count separately applies to that scenario as well. –  Rarst Nov 27 '10 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 Aleksander Nov 28 '10 at 14:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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