get_children filter with postmeta

Question

I am doing the following to get all the children associated with a post:

    $args = array(
        'order'=> 'ASC',
        'post_parent' => get_the_ID(),
        'post_status' => 'publish',
        'post_type' => 'task'
    );
    $tasks = get_children( $args );

Now the posts of type task have postmeta associated with them. One particular meta_key is task_progress. I would like to get all the children whose task_progress meta_value is less than 100. What is the best way to filter the results?

Answer 1

This should do the trick:

global $wpdb;
$parent = 1; // Make sure it's int (intval() it) as it's not escaped in the query
$posts = $wpdb->get_results(
    "SELECT `post_id` AS `ID`, `meta_value` AS `Progress` FROM {$wpdb->postmeta}
        WHERE `meta_key`='task_progress' AND `meta_value` < 100 AND `ID` IN
            (SELECT `post_id` FROM {$wpdb->posts} WHERE `post_parent`={$parent})
        ORDER BY `meta_value` DESC, `post_id` DESC;" // Sort descending
);
foreach($posts as $post){
    $progress = $post->Progress;
    $post = get_post($ID = $post->ID);
    // Print here
}

Regards.