WordPress.org

1. modifiedcontent
   Member
   Posted 3 years ago #

   What does the query to get posts from one category actually look like?

   I know I'm supposed to use get_posts, but I've modified WP for a specific purpose and want to access data in the 'posts' table from outside WP.

   And I just want to understand how this type of category system works. ;-)

   The query would somehow combine data from 'term_taxonomy' and 'term_relationships', probably using INNER JOIN.

   Can someone point me to a basic example how something like this works? Where can I find it in the WP code?

2. MichaelH
   Volunteer
   Posted 3 years ago #

   Here's one example:
   http://benjaminsterling.com/wordpress-getting-all-post-between-to-dates/

3. modifiedcontent
   Member
   Posted 3 years ago #

   Thanks MichaelH! Exactly what I was looking for. :-)

   I'll include it here if you don't mind, in case the other site disappears:

   $querystr = "
   	SELECT *
   	FROM $wpdb->posts as wpost
   	INNER JOIN $wpdb->term_relationships
   	ON (wpost.ID = $wpdb->term_relationships.object_id)
   	INNER JOIN $wpdb->term_taxonomy
   	ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
   	AND $wpdb->term_taxonomy.taxonomy = 'category'
   	AND $wpdb->term_taxonomy.term_id IN (3)
   	where wpost.post_date BETWEEN SUBDATE(CURDATE(), INTERVAL 1 YEAR) and ADDDATE(CURDATE(), INTERVAL 1 DAY) ORDER BY wpost.post_date DESC
    ";
   $pageposts = $wpdb->get_results($querystr, OBJECT);

4. modifiedcontent
   Member
   Posted 3 years ago #

   What would it be for WordPress 2.2?

   It has different table names and probably structure. Can't figure it out. Found this on another thread about changes for 2.3:

   $wpdb->categories is replaced by $wpdb->terms
   $wpdb->post2cat is replaced by $wpdb->term_relationships
   $wpdb->post2cat.post_id is replaced by $wpdb->term_relationships.object_id
   $wpdb->post2cat.category_id is replaced by $wpdb->term_relationships.term_taxonomy_id
   $wpdb->categories.cat_ID is replaced by $wpdb->terms.term_id

   wp_terms now contains all the category (read: taxonomy) names and IDs.

   wp_term_taxonomy contains the term (read category) ID, a description of what type it is (category, link_category, etc), the description and the number of posts/links in that taxonomy.

   wp_term_relationships contains a lookup of an object_id (read: post ID, link ID, etc) and a term taxonomy ID (from the term_taxonomy table, which relates back to the category).

   Other refs to look out for are (especially when they are used OUT of the above context):
   cat_ID –> term_ID
   categories –> terms
   cat_name –> name

   I get this far:
   $query = "SELECT * FROM posts as wpost INNER JOIN post2cat ON (wpost.ID = post2cat.post_id) INNER JOIN term_taxonomy ON (post2cat.category_id = 3) AND term_taxonomy.taxonomy = 'category' AND term_taxonomy.term_id IN (3)";

   I have no clue what to do with term_taxonomy and probably need a different structure anyway. Can't puzzle it together.

5. modifiedcontent
   Member
   Posted 3 years ago #

   Figured it out (I think...). Something like this for posts from categories 3 and 5:

   SELECT * FROM posts LEFT JOIN post2cat ON (posts.ID = post2cat.post_id) WHERE (category_id = 3 OR category_id = 5)

Topic Closed

This topic has been closed to new replies.