0

I have 3 tables:

  • wp_posts
  • wp_metadata
  • users

When I get my query from wp_posts I'm using LEFT JOIN to get the data from the wp_metadata, but how can I get a value from the 3rd table users?

Example:

I have one post on my WordPress blog. On that post I have some meta-fields - one of them is called userid. My users are located in a table outside of WordPress. How can I get the user's username from the table users where the post-meta-key userid is equal to the users-tables row ID?

Here is my SQL-request as it is right now:

$result = query("
SELECT ID, post_title, post_excerpt, post_content, 
       m.meta_value AS placeid, 
       i.meta_value AS photoid, 
       r.meta_value AS rating, 
       u.meta_value AS userid, 
       b.username 
FROM wp_posts p 
       LEFT JOIN wp_postmeta m ON (m.post_id = p.ID) 
       LEFT JOIN wp_postmeta i ON (i.post_id = p.ID) 
       LEFT JOIN wp_postmeta r ON (r.post_id = p.ID) 
       JOIN wp_postmeta u ON (u.post_id = p.ID) 
       LEFT JOIN brugere b ON (b.id = userid) 
WHERE m.meta_key = 'placeid' 
       AND i.meta_key = 'photoid' 
       AND r.meta_key = 'rating' 
       AND u.meta_key = 'userid' 
       AND post_status = 'publish' 
ORDER BY ID DESC 
LIMIT 50
");

I want one query, because I'm using the query on a print json_encode.

  • 1
    This seems like a MySQL question rather than a WordPress question, and so it's probably off-topic ("not specific to WordPress (even if they happen in its context)"). You might be further ahead to ask it on Stack Overflow. – Pat J Nov 5 '13 at 0:24
  • @PatJ It depends on the schema of the WordPress tables, so it is WordPress specific. – fuxia Nov 5 '13 at 0:50
  • agree with @PatJ, this question requires SQL expertise. Doesn't look like understanding the DB schema is the problem here. – Mark Kaplun Nov 5 '13 at 5:49
0

I simplified your query, but wouldn't something like this accomplish it?

SELECT *
FROM $wpdb->posts AS p
LEFT JOIN $wpdb->postmeta AS pm
    ON p.ID = pm.post_id
LEFT JOIN users AS u
    ON pm.meta_value = u.user_id
WHERE pm.meta_key = 'user_id'
    AND post_status = 'publish'
ORDER BY p.ID DESC
LIMIT 50;
  • I think it works - the result shows. But how can I get u.username in the same query? – user2908800 Nov 5 '13 at 7:08
  • If you left the SELECT with a wildcard it should come through unless its conflicting with another username column. You're probably best off narrowing down the SELECT to only include fields you want including u.username – Joey Yax Nov 5 '13 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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