Query within a foreach within a query (queryception)

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? 🙂