1

How can I retrieve posts under a category with plain SQL with out using WordPress native api's like get_pages?

My Wordpress table wp_terms contains a term named Restaurants.

I want to list the post comes under this term only.

Is the below query correct?

Am I correct on the JOIN parts?

SELECT p.ID AS postId, 
    p.post_name,
    p.post_title, 
    p.post_content,
    p.post_excerpt,
    p.post_status,
    p.post_type,
    p.post_author,
    p.guid,
    p.post_modified_gmt
FROM `wp_posts` AS p
LEFT JOIN `wp_term_relationships` AS tr ON p.ID = tr.object_id
LEFT JOIN  `wp_term_taxonomy` AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE post_type='place' AND post_status='publish' AND tt.term_id = 11
ORDER BY postId DESC
4
  • What's the reason for "with out using WordPress native APIs" ?
    – kaiser
    May 6, 2013 at 10:55
  • are you not able to get your desired results from wordpress get_posts??
    – Kumar
    May 6, 2013 at 16:09
  • Guys. I'm developing an api with .NET which pulls data from a WordPress database. And I don't want to use WordPress API or PHP May 7, 2013 at 6:06
  • Does your query work as expected? If not look over core.trac.wordpress.org/browser/tags/3.5.1/wp-includes/… for exact query construction and joins.
    – soulseekah
    May 8, 2013 at 8:45

1 Answer 1

-1
SELECT p.ID AS postId, 
    p.post_name,
    p.post_title, 
    p.post_content,
    p.post_excerpt,
    p.post_status,
    p.post_type,
    p.post_author,
    p.guid,
    p.post_modified_gmt,
    (SELECT group_concat(t.name SEPARATOR ', ')
   FROM wp_terms t
     LEFT JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
     LEFT JOIN wp_term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
   WHERE tt.taxonomy = 'category' AND p.ID = tr.object_id
  )               AS category
FROM `wp_posts` AS p
LEFT JOIN `wp_term_relationships` AS tr ON p.ID = tr.object_id
LEFT JOIN  `wp_term_taxonomy` AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE post_type='place' AND post_status='publish' AND tt.term_id = 11
ORDER BY postId DESC
1
  • Please edit your answer, and add an explanation: why could that solve the problem?
    – fuxia
    Apr 11, 2017 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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