[Resolved] When querying certain fields, wpcf fields are behaving oddly

[Author]

Posts

[frankS-8]

I am trying to:
I wish to show a line-item for each user, regardless of whether they have a value for wpcf-dc-license-number, wpcf-dc-license-expiration, etc.

Currently my results only display user line-items for those users who have all the fields fulfilled.

Here is what I tried:
SELECT lx_usermeta.meta_value AS dc_license_number,
lx_usermeta2.meta_value AS dc_license_expiration,
lx_usermeta3.meta_value AS md_license_number,
lx_usermeta4.meta_value AS md_license_expiration,
lx_usermeta5.meta_value AS va_license_number,
lx_usermeta6.meta_value AS va_license_expiration,
lx_usermeta7.meta_value AS first_name,
lx_usermeta8.meta_value AS last_name,
lx_usermeta9.meta_value AS ltx_company

FROM lx_usermeta
JOIN lx_usermeta lx_usermeta2 ON lx_usermeta.user_id = lx_usermeta2.user_id
JOIN lx_usermeta lx_usermeta3 ON lx_usermeta.user_id = lx_usermeta3.user_id
JOIN lx_usermeta lx_usermeta4 ON lx_usermeta.user_id = lx_usermeta4.user_id
JOIN lx_usermeta lx_usermeta5 ON lx_usermeta.user_id = lx_usermeta5.user_id
JOIN lx_usermeta lx_usermeta6 ON lx_usermeta.user_id = lx_usermeta6.user_id
JOIN lx_usermeta lx_usermeta7 ON lx_usermeta.user_id = lx_usermeta7.user_id
JOIN lx_usermeta lx_usermeta8 ON lx_usermeta.user_id = lx_usermeta8.user_id
JOIN lx_usermeta lx_usermeta9 ON lx_usermeta.user_id = lx_usermeta9.user_id

WHERE lx_usermeta.meta_key = "wpcf-dc-license-number"
AND lx_usermeta2.meta_key = "wpcf-dc-license-expiration"
AND lx_usermeta3.meta_key = "wpcf-maryland-license-number"
AND lx_usermeta4.meta_key = "wpcf-maryland-license-expiration"
AND lx_usermeta5.meta_key = "wpcf-virginia-license-number"
AND lx_usermeta6.meta_key = "wpcf-virginia-license-expiration"
AND lx_usermeta7.meta_key = "first_name"
AND lx_usermeta8.meta_key = "last_name"
AND lx_usermeta9.meta_key = "wpcf-ltx-company"

Here is what I tested after, as I could see that users were returned as a row even if the users were missing first_name or last_name:
SELECT lx_usermeta.meta_value AS first_name, lx_usermeta2.meta_value AS last_name
FROM lx_usermeta
JOIN lx_usermeta lx_usermeta2 ON lx_usermeta.user_id = lx_usermeta2.user_id
WHERE lx_usermeta.meta_key = "first_name"
AND lx_usermeta2.meta_key = "last_name"

When I run the first script, if someone does not have all of the wpcf-* fields with data in them, they will not return as a result. I want to see results for every user, regardless of whether they have data for every column, which does work when I only use the second script.

What could possibly be the issue specifically with wpcf-* fields?

I have tried "LEFT JOIN", "NULL or", and some other ideas but ultimately, it's only when I call the wpcf fields that it seems to not show a user if they have any blank information for the requested fields.

Thank you very much for your help, this has had me stumped spinning my wheels for an entire work day!

[Minesh]

Hello. Thank you for contacting the Toolset support.

Well - this is not a Toolset issue at all - the thing is that if metakey will not be exists with the specific user it will not return the value.

The second query works for you because you are using first_name and last_name those are the default meta keys available for every user but in contrary with usermeta (custom user fields) created using types (wpcf-*) - those meta key will be available with user when it will have value - this is normal and expected result.

So either you need to use subquery or you should contact SQL expert to retrieve your expected output Or you should retrieve all users and use wordpress standard function get_user_meta to display your output as per your need.
=> https://developer.wordpress.org/reference/functions/get_user_meta/

[frankS-8]

Thank you, I figured it was something unrelated to wpcf but wanted to check.