14

I'm writing a complex MySQL query with multiple LEFT JOINs to combine data from several different tables.

$public_query = 
"SELECT * FROM  `wsat_ib` 
LEFT JOIN wp_usermeta ON wsat_ib.user_id = wp_usermeta.user_id
LEFT JOIN wpjb_resume ON wsat_ib.user_id = wpjb_resume.user_id
LEFT JOIN wpjb_field_value ON wpjb_resume.id=wpjb_field_value.job_id 
LEFT JOIN wpjb_application ON wpjb_application.user_id = wsat_ib.user_id
WHERE wp_usermeta.meta_key =  'target_employer'
AND wp_usermeta.meta_value =  'public'
AND wpjb_resume.is_active =1
";

My problem: The table wp_usermeta can have multiple rows for the same user_id. For example a user (with user_id = 5) may have more then one row in the table wp_usermeta with the field: meta_key that is 'target_employer'. In case that's not clear, the rows might look like this.

id    user_id    meta_key           meta_value
1     5          target_employer    13
2     5          target_employer    53
3     79         target_employer    21

My Question: Is there any way that I can return each matching row from wp_usermeta as an array in one of my result objects? So the var_dump() would look something like this:

object(stdClass)#2906 (14) {
  ["user_id"]=>
  string(4) "5"
  ["firstname"]=>
  string(6) "Bilbo"
  ["lastname"]=>
  string(3) "Baggins"
  ...
  ["target_employer"]=>
  array(2) {
    [0]=>13,
    [1]=>53
  }
}

UPDATE: @bpanulla: Here's the real (unsimplified query)...

"SELECT wsat_ib.user_id, wpjb_resume.firstname, wpjb_resume.lastname, wsat_ib.overall_score, wsat_ib.account_score, wsat_ib.econ_score, wsat_ib.math_score, wsat_ib.logic_score, wsat_ib.fsanaly_score, wsat_ib.corpval_score, wsat_ib.end_time, GROUP_CONCAT(wp_usermeta.meta_value) AS target_employers, wpjb_field_value.value AS school, wpjb_application.job_id
FROM  `wsat_ib` 
LEFT JOIN wp_usermeta ON wsat_ib.user_id = wp_usermeta.user_id
LEFT JOIN wpjb_resume ON wsat_ib.user_id = wpjb_resume.user_id
LEFT JOIN wpjb_field_value ON wpjb_resume.id=wpjb_field_value.job_id 
LEFT JOIN wpjb_application ON wpjb_application.user_id = wsat_ib.user_id AND wpjb_application.field_id=3
WHERE (wp_usermeta.meta_key =  'target_employer'
AND wp_usermeta.meta_value =  'public'
AND wpjb_resume.is_active =1)
GROUP BY user_id
";
Improve this question
2
  • "Is there any way that I can return each matching row from wp_usermeta as an array in one of my result objects?" No, SQL requires result cells to be atomic. No arrays. You can accumulate the values for the same user into an array, using user_id as a key. Commented Dec 27, 2012 at 19:54
  • @user4035 Can you give an example how to do what you described? It sounds like what I want to do. Commented Dec 27, 2012 at 19:58

2 Answers 2

Reset to default
38

I don't think MySQL can return an array to PHP, but it can return a delimited list using the GROUP_CONCAT aggregate function. Try something like:

SELECT user_id, firstname, lastname,
     GROUP_CONCAT(wp_usermeta.meta_value) as target_employer_id
FROM `wsat_ib` 
    LEFT JOIN wp_usermeta ON
        (wsat_ib.user_id = wp_usermeta.user_id
           AND wp_usermeta.meta_key = 'target_employer')
GROUP BY user_id, firstname, lastname

By default you should get a comma-separated list of elements in target_employer_id. You can use the PHP explode function to turn that into an array in your PHP logic.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

I've almost got this working, but I'm struggling with the GROUP BY. I get the expected number of rows returned, but I'm confused by why I'm getting multiple duplicates in the target_employer field. Even though most of my users have only one target employer, I see somewhere between 1 and 12 employer ids in each row returned. Most are repeats of the same value, which is strange because duplicate key/value pairs are not allowed in the wp_usermeta table. Any advice?
The GROUP_CONCAT function takes a few extra parameters that can help in cases like these (see the doc page I linked). In particular, try GROUP_CONCAT(DISTINCT wp_usermeta.meta_value) to get a unique list. I am a bit puzzled that there would be duplicates, unless you've added more tables to the mix and are getting multiplied rows from the joins.
I think that is what's happening. I did leave off a couple tables in my example to make the question more readable. I added the real query to the question (above).
Every field that you output in the SELECT clause of your query generally needs to appear in the GROUP BY clause, unless you're using it in an aggregate function expression (like GROUP_CONCAT). There are exceptions to this made by some DB servers, including MySQL, but that's the general rule. Add the extra fields to the GROUP BY and that may eliminate the extra rows. So a SELECT * query will generally hamper using aggregate functions.
That seemed to be the problem. I had additional SELECTs that were causing duplication (aka "cartesian products"?). Adding them to the GROUP BY fixed that. But I have one final question: Can add a second GROUP_CONCAT? I'm testing it and it works BUT my previous problem returns (duplicates in the target_employer field).
|
1

Since MySQL 8 and MariaDB 10.5, it is also possible to use JSON_ARRAYAGG() and JSON_OBJECTAGG(), and then decoding the resulting string as JSON server-side.

Especially the JSON_OBJECTAGG can be viewed as a much more advanced alternative to GROUP_CONCAT() (see the docs).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.