Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I need to get the last posts' ID and the post_tile within categories 1 and 2, and exclude those within category 3. It must be SQL, because if I use WP_Query it retrieves too much info and I only need the ID and post_title.

The following query works fine getting posts from the 2 categories, but if I try excluding the third one, it doesn't work:

select distinct p.ID, p.post_title
            from wp_posts p, 
            wp_terms t, wp_term_taxonomy tt, wp_term_relationships tr,
            wp_terms t2, wp_term_taxonomy tt2, wp_term_relationships tr2

            where p.id = tr.object_id
            and t.term_id = tt.term_id
            and tr.term_taxonomy_id = tt.term_taxonomy_id

            and p.id = tr2.object_id
            and t2.term_id = tt2.term_id
            and tr2.term_taxonomy_id = tt2.term_taxonomy_id

            and (tt.taxonomy = 'category' and tt.term_id = t.term_id and t.term_id = 15)
            and (tt2.taxonomy = 'category' and tt2.term_id = t2.term_id and t2.term_id = 5)
            ORDER BY p.ID DESC
            LIMIT 0, 60

Any help?

Thanks in advance :)

share|improve this question
    
You can use the fields parameter to restrict the fields returned. –  Steve Buzonas Feb 19 '13 at 20:46
add comment

2 Answers

Thank you very much Steve and Max for your replys :)

I tried the query that Max answered me but it retrieves the posts that are IN (15 OR 5) AND NOT IN (1000). But what I needed is: IN (15 AND 5) AND NOT IN (1000).

Finally, I used WP_Query using the parameter 'fields' => 'ids', getting the post_title after with get_the_title($post->ID).

So, the query that finally works for me is:

<?php
$query = array(
    'post_status' => 'publish',
    'order' => 'DESC',
    'orderby' => 'ID',
    'posts_per_page' => '60',
    'post_type' => 'post',
    'category__not_in' => '47',
    'category__and' => array(5,15),
    'fields' => 'ids'
);

Thank you very much!

share|improve this answer
    
That's impossible my code does not work. It was tested. Also, term_id IN(15, 5) is equal to WHERE term_id = 15 AND term_id = 5. Anyhow, WP_Query is better as it's native. Glad you've got it going. –  Max Yudin Feb 20 '13 at 7:41
add comment

You had to use LEFT JOIN to simplify the stuff. By the way, the same can be accomplished using WP_Query and I actualy don't understand why you don't want to use it.

Also I've changed the way you write the query and I've not use the hardcoded $table_prefix for answer to be useful for others too. You can replace $wpdb-> with wp_ in $query if you want to.

I assume you have third category ID=1000 (see NOT IN part):

<?php
global $wpdb;
$query = "
SELECT ID, post_title
FROM $wpdb->posts
    LEFT JOIN $wpdb->term_relationships ON (ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->term_taxonomy.term_id IN(15, 5)
    AND $wpdb->term_taxonomy.term_id NOT IN(1000)
    ORDER BY ID DESC
    LIMIT 0, 60
";
$results = $wpdb->get_results($query);
foreach($results as $result) {
    echo $result->ID;
    echo '<br />';
    echo $result->post_title;
    echo '<hr />';
}
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.