Hello, i’m creating a native apps based on wordpress site powered by newspaper. In one of the block i’m using sort popular post by last 7 day .
I dig deep into meta_key and meta_value and assume there are ‘post_views_count_7_day_total’ and ‘post_views_count_7_day_last_date’ that control the query.. in order to create compatible json with wordpress json api v2.. i create this custom query:
SELECT
mm_posts.ID as id,
mm_posts.post_date as date ,
mm_posts.post_date_gmt as date_gmt,
mm_posts.guid,
mm_posts.post_modified as modified,
mm_posts.post_modified_gmt as modified_gmt,
mm_posts.post_name as slug,
mm_posts.post_type as type,
mm_posts.post_name as link,
mm_posts.post_title as title,
mm_postmeta.meta_value
FROM
mm_posts
Left Join mm_postmeta ON mm_postmeta.post_id = mm_posts.ID
WHERE
mm_posts.post_type = "post" AND
mm_posts.post_status = "publish" AND
mm_postmeta.meta_key = "post_views_count_7_day_total" AND EXISTS(
SELECT
mm_postmeta.post_id
FROM
mm_postmeta
WHERE
mm_postmeta.meta_key = "post_views_count_7_day_last_date" AND
mm_postmeta.meta_value BETWEEN UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -7 DAY)) AND UNIX_TIMESTAMP(now())
)
ORDER BY mm_postmeta.meta_value DESC, mm_posts.post_date ASC
Can you clarify if i make query right ?
Thanks
HI
On a first look the query seems fine but you have to make some tests to find out if it works as expected. This query is very complex and I can’t say for sure if it will work fine.
Thanks!
