1

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.: tags="foo,bar,bla"

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 _cid

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, taxonomy=category

An Example of my ideal end result row

Would look something like

| _pid | _cid | _tags |
| 10   |   3  | foo,bar,bla|
-1
SELECT DISTINCT
  p.ID            AS id,
  p.post_title    AS title,
  p.post_author   AS user,
  p.post_content  AS content,
  p.post_excerpt  AS intro,
  p.post_status   AS status,
  p.post_date     AS created_at,
  p.post_modified AS updated_at,
  (SELECT group_concat(p.guid SEPARATOR ', ')
   FROM wp_postmeta pm
     LEFT JOIN wp_posts p ON pm.meta_value = p.ID
   WHERE pm.post_id = 17917 AND pm.meta_key = '_thumbnail_id' AND p.post_type = 'attachment'
  )               AS image,
  (SELECT group_concat(pm.meta_value SEPARATOR ', ')
   FROM wp_posts p
     LEFT JOIN wp_postmeta pm ON pm.meta_key = 'views' AND pm.post_id = p.ID
   WHERE p.ID = 17917
  )               AS views,
  (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,
  (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 = 'post_tag' AND p.ID = tr.object_id
  )               AS tag
FROM wp_posts p
WHERE p.post_type = 'post'
ORDER BY p.post_date DESC
LIMIT 3
  • Again, please explain your answer. Code dumps without any explanation are not good answers. See How to Answer. – fuxia Apr 11 '17 at 9:33

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.