CODEWITHSUNDEEP
mysqlsqlwordpress
cicit
cicit

Reputation: 591

Sort by value based on another column value

This is a query from Wordpress, but applies to any database setup this way. They have some data stored in meta fields. There is a table that has two fields called meta_name and meta_value. These fields are used to store almost everything. For instance, meta_name could be "lastname" and meta_value is the value of that name. I have a query that will return all users, but how can I sort by meta_value when meta_name="lastname"? Below is the current query.

SELECT um.user_id, users.display_name, GROUP_CONCAT(um.meta_value SEPARATOR ' ') 
FROM `wp_7bv88z_usermeta` um, `wp_7bv88z_pmpro_memberships_users` u, `wp_7bv88z_users` users 
WHERE u.user_id=um.user_id and users.ID=u.user_id and (um.meta_key='pmpro_sfirstname' or um.meta_key='pmpro_slastname') 
and u.status='active'
GROUP BY um.user_id

Sample Data:

user_meta table

╔════=====╦══════════════==╦══════=======╗
║ user_id ║  meta_key        ║ meta_value  ║
╠════=====╬══════════════==╬══════=======╣
║  1      ║ pmpro_sfirstname ║ Jeff        ║
║  1      ║ pmpro_slasttname ║ Atwood      ║
║  2      ║ pmpro_sfirstname ║ Geoff       ║
║  2      ║ pmpro_slasttname ║ Davis       ║
║  3      ║ pmpro_sfirstname ║ Jarrod      ║
║  3      ║ pmpro_slasttname ║ Minor       ║
║  4      ║ pmpro_sfirstname ║ Joel        ║
║  4      ║ pmpro_slasttname ║ Brown       ║
╚════=====╩════════════=====╩══════======╝

Expected Results:

1, Jeff Atwood
4, Joel Brown
2, Geoff Davis
3, Jarrod Minor

Upvotes: 0

Views: 41

Answers (1)

Simon R
Simon R

Reputation: 3772

I'm not a fan of old syntax or cross joins - but you could do something like this; 

SELECT um.user_id, 
       users.display_name, 
       GROUP_CONCAT(um.meta_value SEPARATOR ' ') 
FROM `user_meta` um, 
      `memberships_users` u, 
      `users` users,
      `user_meta` lname,
      `user_meta` fname
WHERE u.user_id = um.user_id 
      and users.id = u.user_id 
      and (lname.user_id = u.user_id and lname.meta_key = 'pmpro_slastname')
      and (fname.user_id = u.user_id and fname.meta_key = 'pmpro_sfirstname')
      and (um.meta_key='pmpro_sfirstname' or um.meta_key='pmpro_slastname') 
and   u.status='active'
GROUP BY lname.meta_value, fname.meta_value, um.user_id

See this SQLFiddle to see it in action

Upvotes: 2

Related Questions