How to merge two SQL queries to get latest Wordpress Post and Featured Image
I'm trying to show latest Post Excerpt, Post Title and Featured Image on an ASP page. To simplify the query I add the Permalink as a custom field for each Post. I have a query that gets all except Featured Image and I have another query that gets Featured image but I can't work out how to merge them into one query.
// Gets Post Excerpt and Post Title
SELECT
wp_posts.id,
wp_posts.post_title,
wp_postmeta.meta_value,
wp_postmeta.meta_key,
wp_posts.post_excerpt
FROM
wp_postmeta
INNER JOIN wp_posts p ON (wp_postmeta.post_id = wp_posts.ID)
WHERE post_id IN (
SELECT wp_posts.id
FROM wp_posts
WHERE post_type = 'post'
AND post_status = 'publish'
AND meta_key = 'my_permalink'
ORDER BY post_date, wp_posts.id
)
ORDER BY wp_posts.post_date DESC, wp_postmeta.post_id
LIMIT 2
// Gets Featured Images for a Post
SELECT p.*
FROM wp_postmeta AS pm
INNER JOIN wp_posts AS p ON pm.meta_value=p.ID
WHERE pm.post_id = $ID
AND pm.meta_key = '_thumbnail_id'
ORDER BY p.post_date DESC
Can anyone help me merge these queries? Thanks.
Sample data to be returned: ID | post_title | post_excerpt | meta_value_my_permalink | featured_image_guid
** UPDATE * I've managed to get the following which works fine except I can't get more that one row as I get an error when I try and use 'IN' in a subquery eg pm2.post_id IN (SELECT wp_posts.id FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY post_date DESC LIMIT 2)
SELECT
p.post_title,
p.post_excerpt,
pm.meta_value AS permalink,
p2.guid as thumbnail,
p2.post_title as image_alt
FROM
wp_postmeta pm
INNER JOIN wp_posts p ON (pm.post_id = p.ID),
wp_postmeta pm2
INNER JOIN wp_posts p2 ON (pm2.meta_value = p2.ID)
WHERE
pm.post_id = (SELECT wp_posts.id FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY post_date DESC LIMIT 1) AND
pm.meta_key = 'my_permalink' AND
pm2.post_id = (SELECT wp_posts.id FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' ORDER BY post_date DESC LIMIT 1) AND
pm2.meta_key = '_thumbnail_id'
It is difficult to see what you are trying to get without some sample data but I think that you know the post ID and need the info from both the my_permakink and _thumbnail_id keys? Since I'm on a tablet I will give you the bare bones only and let you limit the result set and filter it.
Select *
From wp_posts p
Inner join
Wp_postmeta pm1 on p.id = pm1.post_id and metakey = 'my_permalink'
Inner join
Wp_postmeta pm2 on p.id = pm1.post_id and metakey = '_thumbnail_id'
This will give you every post that has an image with the info for both metakeys. If you want the info for posts without an image change the second inner join to a left join.
作为子查询的一堆连接和视图给了我我需要的东西。
SELECT
p.post_title,
p.post_excerpt,
pm.meta_value AS permalink,
p2.guid AS thumbnail,
p2.post_title AS thumbnail_alt
FROM
wp_postmeta pm
INNER JOIN wp_posts p ON (pm.post_id = p.ID)
INNER JOIN wp_postmeta pm2 ON (pm2.post_id = p.ID)
INNER JOIN wp_posts p2 ON (pm2.meta_value = p2.ID)
WHERE
pm.meta_key = 'my_permalink' AND
pm2.meta_key = '_thumbnail_id' AND
p.ID IN (SELECT * FROM vwLatestPostIds)
使用它来检索帖子标题，帖子ID，发布精选缩略图URL，帖子类别等 - “SELECT a.ID id，a.post_title title，a.post_content content，taxonomy，name，a.post_date_gmt postdate，max（c。 guid）img_url FROM kb_posts a JOIN kb_term_relationships tr ON（a.id = tr.object_id）JOIN kb_term_taxonomy tt ON（tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy ='category'）JOIN kb_terms t ON（t.term_id = tt .term_id AND t.term_id = $ user_id）
LEFT JOIN
(select post_parent, max(post_date_gmt) as latest_image_date from kb_posts
where post_type='attachment' GROUP BY post_parent) b
on a.id=b.post_parent
LEFT JOIN kb_posts c
on c.post_parent=a.id
and c.post_type='attachment'
and b.latest_image_date = c.post_date_gmt where c.guid IS NOT NULL
GROUP BY a.ID ORDER BY a.ID DESC LIMIT 5"
上一篇: 前端控制器的帮助