Get posts under a category with SQL

2 Views

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.

Read More

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
Post Views: 2

Related posts

1 comment 

  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

Comments are closed.