Wordpress query to inner join posts -> custom table -> post meta where metakey = metavalue

Question

I need to join POSTS with CUSTOM TABLE and POSTMETA. I am keeping track of popular posts in the custom table but I only want posts returned that have a specific postmeta value.

I searched and could not find a tut.

Here is what I 'think' I should do... but it doesn't work when done by hand in phpmyadmin.

SELECT (post info) FROM posts p INNER JOIN custom_table t ON p.ID = t.ID INNER JOIN post_meta m ON p.ID = m.ID WHERE m.metakey = 'mykey' AND post_type = 'post' AND post_date < '$now' AND post_date > '$lastmonth' ORDER BY postcount DESC LIMIT 5");

Do I need to inner join the post meta as a separate sub query?

pp19dd pp19dd · Accepted Answer · 2011-03-29T16:01:03

If I might suggest, try using WP_Query(). It'll be a bit clumsy, since you'll need to add a filter for the post date range and then remove it, but it'll otherwise be predictably functional without a three-layer SQL join.

<?php
include_once( "wp-config.php" );

function filter_date_range( $where = '' ) {
    $lastmonth = date("Y-m-d 00:00:00", strtotime("-1 month"));
    $where .= " and post_date<now() and post_date>'{$lastmonth}'";
    return( $where );
}

add_filter( 'posts_where', 'filter_date_range' );

$q = new WP_Query(array(
    "post_status" => "publish",
    "post_type" => "post",
    "posts_per_page" => 5,
    "meta_query" => array(array(
        "key" => "mykey",
        "value" => "my_preferred_value"
    ))
));

remove_filter( 'filter_date_range' );

var_dump( $q->posts );

?>

Wordpress query to inner join posts -> custom table -> post meta where metakey = metavalue

1 Answers