Custom select query

This query gets published posts in category 3. Change post_status to values as listed in query_posts():

<?php
$args=array(
  'cat' => 3,
  '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().
?>

Is it possible to use this to retrieve a post based on a combination of slug and category?

I know you should be able to get the post by slug alone, but the database I’m working with has some duplicate entries for slugs, so it returns more than one post. I was thinking that by querying the post slug and category id, it would narrow down the selection since that would return one result only. However, query_posts breaks when I try to do this…

… Or could you get the post ID from its title and category, then just use query_posts with that unique ID?

Here’s what I came up with so far… not sure if this is really legit / going to work in the end, but it seems to be fore the time being!

$querystr ="
SELECT wposts.*
FROM $wpdb->posts wposts
	LEFT JOIN $wpdb->term_relationships tr ON (wposts.ID = tr.object_id)
	LEFT JOIN $wpdb->term_taxonomy tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id)
	LEFT JOIN $wpdb->terms t ON (tt.term_id = t.term_id)

	WHERE wposts.post_name = '$postname_var'
	AND t.term_id = $this_cat_ID_var
	"

;

(postname and cat id having already been determined and stored as vars prior to running the query)

You would make the $args be:

$args=array(
  'name' => 'post-slug',
  'cat' => 3,
  'post_type' => 'post',
  'post_status' => 'publish',
  'posts_per_page' => -1,
  'caller_get_posts'=> 1
);

Don’t forget to use that query_posts link I gave you above.

Hi Michael,

Thanks so much for the help, but for some reason query_posts wasn’t liking those arguments. It would always return the posts by name (slug), seemingly disregarding the ‘cat’ parameter. Since there were multiple posts for a given slug, this would return multiple posts rather than just the desired one. Also, showposts and posts_per_page seemed to be disregarded as well.

Could this be because slug is supposed to be unique, so its category shouldn’t matter (as far as the function is concerned?)

Anyway, I posted the hack/workaround that seems to be working for the moment above…

I don’t have much experience playing with databases, so what I have there is pieced together from other stuff that I’ve read online.

Good point about the combination of name and cat arguments–here’s the SQL that generated–totally ignored that category:

SELECT   wp_posts.*
FROM wp_posts
WHERE 1=1
AND wp_posts.post_name = 'post-slug'
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish')
ORDER BY wp_posts.post_date DESC

Good call, that makes sense now. Is there an easy way to see the SQL that the query functions generate? That might make this easier for future reference.

I figured I would run in to some issues with the duplicate post slugs — that apparently being one of them — but I’m hoping the query I have pulling the post now will work reliably.

I hope trying to keep this format up with duplicate post slugs doesn’t end up being a foolish endeavor; I’ve only really come across one other answer about how to do it in WP: Don’t!

Thanks again for the help.

Don’t remember where I found this plugin but it put the SQL as a comment at the bottom of a page (right click and view source to see it)

<?php /*
Plugin Name: Echo Query
Version: 0.1
Description: Prints the posts query in an html comment
*/

//No rights reserved. :-)

function get_query($query) { //set the database query as a global variable
	global $query_comment;
    $query_comment = $query;
	return $query;
}

function echo_query() { //print it into the footer
	global $query_comment;
	echo "<!-- {$query_comment} -->";
}

add_filter('posts_request', 'get_query');
add_action('wp_footer', 'echo_query');

?>