wpdb Custom Meta Data with 2 conditions

Question

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...

Answer 1

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' );

Answer 2

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
        }
    }