Query to SELECT posts based on category

  • Dear all,

    I am using the following query to retrieve entries based on their category (actually their
    wp_term_taxonomy.term_id). However, even if I use the DISTINCT keyword, I obtain duplicate entries when several categories, for instance here 20 and 26, applies to the same entry. How should I do it?

    SELECT DISTINCT * FROM wp_posts<br>
    LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)<br>
    LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)<br>
    WHERE wp_posts.post_status = 'publish'<br>
    AND wp_term_taxonomy.taxonomy = 'category'<br>
    AND wp_term_taxonomy.term_id IN (5, 20, 26)<br>
    ORDER BY post_date DESC<br>
    LIMIT 5

Viewing 4 replies - 1 through 4 (of 4 total)

  • This works for me:

    SELECT DISTINCT wp_posts.* FROM wp_posts
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_posts.post_status = 'publish'
AND wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id IN (5, 20, 26)
ORDER BY post_date DESC
LIMIT 5
    Thread Starter lob

    (@lob)

    Thank you very much!
    Could you possibly explain me the difference?

    LoB

    SELECT DISTINCT wp_posts.*

    This forces the SELECT to behave only on the posts table in the query. A simple * will not do that.

    Hello! I’ve got a related question: I’m trying to relate a query to the taxonomy table for each ID that I pull out to see if that post is in a category. Here’s my original query:

    $dayswithposts = $wpdb->get_results(“SELECT DISTINCT DAYOFMONTH(post_date), ID
    FROM $wpdb->posts WHERE MONTH(post_date) = $thismonth
    AND YEAR(post_date) = $thisyear
    AND post_status = ‘publish’ AND post_type=’post’
    AND post_date < ‘” . current_time(‘mysql’) . ‘\”, ARRAY_N);
    if ( $dayswithposts ) {
    $counter = 0;
    foreach ( $dayswithposts as $daywith ) {

    $daywithpost[] = $daywith[0];

    //echo $daywithpost[$counter]. ” | “;
    //$counter += 1;
    }
    } else {
    $daywithpost = array();
    }

    Is there anyway to:
    – Lookup the ID to the Taxonomy table
    – Check for the category name of number
    – Only put those that pass this test into the array

    -Brandon

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Query to SELECT posts based on category’ is closed to new replies.