$wpdb MySQL select from main table and join each row with two rows of another table

0
votes

$referrer is the only defined variable.

main_table holds the relationship between users and referrers. a referrer can have many users.

user_id  | referrer
1        | seller
2        | abother seller
3        | another seller
4        | seller

secondary_table holds user_id, meta_key and meta_value with last_name and first_name being meta_keys.

user_id  | meta_key   | meta_value
1        | first_name | John
1        | last_name  | Doe
4        | first_name | Betty
4        | last_name  | Boo

I need to merge the following 3 queries

SELECT user_id FROM main_table WHERE referrer = $referrer

SELECT meta_value FROM secondary_table WHERE user_id = $user_id AND meta_key = first_name

SELECT meta_value FROM secondary_table WHERE user_id = $user_id AND meta_key = last_name

into one query so I can use it with $results = $wpdb->get_results, then asort() the $results by last_name to have an alphabetically ordered output and echo $results with a foreach like

foreach ($results as $result) {
  echo $result->user_id.' '.$result->first_name.' '.$result->last_name;
}

If $referrer == "seller" the output should look like this:

4 Betty Boo
1 John Doe

How should that single query look like?

Thanks for your help.

2
mysqlsqlwordpress

2 Answers

0
votes

You could use this:

select
  main_table.user_id,
  concat(s1.meta_value, ' ', s2.meta_value) as name
from
  main_table left join secondary_table s1
  on main_table.user_id = s1.user_id and s1.meta_key = 'first_name'
  left join secondary_table s2
  on main_table.user_id = s2.user_id and s2.meta_key = 'last_name'
where
  referrer = $referrer

I'm joining main_table with secondary_table twice, the first time filtered on the first name, the second time filtered on the last name. Then to get the name you just have to concat the first meta_value with the second.

0
votes

Its little bit tricky. You can use following query

select m.user_id as new_user_id, s1.meta_value as new_first_name, s2.meta_value as new_last_name from main_table m, secondary_table s1, secondary_table s2 where m.user_id=s1.user_id and m.user_id=s2.user_id and s1.meta_key='first_name' and s2.meta_key='last_name' and m.referrer='seller' order by new_last_name

and then u can do 

$results = $wpdb->get_results(...)  
foreach ($results as $result) {
      echo $result->new_user_id.' '.$result->new_first_name.' '.$result->new_last_name;
    }