Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
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? –  TJ Sherrill Oct 10 '12 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 '12 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. –  TJ Sherrill Oct 10 '12 at 16:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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