Custom SQL query to get most viewed posts with images

0 Views

I am trying to write a custom SQL query and can only get part of it working.

I need to select the top 10 posts with the most amount of views. (See code below)

Read More
SELECT p.*, pm1.meta_value + 0 AS viewcount 

FROM wp_posts p    

LEFT JOIN wp_postmeta pm1  ON pm1.post_id = p.ID

WHERE pm1.meta_key = 'pvc_views' AND p.post_status IN ('publish') AND p.post_type='post' AND p.post_password ='' 

ORDER BY viewcount 

DESC LIMIT 0, 10

The code above does work but I also need to add something to the query that also returns the image attachment metadata. I tried the code below which includes an inner join but I get the error: Unknown column 'ID' in 'where clause' 

SELECT p.*, pm1.meta_value + 0 AS viewcount 

FROM wp_posts p

INNER JOIN (SELECT * FROM wp_postmeta  WHERE post_id = ID AND meta_key = '_wp_attachment_metadata') pm2

LEFT JOIN wp_postmeta pm1  ON pm1.post_id = p.ID

WHERE pm1.meta_key = 'pvc_views' AND p.post_status IN ('publish') AND p.post_type='post' AND p.post_password ='' 

ORDER BY viewcount 

DESC LIMIT 0, 10

Can anyone help?

Post Views: 0

Related posts

Leave a Reply

1 comment

  1. You need to provide the alias of wp_posts’s table in the inner query also you can rewrite your query as below

    SELECT 
p.*, 
pm1.meta_value + 0 AS viewcount ,
pm2.meta_value AS image
FROM wp_posts p
INNER JOIN 
wp_postmeta   pm2
 ON (pm2.post_id = p.ID AND pm2.meta_key = '_wp_attachment_metadata')
LEFT JOIN wp_postmeta pm1  ON pm1.post_id = p.ID
WHERE 
pm1.meta_key = 'pvc_views' 
AND p.post_status IN ('publish') 
AND p.post_type='post' 
AND p.post_password ='' 
ORDER BY viewcount 
DESC LIMIT 0, 10