手动检索带有JSON类别/标签的Wordpress帖子

I'm trying to manually query the wordpress database to retrieve specific post content for a json feed. In theory I want to return a number of 'Post' objects with each containing a 'tags/categories' array within it of each tag/category.

I'm currently using the following SQL query to retrieve the posts I want, however I think I am missing a relationship between the wp_term_taxonomy table, as well as this returns a unique row per tag/category:

SELECT wp_posts.post_date, wp_posts.post_content, wp_posts.post_title, wp_posts.ID, wp_terms.name
FROM wp_posts
LEFT JOIN wp_term_relationships ON ( wp_term_relationships.object_id = wp_posts.ID ) 
LEFT JOIN wp_terms ON ( wp_terms.term_id = wp_term_relationships.term_taxonomy_id ) 
WHERE 1=1
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish')
ORDER BY wp_posts.ID DESC 
LIMIT 25

Anyone got any advice on how I can achieve my goal? What I didnt want to do is do one query to get the posts, then iterate through each one and query to get and set the tag/category array - but maybe that's my only option?

If you are trying to make it possible to retrieve information from WordPress through JSON, it makes more sense to have a page in WordPress serve as the request URL:

Say the URL is yourwebsite.com/request/?posts_per_page=10; you create a page in WordPress with that slug, and then use a page template with only the following code inside it:

$parameters = array(
    'post_type' => 'post',
        'posts_per_page' => $_GET['posts_per_page']
    );
}
$requested_object = get_posts($parameters);

// if we have a request
if (!empty($requested_object)) {

    foreach ($requested_object as $post) :  setup_postdata($post); 

        // build the json request
        // you can make your arrays of tags & categories here
        $json_request[] = array(
            'id' => get_the_ID(),
            'title' => get_the_title(),
            'content' => apply_filters('the_content',$post->post_content)
        );

    endforeach;

    // return the json request
    header('Content-type: application/json');
    echo json_encode($json_request);

When your javascript requests /request/?posts_per_page=10, it will get JSON with 10 posts and all the attributes you want.

Out of interest the MYSQL query to get all post based on a tag:

SELECT * 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
LEFT JOIN wp_terms
ON wp_terms.term_id = wp_term_taxonomy.term_id
WHERE wp_terms.name = 'ford'

As in my case, I did with:

SELECT wp.ID, wp.post_title, wp.post_status, wt.slug, wtt.taxonomy, wtt.term_taxonomy_id, wtt.parent FROM wp2_posts wp
INNER JOIN wp2_term_relationships wtr ON wp.ID = wtr.object_ID
INNER JOIN wp2_term_taxonomy wtt ON wtr.term_taxonomy_id = wtt.term_taxonomy_id
INNER JOIN wp2_terms wt ON wt.term_id = wtt.term_id
WHERE wt.slug = 'tagname'
AND wp.ID IN(
SELECT wp2_posts.ID FROM wp2_posts
INNER JOIN wp2_term_relationships
ON wp2_posts.ID = wp2_term_relationships.object_ID
INNER JOIN wp2_term_taxonomy
ON wp2_term_relationships.term_taxonomy_id = wp2_term_taxonomy.term_taxonomy_id
INNER JOIN wp2_terms
ON wp2_terms.term_id = wp2_term_taxonomy.term_id
WHERE wp2_terms.slug = 'categoryname')
ORDER BY wp.ID ASC

It's bloated though, maybe someone has a sleeker way to do it.