Query builder doesn’t support columns with spaces in the name

  • Resolved ericscoles

    (@ericscoles)


    5 months ago

    Trying to join tables to get user full name information in a report, using following SQL: 

    select wp_2_time_entry.*, wp_users.*, wp_usermeta.* from wp_2_time_entry
    	inner join wp_users
    		on wp_2_time_entry.WP_UserID = wp_users.ID
    	inner join wp_usermeta
    		on wp_usermeta.    user ID = wp_users.id

    Error message returned: 

    Unknown column 'wp_usermeta.User Id' in 'on clause'

    Adding backticks around wp_usermeta returns this error:

    Unknown column 'wp_usermeta.User Id' in 'on clause'

    Removing the backticks around user ID returns this error: 

    You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'id = wp_users.id limit 100' at line 5

    ‘User Id’ appears to definitely be the correct column name for user IDs in usermeta.

    Are there any workaround for this?

Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter ericscoles

    (@ericscoles)

    Editor appears to have mangled the query, trying again — this is what I’m running:

    select wp_2_time_entry.*, wp_users.*, wp_usermeta.* from wp_2_time_entry
    	inner join wp_users
    		on wp_2_time_entry.WP_UserID = wp_users.ID
    	inner join wp_usermeta
    		on wp_usermeta.    User Id = wp_users.id

    EDIT: Can’t get Gutenberg to accurately reproduce the SQL code in a code block, trying this as a paragraph block instead of code.

    select wp_2_time_entry., wp_users.*, wp_usermeta.* from wp_2_time_entry
    inner join wp_users
    on wp_2_time_entry.WP_UserID = wp_users.ID
    inner join wp_usermeta
    on wp_usermeta.User Id = wp_users.id

    • This reply was modified 5 months ago by ericscoles.
    • This reply was modified 5 months ago by ericscoles.
    Plugin Contributor Kim L

    (@kimmyx)

    Hi @ericscoles,

    Could you try wp_usermeta.user_id instead?

    It’s the correct column name for the user ID in the wp_usermeta table.

    See here: https://share.zight.com/9Zu9yd7r

    Let us know how it goes. ??

Viewing 2 replies - 1 through 2 (of 2 total)
  • You must be logged in to reply to this topic.