Support » Developing with WordPress » Query SQL: Get display_name from user_id

Query SQL: Get display_name from user_id

  • Resolved microtag

    (@microtag)


    3 years, 1 month ago

    Hello,

    below query works fine and result is:

    POST_ID: 1

    FIRST_AUTHOR: ADMIN

    SECOND_AUTHOR: 5

    SELECT posts_post.ID AS post_ID,
       posts_post_author.display_name AS First_Author,
       meta1.meta_value AS Second_Author
FROM  wp_posts AS posts_post
INNER JOIN wp_users AS posts_post_author ON posts_post_author.ID = posts_post.post_author
INNER JOIN wp_postmeta meta1 ON meta1.post_id = posts_post.ID AND meta1.meta_key  = 'user_id'
WHERE 1=1 AND posts_post.post_type = 'post'  AND posts_post.post_status = 'publish'

    How can I convert into the query, the “second_author: 5” record that results as USER_ID and get DISPLAY_NAME result?

    Could you help me with join to wp_users?
    Thanks

    • This topic was modified 3 years, 1 month ago by microtag.
Viewing 2 replies - 1 through 2 (of 2 total)
  • Moderator bcworkz

    (@bcworkz)

    3 years, 1 month ago

    Untested:
    INNER JOIN wp_users AS second_author_display ON second_author_display.ID = Second_Author

    Then add to SELECT list:
    , second_author_display.display_name

    I’m not that great with SQL myself. I think that’s right. If not, it should at least be close.

    BTW, 1=1 AND serves no purpose and can be eliminated. WP adds 1=1 into the base query of code generated SQL just in case there are no additional WHERE clauses, then the base query remains a valid syntax. Sometimes WP will add 1=0, which of course always fails. It’s the parser’s way of saying “I don’t understand the arguments you provided.” Just FYI, you can leave 1=1 if you want, no harm done.

    Thread Starter microtag

    (@microtag)

    3 years, 1 month ago

    Works Great!
    Thanks a lot.

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Query SQL: Get display_name from user_id’ is closed to new replies.

Views