Wordpress / Custom MySQL queries
I have got a question to come back all posts from the 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
However I likewise need this question to come back the key phrases connected with every publish. I began to produce a "key phrases by publish ID" query, but recognized that might be a really pricey call when repeated.
Any idea regarding how to request the key phrases/terms for every publish that returns? Comma-separated works, I suppose?
No pretty method of doing it in MySQL -- joining all individuals tables could be nasty. So, the answer is three steps:
1) obtain 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 "key phrases" back as comma-separated ID's
2) obtain the key phrases from wordpress_terms
3) use PHP to find information about key phrases from that array (#2) when needed
You are able to choose all key phrases and posts ids with another query and mix both leads to greater level (PHP - I suppose you utilize it). You'll call database only two times, however you'll have to write more logic in PHP level.
second result would seem like this:
post_id | keyword ----------------- 2 | keyword1 2 | keyword2 3 | keyword3 4 | keyword2 4 | keyword10