2

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.

Bart
  • 19,692
  • 7
  • 68
  • 77
Lasha
  • 581
  • 5
  • 9
  • I came across [» this page on the WordPress Codex](http://codex.wordpress.org/Displaying_Posts_Using_a_Custom_Select_Query#Query_based_on_Custom_Field_and_Category) 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 Sep 15 '11 at 05:20
  • 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](http://wordpress.stackexchange.com/questions/907/using-wp-query-to-query-multiple-categories-with-limited-posts-per-category) – Lasha Sep 15 '11 at 06:01
  • Please show table structure - CREATE TABLE statement. It might help us to resolve your question. – Devart Sep 15 '11 at 07:14
  • 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. http://wordpress.org/ideas/topic/retrieve-x-number-of-posts-per-category-via-wp_query – Lasha Apr 09 '14 at 16:44

1 Answers1

0

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
Free Lancer
  • 161
  • 4