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

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.

1 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.

  • ok so being ok at php I think I am understanding what your getting at here. The challenge is that post_type=property in this case. wpp_agents is the postmeta table field that contains the related user->ID. So postmeta records for post->ID 29 has a wpp_agents value of 3, which is the user-ID I am already querying. Am I missing something here? Oct 10, 2012 at 3:22
  • You see the function and you can read what arguments you can throw in - exchange what you need, come back with an update and a dump.
    – kaiser
    Oct 10, 2012 at 3:47
  • I am going to keep playing with this stuff but I decided to go at the issue from the other end. Listing the posts, then using the postmeta->wpp_agents value to find a user. IF I was dealing with a large db, this would not be a good way to go because I think it taxes the db to heavily but with 20 posts and 50 users, its not overkill. Oct 10, 2012 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.