Query the Wordpress database to get data together with replaced information

Question

sorry for cross-posting: I posted a similar question on StackOverflow but I received no attention (my fault, for sure).

I need to query a Wordpress database.

The first table is wp_posts: from this table I need to retrieve the ID and the post_content where the post_title is “A”, “B” or “C”. This is of course fairly easy:

SELECT ID,post_content FROM `wp_posts` 
WHERE post_title IN ("A","B","C") and post_type = "plugin_is_writing_this_record" 

post_type is necessary here because only a particular Wordpress plugin writes records using that post_type.

Problem is that the same plugin (a plugin used to send forms) replaces with a 0 a vital information for me: the post_author, that in wp_posts is generally the id of the user who created the post, is NOT the id of the user who posted the form, but the id of the user who created the post where the form is located. I need to retrieve the id of the user who posted the form.

Using the ID of the post in wp_posts, I could get the email of the user from the table wp_postmeta.

If you query this last table this way:

SELECT * FROMwp_postmetaWHERE post_id = 277125 (*)

you get data this way (I cut down data a little):

meta_id | post_id | meta_key            | meta_value
----------------------------------------------------------
3       | 277125  | _from_email         | [email protected]
4       | 277125  | _field_your-surname | Surname
5       | 277125  | other_meta_key      | otherdata
6       | 277125  | ...                 | ...

At this point, I know _from_email meta value is related to the mail of the user who posted the form (I need to retrieve even other meta_keys from this table but this one is functional to the query and to this question).

The table that contains the ID I need and the email of the user is of course wp_users, a third table.

Is it possible in a single query over the three tables at once to retrieve the data I need? I tried some JOINS but I couldn't get them working properly. Any help is appreciated.

Answer 1

This query worked for me. I had to reverse it, starting at the user and then matching the post ID. You would need to replace the value of wp_posts.ID of course.

SELECT wp_users.ID
FROM wp_users
LEFT JOIN wp_postmeta ON wp_postmeta.meta_value = wp_users.user_email AND wp_postmeta.meta_key = '_from_email' 
LEFT JOIN wp_posts ON wp_posts.ID = wp_postmeta.post_id AND wp_posts.ID = 2