mysql query on wp_user and user_meta problem

Question

I have a query that works when I have a field view_type = sale but when the view_type = lead it doesn't return any records even when I know there is one. Here is the query...

    SELECT a.id, a.user_email, a.user_registered, a.user_login, b1.meta_value AS first_name, b2.meta_value as last_name, b3.meta_value as qualified,
b4.meta_value as referrer,
b5.meta_value as view_type,
b6.meta_value as ref_by,
b7.meta_value as wp_optimizemember_custom_fields
FROM wp_users a
INNER JOIN wp_usermeta b1 ON b1.user_id = a.ID AND b1.meta_key = 'first_name' 
INNER JOIN wp_usermeta b2 ON b2.user_id = a.ID AND b2.meta_key = 'last_name'
INNER JOIN wp_usermeta b3 ON b3.user_id = a.ID AND b3.meta_key = 'qualified'
INNER JOIN wp_usermeta b4 ON b4.user_id = a.ID AND b4.meta_key = 'referrer'
INNER JOIN wp_usermeta b5 ON b5.user_id = a.ID AND b5.meta_key = 'view_type'
INNER JOIN wp_usermeta b6 ON b6.user_id = a.ID AND b6.meta_key = 'ref_by'
INNER JOIN wp_usermeta b7 ON b7.user_id = a.ID AND b7.meta_key = 'wp_optimizemember_custom_fields'

WHERE (b4.meta_value LIKE '$user_ID' AND b5.meta_value LIKE 'lead') or
(b6.meta_value LIKE '$user_ID' AND b5.meta_value LIKE 'lead') 

What wrong with this query?

Answer 1

In usermeta table WordPress stores many serialized data. So direct meta_value would not work I think. So try the below query-

SELECT a.id, 
       a.user_email, 
       a.user_registered, 
       a.user_login, 
       b1.meta_value AS first_name, 
       b2.meta_value AS last_name, 
       b3.meta_value AS qualified, 
       b4.meta_value AS referrer, 
       b5.meta_value AS view_type, 
       b6.meta_value AS ref_by, 
       b7.meta_value AS wp_optimizemember_custom_fields 
FROM   wp_users AS a 
       INNER JOIN wp_usermeta AS b1 
               ON b1.user_id = a.id 
                  AND b1.meta_key = 'first_name' 
       INNER JOIN wp_usermeta AS b2 
               ON b2.user_id = a.id 
                  AND b2.meta_key = 'last_name' 
       INNER JOIN wp_usermeta AS b3 
               ON b3.user_id = a.id 
                  AND b3.meta_key = 'qualified' 
       INNER JOIN wp_usermeta AS b4 
               ON b4.user_id = a.id 
                  AND b4.meta_key = 'referrer' 
       INNER JOIN wp_usermeta AS b5 
               ON b5.user_id = a.id 
                  AND b5.meta_key = 'view_type' 
       INNER JOIN wp_usermeta AS b6 
               ON b6.user_id = a.id 
                  AND b6.meta_key = 'ref_by' 
       INNER JOIN wp_usermeta AS b7 
               ON b7.user_id = a.id 
                  AND b7.meta_key = 'wp_optimizemember_custom_fields' 
WHERE  ( b4.meta_value LIKE '%{$user_ID}%' 
         AND b5.meta_value LIKE '%lead%' ) 
        OR ( b6.meta_value LIKE '%{$user_ID}%' 
             AND b5.meta_value LIKE '%lead%' ) 

Look here I put all the meta_value's value between two %. That means it will take the serialized data and will search it as string.

Hope that helps.

Answer 2

Ok so the problem was that 1 record didn't have the 'ref_by' meta_key set and with INNER JOINS all must succeed, thats why I wasn't getting a result.