MySQL将某些结果分组到一个数组中 [英] MySQL group certain results into an array

问题描述

我正在编写一个包含多个LEFT JOIN的复杂MySQL查询，以合并来自多个不同表的数据.

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

我的问题:表wp_usermeta可以针对同一user_id具有多个行.例如，一个用户(具有user_id = 5)在表wp_usermeta中可能具有多于一行的字段:meta_key是'target_employer'.如果不清楚，这些行可能看起来像这样.

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

我的问题:有什么方法可以将wp_usermeta中的每个匹配行作为数组返回到我的一个结果对象中?所以var_dump()看起来像这样:

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
  }
}

更新: @bpanulla:这是真实的(未简化的查询)...

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

推荐答案

我不认为MySQL可以将数组返回给PHP，但是它可以使用

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

默认情况下，您应该在target_employer_id中获得一个逗号分隔的元素列表.您可以使用PHP explode函数将其转换为PHP逻辑中的数组.

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.

这篇关于MySQL将某些结果分组到一个数组中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！