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

Question

I have about 50 users. Its pretty easy to list them all. However they are related to a special custom post type through a postmeta field called wpp_agents.

So I need to list Users, but also the post title(linked to the post).

1. User 1 | User 1's Post(linked)
2. User 2 | User 2's Post(linked)
3. etc...

I am a mysql noob but getting pretty ok with php.

Here is what I am playing with:

$data = $wpdb->get_results(
    "SELECT $wpdb->users*,
        "SELECT display_name, user_email 
     FROM $wpdb->users 
     LEFT JOIN $wpdb->usermeta ON
      user.ID=wpp_agents;",
    ARRAY_A
);

but its not right.

I believe I need to SELECT * FROM $wpdb->users LEFT JOIN $wpdb->postmeta

but then not sure.

any help is greatly appreciated.

Answer 1

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.