title: How to mysql query a list of all posts and the categories they belong to (includeing the parent categories) in wordpress
body: <p>I'm developing an app that I'm basically syncing parts the wordpress database to the app.  I was hoping instead of create the entire table structure of wordpress I would create a CategoryRelationship table, which included just 2 columns (POST_ID), and (CATEGORIES_ID).  Categories ID is a comma seperated value of all categories the post belonged to (including parent categories).  Please note my wordpress database is part of a multisite hence the WP_2_ prefix.  </p>
  
  <p><strong>Attempt 1)</strong> is the following, which almost works and gives me the result closest to how I want, but doesn't seem to get the 1st parent (Please note I only have categories with max hierarchy of 3 parents:</p>
  
  <pre><code>SELECT DISTINCT ID, 
  (SELECT CONCAT_WS(',',group_concat(tt.term_id separator ','),tt.parent,tt2.term_id, tt3.term_id, tt4.term_id) 
  FROM wp_2_terms 
  JOIN wp_2_term_taxonomy tt on wp_2_terms.term_id = tt.term_id 
  JOIN wp_2_term_relationships wpr on wpr.term_taxonomy_id = tt.term_taxonomy_id 
  LEFT JOIN wp_2_term_taxonomy tt2 on tt.parent = tt2.term_id 
  LEFT JOIN wp_2_term_taxonomy tt3 on tt2.parent = tt3.term_id 
  LEFT JOIN wp_2_term_taxonomy tt4 on tt3.parent = tt4.term_id 
  WHERE tt.taxonomy != 'category' and wp_2_posts.ID = wpr.object_id 
  ) AS 'Categories' 
  FROM wp_2_posts 
  WHERE post_type = 'post' 
  AND post_status = 'publish'
  AND post_author = 2
  </code></pre>
  
  <p>This gives me a result like:</p>
  
  <pre><code>ID  Categories
  10  21,101,166,183,311,350,356,357,360,363,0
  19  69,123,166,352,354,356,132,358,360,362,68,68
  </code></pre>
  
  <p>The above example mostly works, although I'm missing the 1st parents, I know this query can give me duplicates but I clean that up later (if I could do it all in 1 go that would be best obviously).<br>
  Also if I change the line: </p>
  
  <pre><code>LEFT JOIN wp_2_term_taxonomy tt2 on tt.parent = tt2.term_id
  </code></pre>
  
  <p>TO</p>
  
  <pre><code>JOIN wp_2_term_taxonomy tt2 on tt.parent = tt2.term_id
  </code></pre>
  
  <p>Give me results like:</p>
  
  <pre><code>ID  Categories
  10  101,93,93
  19  69,123,68,68
  </code></pre>
  
  <p>Note I get Category 93 now for ID 10 (which I was missing in the first), but not the rest.  I've tried all the different joins but to no avail...</p>
  
  <p><strong>Attempt 2)</strong> I modified this code from <a href="https://stackoverflow.com/questions/12948009/finding-all-parents-in-mysql-table-with-single-query">here</a>, this gets me the categories and parent categories but I have to specify the ID.  I'm not sure how to plug my code in above to loop through to get all the post IDs with the categories.  Currently it just gets me all the parent categories &amp; the category of term_id = 165.</p>
  
  <pre><code>SELECT group_concat(T2.term_id separator ',') AS 'Cats'
  FROM (
      SELECT
          @r AS _id,
          (SELECT @r := parent FROM wp_2_term_taxonomy WHERE term_id = _id) AS parent,
          @l := @l + 1 AS lvl
      FROM
          (SELECT @r := 165, @l := 0) vars,
          wp_2_term_taxonomy tt
      WHERE @r &lt;&gt; 0) T1
  JOIN wp_2_term_taxonomy T2
  ON T1._id = T2.term_id
  ORDER BY T1.lvl DESC;
  </code></pre>
  
  <p>This gives me a result like:</p>
  
  <pre><code>Cats
  165,142
  </code></pre>
  
  <p><strong>Attempt 3)</strong> This query is still quite basic, gets me all the categories and the 1st parent in a separate column, but I would need to iterate through the parent category some how... Any ideas?  I'm hoping a mix between Attempt 2 and 3 will work but I've been working on this for way too long.</p>
  
  <pre><code>SELECT DISTINCT ID, TT.term_id, TT.parent
  FROM wp_2_posts p
  JOIN wp_2_term_relationships TR
  ON TR.object_id = p.ID
  JOIN wp_2_term_taxonomy TT
  ON TR.term_taxonomy_id = TT.term_taxonomy_id
  WHERE p.post_type = 'post'
  AND p.post_status = 'publish'
  AND p.post_author = 2
  </code></pre>
  
  <p>This gives me a table looking like:</p>
  
  <pre><code>ID  term_id parent
  10  1   0
  10  21  0
  10  101 93
  10  166 0
  10  183 0
  10  311 0
  10  350 0
  10  356 0
  10  357 0
  10  360 0
  10  363 0
  19  1   0
  19  69  68
  19  123 122
  19  166 0
  19  352 0
  19  354 0
  19  356 0
  19  132 0
  19  358 0
  19  360 0
  19  362 0
  </code></pre>
  
  <p>Thanks in advance for any help.</p>