Query posts GROUP BY post_parent

  • Maxell_WP

    (@maxell_wp)


    4 years, 9 months ago

    Hi, I am trying to get recently modified posts grouped by post_parent ID..

    $arg = array(
    ‘post_type’ => ‘episode’,
    ‘post_status’ => ‘publish’,
    ‘order’ => ‘DESC’,
    ‘orderby’ => ‘post_modified’,
    ‘groupby’ => ‘post_parent’, // Is this possible
    ‘paged’ => $paged
    );

    I want to display recent 32 posts, only 1 most recently updated post from each PARENT post, I tried so many things, but doesn’t work. Any help would be appreciated. thanks

Viewing 4 replies - 1 through 4 (of 4 total)
  • Sarah Snow

    (@arasae)

    4 years, 9 months ago

    Hi there,

    Have you considered using a shortcodes plugin to achieve this? Some of these plugins are more complicated than others, but there are a few that display a number of posts (number set by you!) by categories, tags, parent, etc:

    https://wordpress.org/plugins/search/shortcodes+posts/

    I might take a look at some of the features of popular ones in that link above. It may take some setup, but you may be able to add multiple shortcodes to a single page, set to display either the parent post like you mentioned or categories as a work-around.

    Thread Starter Maxell_WP

    (@maxell_wp)

    4 years, 9 months ago

    Thanks Sara, but its a simple query, I would not prefer using any plugin for this. I also tried to group result with direct query, below is my query:

    $wpdb->get_results("SELECT * FROM wp_posts WHERE post_type='episode' GROUP BY post_parent ORDER BY post_modified DESC LIMIT 32;");

    it doesn’t sort with post_modified DESC, but if I remove GROUP BY post_parent, it perfectly displays all recently updated posts. Is there any way to handle this ? Following query works if I remove GROUP BY…

    $wpdb->get_results("SELECT * FROM wp_posts WHERE post_type='episode' ORDER BY post_modified DESC LIMIT 32;");

    The only workaround I did was running query on 1000 posts and then adding post_parent to one array and for final out code checking with if parent is in excluded IDs, then not add in final output.. something like below:

    if (!in_array($ep_parent_media_id, $excluded_cats)) {

    but to get only 32 posts I have to query 500 posts if there are more than 200 updated posts under one drama. This is wrong.. There must be a very simple way, but due to my limited knowledge on WP I cant get it work, any experienced developer can write max 10 lines of code and fix it.. Lets hope someone replies….

    thanks for your suggestions though 🙂

    • This reply was modified 4 years, 9 months ago by Maxell_WP.
    • This reply was modified 4 years, 9 months ago by Maxell_WP.
    Dion

    (@diondesigns)

    4 years, 9 months ago

    You can achieve what you want with a modified query, but you must first clear the ONLY_FULL_GROUP_BY SQL mode. That’s well beyond the scope of this site; perhaps dba.stackexchange.com would be a better place to ask.

    Also note that you’re sorting on a non-indexed datetime column, so your query has the potential to cause server issues.

    Thread Starter Maxell_WP

    (@maxell_wp)

    4 years, 9 months ago

    So basically no one knows how to achieve this.. this is one of the reasons I just delete WP after wasting so much time..

Viewing 4 replies - 1 through 4 (of 4 total)

The topic ‘Query posts GROUP BY post_parent’ is closed to new replies.