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.

This is one of those problems that I've spent way too much time on, and there is probably a VERY simple solution that I am missing. Any help would be greatly appreciated.

I am trying to populate a select box with names from custom data (meta_key "pyd_pub_author"). But, I cant just call the data directly, because it also needs to make sure that, meta_key "pyd_pub_type" = "Newsletter"

The meta_value for meta_key "pyd_pub_type" needs to be "Newsletter" and the author names needs to come from meta_key "pyd_pub_author". The tricky part is, there are many other "pyd_pub_authors" that are associated with other "pyd_pub_type", I need to filter them out as well.

So, I need to filter out the meta_value "Newsletter" and all "pyd_pub_author" that are associated with it.

What I've done is this:

 $metakey       = $prefix . 'pub_author';
    $authors_query = $wpdb->get_col(
        $wpdb->prepare(
            "SELECT DISTINCT meta_value, post_id
             FROM $wpdb->postmeta
             WHERE meta_key = %s
             HAVING post_id = ANY (
             SELECT DISTINCT post_id
             FROM $wpdb->postmeta
             WHERE meta_value = 'Newsletter'
             )
            ", $metakey
        )
    );

This returns the name of an author for each of the post_id(s) that match the list, even if they are duplicate. The "DISTINCT" isnt filtering the names because it is checking the post_id.
So, what this leaves me to to is run an array_unique() to strip out the duplicates. It works, but there has to be an easier, less expensive way to do this.

head->desk, head->desk, head->desk...

share|improve this question
    
+1 for "head->desk, head->desk, head->desk" :) –  kaiser Mar 8 '12 at 14:14
add comment

2 Answers 2

You can use the posts_clauses filter to intercept the query and change whatever you need. It also gives you imho better insights about the whole query parts and an easier way to debug.

function debug_query( $pieces )
{
    $dump  = '<pre>';
    $dump .= var_export( $pieces, FALSE );
    $dump .= '</pre>';

    echo $dump;
}
add_filter( 'posts_clauses', 'debug_query' );
share|improve this answer
    
Thank you, I haven't used 'posts_clauses' before, I will read up on it. After a night of sleep, i found my mistake. I will post it below. –  mgyura Mar 8 '12 at 15:09
add comment
up vote 0 down vote accepted

I found my error in the above query. There still may be a better way to do this, thank you kaiser, and I will update once I've done more study. What I forgot to do was GROUP_CONCAT another meta_value so i could pull out different bits. It still seems longer than it needs to be, but it gets the job done fast. Here is the code, just wrap this in a form and select and you can have a drop down of unique meta_values with a few filters.

    $metakey       = $prefix . 'pub_author';
    $authors_query = $wpdb->get_col(
        $wpdb->prepare(
            "SELECT meta_value, post_id, GROUP_CONCAT(meta_value)
                             FROM pyd_postmeta
                             WHERE meta_key = %s
                             GROUP BY meta_value
                             HAVING post_id = ANY (
                             SELECT post_id
                             FROM pyd_postmeta
                             WHERE meta_value = 'Newsletter'
                             )
            ", $metakey
        )
    );

    if ( $authors_query ) {
        foreach ( $authors_query as $author ) {
            ?>
        <option value="<?php echo $author ?>" <?php selected( $pydnet_show_author, $author ) ?> ><?php echo $author ?></option>';
        <?php
        }
    }
share|improve this answer
    
Please mark this as solution. –  kaiser Mar 15 '12 at 17:03
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.