Query within a foreach within a query (queryception)

I’m achieving this list in a really roundabout way at the moment and I feel like I should be able to do it with a foreach instead of separate queries for each “House” (House is a CPT and is assigned as usermeta too), but I can’t get it to work.

Currently doing it with these queries for EACH House (manually writing in the ID of the ‘House’ post for each new query):

$houseid = '8490'; //Manually changing this id each time, so I have the code below repeated a bunch...
 $connectors = get_users( array( 
  'fields' => 'ID',
  'meta_query' => array(
   'relation' => 'OR',
   'house' => array(
    'key' => 'house',
    'value' => $houseid,
   ),
   'leader_of_house' => array(
    'key' => 'leader_of_house',
    'value' => $houseid,
   ),
  )
 ) );
$connections = new WP_Query( array( 'post_type' => 'connection', 'posts_per_page' => -1, 'author__in' => $connectors ) );
$total = $connections->found_posts; if ( !empty ( $total ) ) { echo '<span class="connector"><p>' . get_the_title($houseid) . ' (' . $connections->found_posts . ' connections)</p></span>'; }

So I’ve started writing a foreach instead to loop through those House IDs I was manually writing in before, but it’s not working (it’s just showing one result, which is the title of page I’m writing this code on). Here’s my current attempt:

$the_query = new WP_Query( array( 'post_type' => 'house', 'posts_per_page' => -1 ) );
$houses = $the_query->get_posts();
foreach( $posts as $post ) {
 $houseid = $post->ID;
 $connectors = get_users( array( 
  'fields' => 'ID',
  'meta_query' => array(
   'relation' => 'OR',
   'house' => array(
    'key' => 'house',
    'value' => $houseid,
   ),
   'leader_of_house' => array(
    'key' => 'leader_of_house',
    'value' => $houseid,
   ),
  ),
 ) );
 $connections = new WP_Query( array( 'post_type' => 'connection', 'posts_per_page' => -1, 'author__in' => $connectors ) );
 $total = $connections->found_posts; if ( !empty ( $total ) ) { echo '<span class="connector"><p>' . get_the_title($houseid) . ' (' . $connections->found_posts . ' connections)</p></span>'; }
}

Can anyone tell me where I’ve gone wrong?

The goal is to list each “House” and then tally up the number of “Connections” (also a CPT) which the authors assigned to each House have created. Like a scoreboard. So House > Users with matching meta (House ID) > Connections created by those users.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Good for you that you’ve corrected the $posts that was undefined, but then:

1. There’s no need to call $the_query->get_posts() because when WP_Query is instantiated with a non-empty query, e.g. new WP_Query( 'post_type=house' ) (contains one query arg — post_type) as opposed to new WP_Query() (no query args specified), the get_posts() method in that class will automatically be called.
2. If you were trying to get the posts that were already fetched by the specific query, then get_posts() is not actually for that purpose. On the contrary, it will re-parse the query args, apply various filters, etc. and then re-query the database for the posts matching the query args. ( So basically, the same query is gonna be duplicated and it’s not good.. )

   So how can you get that already fetched posts?

   Easy: Use the $posts property, i.e. $the_query->posts in your case.

3. Referring to your second query ($connections), if you just want to access the value of $found_posts, then you should just set the posts_per_page to 1 and not -1.

   ( As an aside, I’ve been hoping WP_Query would implement something like fields=count so that we could easily get the total number of found posts in the database.. )

Now as for this (from your comment): how to sort the results by the number in $total, rather than echoing in your foreach, you can store the totals (and post IDs) in an array and then sort and echo them afterwards.

Here’s an example where I store them in an array named $list and used usort() to sort them — by the $total value, or the house name (post title) if the total is equal:

$the_query = new WP_Query( array(
    'post_type'      => 'house',
    // Note: Instead of -1, you should use a high number like 99999..
    'posts_per_page' => -1,
) );

// each item is: array( <total connections>, <post ID>, <post title> )
$list = array();

foreach ( $the_query->posts as $post ) {
    $connectors = get_users( array(
        'fields'     => 'ID',
        'meta_query' => array(
            'relation' => 'OR',
            array(
                'key'   => 'house',
                'value' => $post->ID,
            ),
            array(
                'key'   => 'leader_of_house',
                'value' => $post->ID,
            ),
        ),
    ) );

    if ( empty( $connectors ) ) {
        continue;
    }

    $connections = new WP_Query( array(
        'post_type'      => 'connection',
        'posts_per_page' => 1,
        'author__in'     => $connectors,
    ) );

    if ( $connections->found_posts ) {
        $list[] = array( $connections->found_posts, $post->ID, $post->post_title );
    }
}

// Sort by the total connections, or the post title instead if the total is equal.
usort( $list, function ( $a, $b ) {
    return $a[0] === $b[0] ? strcasecmp( $a[2], $b[2] ) : $b[0] > $a[0];
} );

if ( ! empty( $list ) ) {
    echo '<ul>';
    foreach ( $list as $item ) {
        list ( $total, $post_id ) = $item;

        echo '<li><span class="connector">' . esc_html( get_the_title( $post_id ) ) .
            " ($total connections)</span></li>";
    }
    echo '</ul>';
}

And with that, the first three items in this list would be displayed in the following order — the first two items have 4 connections, so they are instead sorted alphabetically in ascending order: (In MySQL, this is equivalent to ORDER BY total DESC, LOWER( post_title ) ASC)

Chapman, Robinson & Moore House (4 connections)
Databasix House (4 connections)
Aston & James House (2 connections)

So is that how you wanted the list be sorted? 🙂

Method 2

Ah I did it. I’d just cocked up the names of some identifiers in the example above. This works for anyone who may be interested:

$the_query = new WP_Query( array( 'post_type' => 'house', 'fields' => 'ID', 'posts_per_page' => -1 ) );
$posts = $the_query->get_posts();
foreach( $posts as $post ) {
 $connectors = get_users( array( 
  'fields' => 'ID',
  'meta_query' => array(
   'relation' => 'OR',
   'house' => array(
    'key' => 'house',
    'value' => $post->ID,
   ),
   'leader_of_house' => array(
    'key' => 'leader_of_house',
    'value' => $post->ID,
   ),
  ),
 ) );
$connections = new WP_Query( array( 'post_type' => 'connection', 'posts_per_page' => -1, 'author__in' => $connectors ) );
$total = $connections->found_posts; if ( !empty ( $total ) ) { echo '<span class="connector"><p>' . get_the_title($post->ID) . ' (' . $total . ' connections)</p></span>'; }
}

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0