MySQL group certain results into an array

Question

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
";

Solution

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.

Comments

[emersonthis]

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?

[bpanulla]

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.

[emersonthis]

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).

[bpanulla]

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.

[emersonthis]

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).

[bpanulla]

Only if you are grouping by the same set of fields. Think of GROUP_CONCAT() exactly like you would SUM(), AVG(), or COUNT(). You could in principle add another copy of wp_usermeta joined to get another meta variable and GROUP_CONCAT() those meta_values too, as long as you grouped by the same fields.