Wordpress: mysql query to return posts from specific categry - and keywords?

Question

Wordpress / Custom MySQL queries

I've got a query to return all posts from a specific category:

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

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.

Any idea on how to request the keywords/terms for each post that comes back? Comma-separated would work, I guess?

SOLUTION:

No pretty way to do it in MySQL -- joining all those tables can be nasty. So, the solution is three steps:

1) get the posts:

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

The "group_concat" will return "keywords" back as comma-separated ID's

2) get the keywords from wp_terms

3) use PHP to look up keywords from that array (#2) as needed

Answer 1

You can select all keywords and posts ids with another query and combine both results in higher level (PHP - I guess you use it). You will call database only twice, however you will have to write more logic in PHP level.

2nd result would look like this:

post_id | keyword 
-----------------
      2 | keyword1
      2 | keyword2 
      3 | keyword3 
      4 | keyword2 
      4 | keyword10