Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2012
    Posts
    6

    Unanswered: WordPress Pivot Query

    Heya guys,

    Doing a complicated back-end application for a client that pulls in user info from WordPress wp_users and wp_usermeta tables in one query. The wp_usermeta field has columns like meta_key and meta_value and contains many rows for each user. I found some example query that lets me gather all the info I need for each user in one row.

    But, I now also need to add a WHERE clause to limit the rows returned based on one of the fields in the wp_usermeta table and can't figure it out. For instance, to search by users last name.

    Can anyone help point me in the right direction? I'm over my head on the queries. Here is what I have so far ($max is a limit/offset for pagination).

    PHP Code:
    $sql '
    SELECT

    wp_users.ID, wp_users.user_email, 
    MAX(CASE WHEN wp_usermeta.meta_key = "first_name" then wp_usermeta.meta_value ELSE NULL END) as first_name,
    MAX(CASE WHEN wp_usermeta.meta_key = "last_name" then wp_usermeta.meta_value ELSE NULL END) as last_name,
    MAX(CASE WHEN wp_usermeta.meta_key = "sign_up_date" then wp_usermeta.meta_value ELSE NULL END) as sign_up_date,
    MAX(CASE WHEN wp_usermeta.meta_key = "years_in_sales" then wp_usermeta.meta_value ELSE NULL END) as years_in_sales,
    MAX(CASE WHEN wp_usermeta.meta_key = "industry" then wp_usermeta.meta_value ELSE NULL END) as industry,
    MAX(CASE WHEN wp_usermeta.meta_key = "leads_team" then wp_usermeta.meta_value ELSE NULL END) as leads_team,
    MAX(CASE WHEN wp_usermeta.meta_key = "site_visits" then wp_usermeta.meta_value ELSE NULL END) as site_visits,
    MAX(CASE WHEN wp_usermeta.meta_key = "last_visit" then wp_usermeta.meta_value ELSE NULL END) as last_visit

    FROM wp_users

    LEFT JOIN wp_usermeta
    ON wp_users.ID = wp_usermeta.user_id

    WHERE 1 = 1 ' 
    .$where '

    GROUP BY wp_users.user_login

    ORDER BY wp_users.ID asc 

    .  $max
    Thanks a million!

    Philip

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is in your variable $where?
    if its a string value it should be encapsualted by '

    there should be an and

    eg
    PHP Code:
    $where " AND name = 'patch2112'"
    or
    PHP Code:
    $SearchFor "patch2112";
    $where " AND name = '".$SearchFor."'"
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2012
    Posts
    6

    query

    Thanks for responding Healdem.

    That is how the variable is being stored. Probably should have just taken it out for this post. When I put it in like that though, I get the error "Unknown column 'last_name' in 'where clause'".

    Here's the varible-less query...

    Code:
    SELECT
    
    wp_users.ID, wp_users.user_email, 
    MAX(CASE WHEN wp_usermeta.meta_key = "first_name" then wp_usermeta.meta_value ELSE NULL END) as first_name,
    MAX(CASE WHEN wp_usermeta.meta_key = "last_name" then wp_usermeta.meta_value ELSE NULL END) as last_name,
    MAX(CASE WHEN wp_usermeta.meta_key = "sign_up_date" then wp_usermeta.meta_value ELSE NULL END) as sign_up_date,
    MAX(CASE WHEN wp_usermeta.meta_key = "years_in_sales" then wp_usermeta.meta_value ELSE NULL END) as years_in_sales,
    MAX(CASE WHEN wp_usermeta.meta_key = "industry" then wp_usermeta.meta_value ELSE NULL END) as industry,
    MAX(CASE WHEN wp_usermeta.meta_key = "leads_team" then wp_usermeta.meta_value ELSE NULL END) as leads_team,
    MAX(CASE WHEN wp_usermeta.meta_key = "site_visits" then wp_usermeta.meta_value ELSE NULL END) as site_visits,
    MAX(CASE WHEN wp_usermeta.meta_key = "last_visit" then wp_usermeta.meta_value ELSE NULL END) as last_visit
    
    FROM wp_users
    
    LEFT JOIN wp_usermeta
    ON wp_users.ID = wp_usermeta.user_id
    
    WHERE 1 = 1  and last_name="Light"
    
    GROUP BY wp_users.user_login
    
    ORDER BY wp_users.ID asc

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem is, you cannot reference a column alias in the WHERE clause

    workaround is simple, just push the query down into a subquery --
    Code:
    SELECT dt.* 
      FROM ( SELECT wp_users.ID
                  , wp_users.user_email
                  , MAX(CASE WHEN wp_usermeta.meta_key = 'first_name' then wp_usermeta.meta_value ELSE NULL END) as first_name
                  , MAX(CASE WHEN wp_usermeta.meta_key = 'last_name' then wp_usermeta.meta_value ELSE NULL END) as last_name
                  , MAX(CASE WHEN wp_usermeta.meta_key = 'sign_up_date' then wp_usermeta.meta_value ELSE NULL END) as sign_up_date
                  , MAX(CASE WHEN wp_usermeta.meta_key = 'years_in_sales' then wp_usermeta.meta_value ELSE NULL END) as years_in_sales
                  , MAX(CASE WHEN wp_usermeta.meta_key = 'industry' then wp_usermeta.meta_value ELSE NULL END) as industry
                  , MAX(CASE WHEN wp_usermeta.meta_key = 'leads_team' then wp_usermeta.meta_value ELSE NULL END) as leads_team
                  , MAX(CASE WHEN wp_usermeta.meta_key = 'site_visits' then wp_usermeta.meta_value ELSE NULL END) as site_visits
                  , MAX(CASE WHEN wp_usermeta.meta_key = 'last_visit' then wp_usermeta.meta_value ELSE NULL END) as last_visit
               FROM wp_users
             LEFT 
               JOIN wp_usermeta
                 ON wp_usermeta.user_id = wp_users.ID
             GROUP 
                 BY wp_users.user_login
           ) AS dt             
     WHERE dt.last_name = 'Light'
    ORDER 
        BY dt.ID asc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2012
    Posts
    6

    Thanks!

    You are the man r937!

    I wish you much beer and eternal happiness

  6. #6
    Join Date
    Aug 2012
    Posts
    6

    Another one

    Ok, that got me through that section. Next one adds builds off this one and adds even more complexity to it. Need to do a very similar query as above, but also pull in additional results from a 3rd table that stores results from a calculator type thing that the registered users can use.

    Something like...

    Code:
    SELECT dt.* 
      FROM ( SELECT wp_users.ID
                  , wp_users.user_email
                  , path_results.*
                  , MAX(CASE WHEN wp_usermeta.meta_key = "first_name" then wp_usermeta.meta_value ELSE NULL END) as first_name
                  , MAX(CASE WHEN wp_usermeta.meta_key = "last_name" then wp_usermeta.meta_value ELSE NULL END) as last_name
               FROM wp_users, path_results
             LEFT 
               JOIN wp_usermeta
                 ON wp_usermeta.user_id = wp_users.ID
             LEFT 
               JOIN path_results
                 ON path_results.user_id = wp_users.ID
             GROUP 
                 BY wp_users.user_login
           ) AS dt             
    WHERE 1 = 1
    ORDER 
        BY dt.ID asc
    Tried it several different ways, but I"m out of my league on MySQL here and can't crack it.

    Thanks so much for your help!

    Philip

  7. #7
    Join Date
    Aug 2012
    Posts
    6

    Ps

    PS: Need to be able to search by fields in both wp_usermeta and path_results in this one to give client the search functionality they want.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT dt.* 
         , path_results.*
      FROM ( SELECT wp_users.ID
                  , wp_users.user_email
                  , MAX(CASE WHEN wp_usermeta.meta_key = "first_name" then wp_usermeta.meta_value ELSE NULL END) as first_name
                  , MAX(CASE WHEN wp_usermeta.meta_key = "last_name" then wp_usermeta.meta_value ELSE NULL END) as last_name
               FROM wp_users
             INNER 
               JOIN wp_usermeta
                 ON wp_usermeta.user_id = wp_users.ID
             GROUP 
                 BY wp_users.user_login
           ) AS dt             
    INNER
      JOIN path_results
        ON path_results.user_id = dt.ID
     WHERE 1 = 1
       AND dt.last_name = 'Light'
       AND path_results.foo = 'bar'
    ORDER 
        BY dt.ID asc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2012
    Posts
    6

    Awesome

    Ah-ha! Makes sense when you see it.

    Thank you soooooo much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •