Custom author query

<?php
//return all posts for author_id=1 with custom field 'cf1', but exclude any posts that have custom key value of cf1_value
$author_id = 1;
$args=array(
  'author' => $author_id,
  'meta_value'=> 'cf1_value',
  'meta_key'=>'cf1',
  'meta_compare'=>'!=',
  'post_type' => 'post',
  'post_status' => 'publish',
  'posts_per_page' => -1,
  'caller_get_posts'=> 1
);
$my_query = null;
$my_query = new WP_Query($args);
if( $my_query->have_posts() ) {
  echo 'List of Posts';
  while ($my_query->have_posts()) : $my_query->the_post(); ?>
    <p><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title_attribute(); ?>"><?php the_title(); ?></a></p>
    <?php
  endwhile;
}
wp_reset_query();  // Restore global post data stomped by the_post().
?>

Note that code causes this SQL to be used:

SELECT   wp_posts.* FROM wp_posts
JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
WHERE 1=1  AND (wp_posts.post_author = 1)
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish')
AND wp_postmeta.meta_key = 'cf1'
AND wp_postmeta.meta_value != 'cf1_value'
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC

Thanks for your help, Michael. Unfortunately that doesn’t quite work I don’t think, because I’m looking for all posts by say, user 1, but not the posts that even have a meta_key of ‘cf1’. So if there are three posts by user 1, one of which has a meta_key of ‘cf1’ (regardless of meta_value), then only two posts should be returned.

Perhaps there is a good way to do this in one post, but I’m thinking that I might need to get all the posts by user 1, then join the result separating IDs with commas, then get all the posts that have a meta_key of ‘cf1’ in the ID list that I just created. Then do an array_diff of IDs for the ones without the ‘cf1’ meta_key.

Easier way?

I was afraid you’d say that ??

Could just get all posts for user 1, then in the loop do this:

<?php
$cf1='';
$cf1 = get_post_meta($post->ID, 'cf1', true);
if (!$cf1){
//do you post display stuff
}
?>

Of course if you need proper pagination that wouldn’t work.

Perhaps I’m missing something, and I’ve never really done pagination before (at the query level, at least), but how would that work/not work with regards to pagination? I tried digging through core earlier to understand how it really does pagination, but I quickly got lost in wp-db.php…

Also, the downside to that approach is that I’ll be hitting the
database constantly, right?

I’m going to try my multi-step approach above, though that might very well fail with pagination as well. Will report back here when I’ve got something.

how would that work/not work with regards to pagination

Because you are filtering “outside” the query.

Also, the downside to that approach is that I’ll be hitting the database constantly, right?

Not sure how bad it would be…but here’s the SQL to get user 2 posts where those posts don’t have cf1 as a custom field (didn’t test this too much)

$no_cf1_posts_user2 = $wpdb->get_results("SELECT   wp_posts.* FROM wp_posts
WHERE 1=1 AND (wp_posts.post_author = 2)
    AND wp_posts.post_type = 'post'
    AND (wp_posts.post_status = 'publish')
    AND wp_posts.ID NOT IN (
    SELECT DISTINCT wp_postmeta.post_id
    FROM wp_postmeta
    WHERE wp_postmeta.meta_key = 'cf1')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC");

Wow, thank you – that’s almost exactly it. Whew, this is a doozy! Here’s your query above touched up a bit and simplified to work seemingly perfectly for my purposes:

$attachments = $wpdb->get_results( $wpdb->prepare( "
		SELECT *
		FROM $wpdb->posts
		WHERE post_author = %d
			AND ID NOT IN (
				SELECT DISTINCT post_id
				FROM $wpdb->postmeta
				WHERE meta_key = 'media-credit')
			AND post_type = 'attachment'
			OR (post_type = 'post'
				AND post_parent = '0'
				AND post_status = 'publish')
		GROUP BY ID
		ORDER BY post_date DESC" ,
		$curauth->ID ));

Unfortunately, it’s not exactly the most optimal query, as you suspected. The much more verbose code that follows is slightly better because it’s more specific about which rows in postmeta to look at. In my veryyyy informal speed tests, the following runs about 30% faster:

/**
 * Joins fields from an array of objects, returning a string with the fields separated by the given separator.
 */
function joinKeys($toJoin, $key, $separator) {
	for ($i = 0; $i < count($toJoin); ++$i)
		$ids .= $toJoin[$i]->$key . ($i < count($toJoin) - 1 ? $separator : '');
	return $ids;
}

// media inline with posts
$all_attachments = $wpdb->get_results( $wpdb->prepare( "
			SELECT ID
			FROM $wpdb->posts
			WHERE post_author = %d
				AND (post_type = 'attachment'
					OR (post_type = 'post'
						AND post_parent = '0'
						AND post_status = 'publish')
				)
			ORDER BY post_date DESC",
			$curauth->ID ));

$all_ids = joinKeys($all_attachments, 'ID', ',');

$freeform = $wpdb->get_results( "
			SELECT post_id
			FROM $wpdb->postmeta
			WHERE meta_key = 'media-credit'
				AND post_id IN ($all_ids)" );

$freeform_ids = joinKeys($freeform, 'post_id', ',');

$attachments = $wpdb->get_results( "
			SELECT *
			FROM $wpdb->posts
			WHERE ID IN ($all_ids)
				AND ID NOT IN ($freeform_ids)
			ORDER BY post_date DESC" );

I then use this to display it:

<?php if ($attachments) : ?>
<div id="posts-and-media">
	<h3>Posts and images by <?php echo $curauth->display_name; ?></h3>
	<ul>
	<?php foreach ($attachments as $post) : ?>
		<li>
			<?php setup_postdata($post);
			if ( $post->post_type == 'attachment' )
				echo wp_get_attachment_link($post->ID, 'thumbnail');
			else
				echo the_title();
			?>
		</li>
	<?php endforeach; ?>
	</ul>
</div>
<?php endif; ?>

A) Any suggested optimizations to the above?
B) Is there a way to take advantage of the Loop instead of my custom looping? I’m not that familiar with how the Loop is made, or thus how to make one myself…

a. you’re way beyond me now ??
b. your setup_post data is fine — a loop is usually a result of query_posts or new WP_Query

Is there a way to create a WP_Query from SQL code rather than using an args array?

So you’re wanting posts(attachments, or whatever), created by one particular user where a particular meta key does not exist for that post, is that correct?

This is an example query, but perhaps you can take away something from it..

SELECT * FROM wp_posts
WHERE ( 1=1
AND NOT EXISTS
( SELECT * FROM wp_postmeta WHERE meta_key = 'fruit' AND wp_postmeta.post_id = wp_posts.ID )
AND wp_posts.post_status = 'publish'
AND wp_posts.post_author = 1
AND wp_posts.post_type = 'post'
)
GROUP by ID
ORDER by post_date DESC

..you’d replace fruit with the appropriate meta key… etc…

I’m no mysql guru, so i can’t say it’s 100% perfect, but thought i’d give it a shot..