php - MySQL WordPress 查询 : Select posts by meta value but also include category

Question

好吧，试图在这里完成一些忍者开发，但有点卡住了。我在这里需要真正专家的帮助。提前致谢。

此查询非常适合从 WordPress 外部返回帖子信息和类别:

SELECT wp_posts.ID,wp_posts.post_title,wp_terms.name as category FROM wp_posts
INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
INNER JOIN wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
INNER JOIN wp_terms ON wp_terms.term_id = wp_term_taxonomy.term_id
WHERE wp_term_taxonomy.taxonomy = 'category'
AND wp_posts.post_status = 'publish'

现在，此查询非常适合按公司名称进行过滤，公司名称是存储在 postmeta 表中的元字段。 (下面的{$q}是经过清理的公司名称)

SELECT ID,post_title,post_date,post_name,post_author FROM wp_posts,wp_postmeta 
WHERE ID = wp_postmeta.post_id AND meta_key = 'company'
AND meta_value = \"{$q}\" AND wp_posts.post_status = 'publish' 
ORDER BY post_date DESC

无论如何，这两个查询本身都可以很好地工作。但是，我想将它们组合成一个查询，以便我可以选择类别，但仍按公司进行过滤。

有什么想法吗？我很想听听您关于如何解决这个问题的想法。如果可以的话，我会给你这个答案 3 分。

Answer 1

实现此目的的一种方法:

SELECT wp_posts.ID,wp_posts.post_title,wp_terms.name as vert FROM wp_posts
INNER JOIN wp_term_relationships on wp_posts.ID = wp_term_relationships.object_id
INNER JOIN wp_term_taxonomy on wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
INNER JOIN wp_terms ON wp_terms.term_id = wp_term_taxonomy.term_id
WHERE wp_term_taxonomy.taxonomy = 'category'
AND wp_posts.post_status = 'publish'
AND wp_posts.ID IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'company' AND meta_value = \"{$q}\" ) LIMIT 0,99999