how would I create a custom query to get all users, and a related post based on a postmeta field?

Question

Gladly there’s a function that will output exactly that query string for you (still laughing about it 🙂 ).

Here it is: get_posts_by_author_sql ^{@queryposts.com}, which looks in the core source like this.

In detail it builds the WHERE part for you, that you can drop into your query.

So just throw the user ID in and build your string that you can use in your query:

foreach ( $users as $user )
{
    $where = get_posts_by_author_sql( 'wpp_agents', true, $user->ID );

    echo '<pre>';
    var_dump( $where );
    echo '</pre>';
}

Take a look at this question on how to intercept the posts_clauses (or even better the posts_where) filter.

Maybe you find a way to simply alter the main query to spit this out on a single call. Hint: You should have some serious caching mechanism or add the result as transient. This can get intense.