Query posts WITHOUT a custom meta field

Question

I'm trying to build a custom query that will return all posts that DON'T have the associated post meta. I'm not quite sure how to do this...here is what I am working with, and for your reference "score" is a custom column that I have added to the posts table.

    $querystr = "
        SELECT $wpdb->posts.ID 
        FROM $wpdb->posts, $wpdb->postmeta
        WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id 
        AND $wpdb->posts.post_type = 'post'
        AND $wpdb->posts.post_date < NOW()
        AND $wpdb->postmeta.meta_key != '_social_'
        ORDER BY $wpdb->posts.score DESC
        LIMIT 1
     ";

    $result = $wpdb->get_row($querystr, ARRAY_A);
    add_post_meta($result["ID"], "_social_", date("Y-m-d-H-i"), true);

The above example does not work. The context of this is that I'm running a cron to auto publish a single post to my social networks each half hour, and after I do this, I want to add the post meta _social_ so that on my next cron, I do not add it to my social networks a second time. This query, I hope, will always give me the post with the highest score that has not yet been added to my social networks.

Could anyone tell me how to query all posts that exclude the _social_ custom post meta?

Answer 1

You have to do a join or a subquery:

$querystr = "
SELECT $wpdb->posts.ID 
FROM $wpdb->posts
WHERE $wpdb->posts.post_type = 'post'
AND $wpdb->posts.post_date < NOW()
AND $wpdb->posts.ID NOT IN
( SELECT DISTINCT post_id FROM $wpdb->postmeta WHERE meta_key = '_social_' )
ORDER BY $wpdb->posts.score DESC
LIMIT 1;
";

Answer 2

An alternate SQL statement from Milo's would be:

$querystr = "
  SELECT {$wpdb->posts}.ID 
  FROM {$wpdb->posts}
  LEFT JOIN {$wpdb->postmeta} ON (
    {$wpdb->posts}.ID = {$wpdb->postmeta}.post_id
    AND {$wpdb->postmeta}.meta_key = '_social_'
  )
  WHERE {$wpdb->posts}.post_type = 'post'
  AND {$wpdb->posts}.post_date < NOW()
  AND {$wpdb->postmeta}.post_id IS NULL
  /* ORDER BY {$wpdb->posts}.score DESC 
  LIMIT 1 */
";

$result = $wpdb->get_col($querystr);
var_dump($result);

I commented the last two lines because:

1. You appear to have hacked the $wpdb->posts table to include a new column-- score-- and I couldn't test that (but being an ORDER BY argument it shouldn't matter.) I am curious why you felt the need to hack the table though?
2. Your question reads "I'm trying to build a custom query that will return all posts..." With a LIMIT 1 argument, there is no way that the query will returns "all" posts. That argument will force it to return only one, no matter how many match the criteria.

I additionally changed the $wpdb method to get_col as it will get you a neat array of IDs without further hassle.