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
  • What's the reason for "with out using WordPress native APIs" ? – kaiser May 6 '13 at 10:55
  • are you not able to get your desired results from wordpress get_posts?? – Kumar May 6 '13 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 – Mithun Sreedharan May 7 '13 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 '13 at 8:45
-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
| improve this answer | |
  • Please edit your answer, and add an explanation: why could that solve the problem? – fuxia Apr 11 '17 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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