I am trying to select all posts from a WordPress database, include few fields on each post, but most importantly I would like to also select the "category id" or each post, and ALL the tags that belong to this post, as a CSV value, i.e.:
So far I am using 2 queries, but I would like to combine them if possible.
I can select all the tags like this
SELECT wp_terms.name as _name, # get the tag name wp_term_relationships.object_id as _pid # get the post id that it belongs to FROM wp_terms LEFT JOIN wp_term_relationships ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id LEFT JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id WHERE wp_term_taxonomy.count > 0 AND wp_term_taxonomy.taxonomy = "post_tag"
Then I select all posts
but also figure out to which category each post belongs to, note the
SELECT wp_posts.ID as _pid, wp_terms.term_id as _cid # that's the category id FROM wp_posts # all this to get the _cid 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_term_taxonomy.taxonomy = "category" # filter the terms by category taxonomy AND wp_posts.post_type="post" AND wp_posts.post_status="publish"
Then in my code logic, I create a map of all posts using the post ids as keys, then add an array or a string value, call it
tags, then I push the tags that belong to that post id on it. It works, but it's not as efficient, since I have millions of posts, and I would like to support pagination using
LIMIT, I would still have to query all tags all the time.
I attempted using
GROUP_CONCAT but I failed to get what I want. The main problem I am facing is that both, the category name and the tags are in reality just terms, but I need to concat all the terms of one of them, the
taxonomy=post_tag, but not the other,
An Example of my ideal end result row
Would look something like
| _pid | _cid | _tags | | 10 | 3 | foo,bar,bla|