One query to get latest 7 posts from each category in WordPress

2
votes

I'm looking to write a SINGLE custom SQL query to retrieve the latest 7 posts from each of my 6 specific categories of a custom post type.

I know how to query custom post types and taxonomies, but my SQL knowledge is limited. I don't know how to get WordPress to give me 7 latest posts from each of the 6 categories. It doesn't really matter in what order it's returned, as long as the content is there.

Example: If I have categories 1, 2, 3, 4, 5, 6, I would like to retrieve the 7 latest posts from each of those categories. Specifically, the query should pull the 7 latest posts for category 1, 7 latest posts for category 2, and so forth, all with a single query.

I don't want to have 6 separate WP_Query instances as it's not efficient.

1
mysqlwordpressposts
I came across » this page on the WordPress Codex covering custom SQL queries, and one of the examples covered pulling in ALL posts from specific categories, and then setting a LIMIT of 4. What I want to do is to set a similar LIMIT, but for EACH CATEGORY, so I can have the 7 LATEST POSTS from them. – Lasha
I may have found a solution, but I am not sure if it's the best way to do this. Can anyone suggest an improved or better way to achieve this? CLICK HERE FOR AFOREMENTIONED SOLUTION – Lasha
Please show table structure - CREATE TABLE statement. It might help us to resolve your question. – Devart
This problem still hasn't been solved without having to do a lot of work. I posted this idea a long time ago on WordPress Ideas; it's now one of the top rated ideas. Help vote it even more to get this feature into WP core. wordpress.org/ideas/topic/… – Lasha

1 Answers

0
votes

you need to use the UNION like below

SELECT wposts.* 
FROM $wpdb->posts wposts
    LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id 
    LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE wpostmeta.meta_key = 'customDateField'
    AND wpostmeta.meta_value >= CURDATE()
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->term_taxonomy.term_id IN(1)
ORDER BY wpostmeta.meta_value ASC
LIMIT 7
UNION
SELECT wposts.* 
FROM $wpdb->posts wposts
    LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id 
    LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE wpostmeta.meta_key = 'customDateField'
    AND wpostmeta.meta_value >= CURDATE()
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->term_taxonomy.term_id IN(2)
ORDER BY wpostmeta.meta_value ASC
LIMIT 7
UNION
SELECT wposts.* 
FROM $wpdb->posts wposts
    LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id 
    LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE wpostmeta.meta_key = 'customDateField'
    AND wpostmeta.meta_value >= CURDATE()
    AND $wpdb->term_taxonomy.taxonomy = 'category'
    AND $wpdb->term_taxonomy.term_id IN(3)
ORDER BY wpostmeta.meta_value ASC
LIMIT 7