WordPress.org

1. LoB
   Member
   Posted 2 years ago #

   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

2. Kafkaesqui
   Moderator
   Posted 2 years ago #

   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

3. LoB
   Member
   Posted 2 years ago #

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

   LoB

4. Kafkaesqui
   Moderator
   Posted 2 years ago #

   SELECT DISTINCT wp_posts.*

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

5. elbuenob
   Member
   Posted 1 year ago #

   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

Topic Closed

This topic has been closed to new replies.