I'm in need of someone who knows MySQL better than I do! I've started writing a custom statement, but I'm not an expert with mySQL and it's not quite there (I'm happy to scrap it entirely if there's a better way to do it!)

Here's what I'm trying to do...

Retrieve a SINGLE user_id where the users meta_key _wcv_store_icon_id has a value (not including empty string). The results must be ordered by promo_count. The issue I'm having is that promo_count might not exist, but I'd like Lower (or non-existing promo_count to come first)

What I currently have:

SELECT u1.`user_id`
FROM `wp_usermeta` u1
LEFT JOIN `wp_usermeta` u2
ON u1.`user_id` = u2.`user_id`
WHERE (u1.`meta_key` = '_wcv_store_icon_id' AND u1.`meta_value` <> '')
AND u2.`meta_key` = 'promo_count'
GROUP BY u1.`user_id`
ORDER BY u2.`meta_value` ASC

For the record, I originally tried doing this with get_users but I simply couldn't get it working with any combination of meta_key, meta_value, or meta_query parameters... I think a custom query is definitely the way to go here, however I don't know enough to get it working.

Any suggestions are welcome! I'm at the end of my tether with this one :(

  • Try WP_Meta_Query codex.wordpress.org/Class_Reference/WP_Meta_Query – Ruhul Amin Dec 14 '16 at 14:15
  • I've tried that - I'd really just like to know the mySQL statement required for the task because using WP_Meta_Query I need to have the promo_count exist before I can sort it by that value. If the promo_count doesn't exist, I'd like that use to be sorted to the top of the list. – Tom Dyer Dec 14 '16 at 16:36

Your Answer

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

Browse other questions tagged or ask your own question.