Custom SQL Query: Get all posts with category id and a concated list of tags on each post

Question

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|

Answer 1

    SELECT DISTINCT
      p.ID            AS id,
      p.post_title    AS title,
      (
        SELECT group_concat(p2.guid SEPARATOR ', ')
        FROM wp_postmeta pm
        LEFT JOIN wp_posts p2 ON pm.meta_value = p2.ID
        WHERE pm.post_id = p.ID AND pm.meta_key = '_thumbnail_id' AND p2.post_type = 'attachment'
      )               AS image,
      (
        SELECT group_concat(pm.meta_value SEPARATOR ', ')
        FROM wp_postmeta pm
        WHERE pm.post_id = p.ID AND pm.meta_key = 'views'
      )               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' AND p.post_status = 'publish' AND p.ID = 10
    ORDER BY p.post_date DESC
    LIMIT 3

A result like this:

| id | title     | image     | category       | tag            |
| 10 | test post | image URL | cat, cat 2 ... | tag, tag 2 ... |

Or without this AND p.ID = 10 you could get all post with image, tags, categories: