title: Wordpress: mysql query to return posts from specific categry - and keywords?
body: <p>Wordpress / Custom MySQL queries</p>
  
  <p>I've got a query to return all posts from a specific category:</p>
  
  <pre><code>SELECT  ID, post_title, post_name, guid, post_date, post_content, guid FROM wp_posts as p
  INNER JOIN wp_term_relationships AS tr ON
  (p.ID = tr.object_id AND
  tr.term_taxonomy_id IN (4) )
  INNER JOIN wp_term_taxonomy AS tt ON
  (tr.term_taxonomy_id = tt.term_taxonomy_id AND
  taxonomy = 'category')
  ORDER BY id DESC LIMIT 10
  </code></pre>
  
  <p>But I also need this query to return the keywords associated with each post.  I started to create a "keywords by post ID" query, but realized that would be a very costly call when repeated.</p>
  
  <p>Any idea on how to request the keywords/terms for each post that comes back?  Comma-separated would work, I guess?</p>
  
  <p><strong>SOLUTION:</strong></p>
  
  <p>No pretty way to do it in MySQL -- joining all those tables can be nasty.  So, the solution is three steps:</p>
  
  <p>1) get the posts:</p>
  
  <pre><code>SELECT DISTINCT ID, post_title, post_name, guid, post_date, post_content, guid , GROUP_CONCAT(k.term_taxonomy_id) as keywords FROM wp_posts as p
  INNER JOIN wp_term_relationships AS tr ON
  (p.ID = tr.object_id AND
  tr.term_taxonomy_id IN (4) )
  INNER JOIN wp_term_relationships as k ON
  (p.ID = k.object_id)
  INNER JOIN wp_term_taxonomy AS tt ON
  (tr.term_taxonomy_id = tt.term_taxonomy_id AND
  taxonomy = 'category')
  GROUP BY p.ID
  ORDER BY id DESC LIMIT 10
  </code></pre>
  
  <p>The "group_concat" will return "keywords" back as comma-separated ID's</p>
  
  <p>2) get the keywords from wp_terms</p>
  
  <p>3) use PHP to look up keywords from that array (#2) as needed</p>