Just an example but, The results should be the top 15 unique terms in the taxonomy genre used/added in the past 24 hours.

My thoughts were to query all posts from the past 24 hours, get the terms list, and iterate through the list to get the terms count.

Is there an easier and better way to do this?


Well it only took me 3 days but I finally pieced together this. I know this isnt the past 24 hours, but it is a 24 hour period and I can easily change the dates. These dates worked for testing purposes for posts I had with tags.

$date1 = '2016-07-20 00:00:00';
$date2 = '2016-07-21 00:00:00';
$sql = $wpdb->prepare("
    SELECT taxonomy.term_taxonomy_id AS termId, COUNT( * ) AS count, relatedTerms.*
    FROM celebnew_term_taxonomy AS taxonomy
    JOIN (
        SELECT *
        FROM celebnew_term_relationships related
        JOIN (
            SELECT *
            FROM celebnew_posts AS posts
            WHERE posts.post_date > '%s'
            AND posts.post_date < '%s'
            AND posts.post_status = 'publish'
            ORDER BY posts.post_date ASC
        )24HrPosts ON 24HrPosts.ID = related.object_id
    )relatedTerms ON relatedTerms.term_taxonomy_id = taxonomy.term_taxonomy_id
    WHERE taxonomy.taxonomy = 'post_tag'
    GROUP BY taxonomy.term_taxonomy_id
", $date1, $date2);
$slebs = $wpdb->get_results($sql);

Id like to see a cleaner version of this if anyone has any suggestion, but for now this is what Im going with.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.